Thursday, July 30, 2020

Home Code VBA How To Save A Worksheet As PDF File And Email It As An Attachment Through Outlook?

How To Save A Worksheet As PDF File And Email It As An Attachment Through Outlook?

July 30, 2020 Code VBA,

How To Save A Worksheet As PDF File And Email It As An Attachment Through Outlook?

In some cases, you may need to send a worksheet as a PDF file through Outlook. Usually, you have to manually save the worksheet as a PDF file, then create a new email with this PDF file as attachment in your Outlook and finally send it. It is time-consuming to achieve it manually step by step. In this article, we will show you how to quickly save a worksheet as a PDF file and send it automatically as an attachment through Outlook in Excel.

Save a worksheet as PDF file and email it as an attachment with VBA code

Save A Worksheet As PDF File And Email It As An Attachment With VBA Code

Amazing! Using Efficient Tabs In Excel Like Chrome, Firefox And Safari!

Save 50% Of Your Time, And Reduce Thousands Of Mouse Clicks For You Every Day!

You can run the below VBA code to automatically save active worksheet as a PDF file, and then email it as an attachment through Outlook. Please do as follows.

1. Open the worksheet you will save as PDF and send, then press the Alt + F11keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Code window. See screenshot:

VBA code: Save a worksheet as PDF file and email it as an attachment

Sub Saveaspdfandsend()

Dim xSht As Worksheet

Dim xFileDlg As FileDialog

Dim xFolder As String

Dim xYesorNo As Integer

Dim xOutlookObj As Object

Dim xEmailObj As Object

Dim xUsedRng As Range

Set xSht = ActiveSheet

Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)

If xFileDlg.Show = True Then

   xFolder = xFileDlg.SelectedItems(1)

Else

   MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"

   Exit Sub

End If

xFolder = xFolder + "\" + xSht.Name + ".pdf"

'Check if file already exist

If Len(Dir(xFolder)) > 0 Then

    xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _

                      vbYesNo + vbQuestion, "File Exists")

    On Error Resume Next

    If xYesorNo = vbYes Then

        Kill xFolder

    Else

        MsgBox "if you don't overwrite the existing PDF, I can't continue." _

                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"

        Exit Sub

    End If

    If Err.Number <> 0 Then

        MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _

                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"

        Exit Sub

    End If

End If

Set xUsedRng = xSht.UsedRange

If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then

    'Save as PDF file

    xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

    'Create Outlook email

    Set xOutlookObj = CreateObject("Outlook.Application")

    Set xEmailObj = xOutlookObj.CreateItem(0)

    With xEmailObj

        .Display

        .To = ""

        .CC = ""

        .Subject = xSht.Name + ".pdf"

        .Attachments.Add xFolder

        If DisplayEmail = False Then

            '.Send

        End If

    End With

Else

  MsgBox "The active worksheet cannot be blank"

  Exit Sub

End If

End Sub