Print to PDF and Email Using VBA in Excel: 2 Useful Cases

➜ Open a module by clicking Developer > Visual Basic (or press ALT + F11). How to Insert VBA Code ➜ Go to Insert > Module. Insert the complete code (you’ll get the complete code at the end of Step 4). Step 01: Print to PDF Print to PDF of your dataset available in the worksheet. To do that, you can use the ChDir statement to specify the current folder. Utilize the ExportAsFixedFormat statement to publish the active sheet in PDF format.

ChDir "E:\Exceldemy" 'Print to PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "E:\Exceldemy\Sales Report March.pdf", OpenAfterPublish:=True

Step 02: Declare and Set Variables to Send Email You must declare and set the following variables to automatically send the email.

'Declare Variables Dim EmailApp As Object Dim EmailItem As Object Dim myAttachments As Object 'Set Variables Set EmailApp = CreateObject("Outlook.application") Set EmailItem = EmailApp.CreateItem(0) Set myAttachments = EmailItem.Attachments

Note: It is necessary to open Outlook, mainly an email app having versatile tasks, for sharing or sending email from Excel directly. Step 03: Specify the Email Items Also, you need to assign the necessary email items e.g. the email address, subject and email body using the MailItem object.

With EmailItem .To = "[email protected]" .Subject = "Sales Report of Fruits" .Body = "Please find the PDF attachment of the Excel file"

Step 04: Add the Published PDF and Close Statement Use the Attachments.Add method to add the published PDF document and close the statement by setting the EmaiItem and EmailApp as Nothing.

.Attachments.Add "E:\Exceldemy\Sales Report March.pdf" '.send .Display End With Set EmailItem = Nothing Set EmailApp = Nothing

The complete code will look as follows.

Sub PrintToPDF_Email() ChDir "E:\Exceldemy" 'Print to PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "E:\Exceldemy\Sales Report March.pdf", OpenAfterPublish:=True 'Declare Variables Dim EmailApp As Object Dim EmailItem As Object Dim myAttachments As Object 'Set Variables Set EmailApp = CreateObject("Outlook.application") Set EmailItem = EmailApp.CreateItem(0) Set myAttachments = EmailItem.Attachments 'Specify Email Items and Add Attachment With EmailItem .To = "[email protected]" .Subject = "Sales Report of Fruits" .Body = "Please find the PDF attachment of the Excel file" .Attachments.Add "E:\Exceldemy\Sales Report March.pdf" '.send .Display End With Set EmailItem = Nothing Set EmailApp = Nothing End Sub

Excel VBA Print to PDF and Email for Single Worksheet

⧬ Things that you have to change:

File path: Define the file path where you want to publish the PDF e.g. E:\Exceldemy.
File name: Specify the file name e.g. E:\Exceldemy\Sales Report March.pdf
Email address: Enter the suitable email address.
Email subject: Add an attractive subject e.g. Sales Report of Fruits.
Email body: Describe briefly about the attachment e.g. Please find the PDF attachment of the Excel file.
Attached file name: It will be the same as the file name.

After running the code (keyboard shortcut is F5), you’ll get the published pdf document first.

Excel VBA Print to PDF and Email for Single Worksheet

Get the Email input box where the PDF files are attached. Now, you need to press the Send button only.

Excel VBA Print to PDF and Email for Single Worksheet

Set up an Outlook account; you may get this type of message.

Setting up an Outlook Account

After pressing the Next button, you’ll get the following message.

Setting up an Outlook Account

Check the circle before the Yes command and set up your email account.

Setting up an Outlook Account

Method 2 – Print to PDF and Email for Multiple Worksheets

If you have multiple worksheets print in multiple PDF files and then email those attachments. You have to change two major things, and the rest will be the same as shown in the first method.

Things to Change 01: Add loop for Printing Multiple Worksheets

You can use InputBox to enter the name of multiple worksheets. Subsequently, you have run For…Next statement along with the LBound and UBound function to set the lower and upper limit successfully. Use the ExportAsFixedFormat statement for printing to PDF.

Sheet_Names = InputBox("Enter the Names of the Worksheets to Print to PDF: ") Sheet_Names = Split(Sheet_Names, ", ") For i = LBound(Sheet_Names) To UBound(Sheet_Names) Worksheets(Sheet_Names(i)).ExportAsFixedFormat Type:=xlTypePDF, _ Filename:= _ "E:\Exceldemy\" + Sheet_Names(i) + ".pdf", _ OpenAfterPublish:=True Next i

Things to Change 02: Add Multiple Attachments

Add multiple attachments together in the following way.

.Attachments.Add "E:\Exceldemy\Sales Mar.pdf" .Attachments.Add "E:\Exceldemy\Sales Apr.pdf"

The complete code will look like the following after changing the two things (change the path, file name, and email items).

Sub PrintToPDF_Email_MultipleSheets() ChDir "E:\Exceldemy" 'Print to PDF for Multiple Sheets Sheet_Names = InputBox("Enter the Names of the Worksheets to Print to PDF: ") Sheet_Names = Split(Sheet_Names, ", ") For i = LBound(Sheet_Names) To UBound(Sheet_Names) Worksheets(Sheet_Names(i)).ExportAsFixedFormat Type:=xlTypePDF, _ Filename:= _ "E:\Exceldemy\" + Sheet_Names(i) + ".pdf", _ OpenAfterPublish:=True Next i 'Declare and Set Variables Dim EmailApp As Object Dim EmailItem As Object Dim myAttachments As Object Set EmailApp = CreateObject("Outlook.application") Set EmailItem = EmailApp.CreateItem(0) Set myAttachments = EmailItem.Attachments 'Specify Email Items and Add Attachments With EmailItem .To = "[email protected]" .Subject = "Sales Report in 2022" .Body = "Please find the PDF attachment of the Excel file" .Attachments.Add "E:\Exceldemy\Sales Mar.pdf" .Attachments.Add "E:\Exceldemy\Sales Apr.pdf" '.send .Display End With Set EmailItem = Nothing Set EmailApp = Nothing End Sub

Excel VBA Print to PDF and Email for Multiple Worksheets