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, _ OpenAfterPublish:=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@example.com" .CC = "firstname.lastname@example.org" .BCC = "email@example.com" .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 err: MsgBox err.Description End Sub