Dear Readers,
In my previous article, you saw how to save an Excel Sheet as PDF file. Now you know, how to save an Excel Sheet as a pdf. Also, we have seen how to send the ActiveSheet as an attachment in Email. In this article I am going to show you how to send the ActiveSheet as an Attachment in PDF format. You can also download the Excel Workbook at the end of this article and play around with the code. Its all yours 🙂

<< Go Back to the Excel Tutorial Page

Below is the code, which works at the below logic:
1. Save the ActiveSheet as PDF in Temporary Folder of the System
2. Attach the File in Outlook New Email
3. Send the Email
4. Delete the PDF file from the Temp Folder.

Sub Email_ActiveSheet_As_PDF()
'Do not forget to change the email ID
'before running this code
    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
' Temporary file path where pdf
' file will be saved before
' sending it in email by attaching it.
    TempFilePath = Environ$("temp") & "\"
' Now append a date and time stamp
' in your pdf file name. Naming convention
' can be changed based on your requirement.
    TempFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
'Complete path of the file where it is saved
    FileFullPath = TempFilePath & TempFileName
'Now Export the Activesshet as PDF with the given File Name and path
    On Error GoTo err
    With ActiveSheet
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
    End With
'Now open a new mail
    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)
    On Error Resume Next
    With NewMail
        .To = "[email protected]"
        .CC = "[email protected]"
        .BCC = "[email protected]"
        .Subject = "Type your Subject here"
        .Body = "Type the Body of your mail"
        .Attachments.Add FileFullPath '--- full path of the pdf where it is saved
        .Send   'or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the pdf file from the temp folder
    Kill FileFullPath
'set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing
'Now set the application properties back to true
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox ("Email has been Sent Successfully")
    Exit Sub
        MsgBox err.Description

End Sub

Download Now


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!