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

Method 1 – Print to PDF and Email for Single Worksheet

➜ Open a module by clicking Developer > Visual Basic (or press ALT + F11).

➜ 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

⧬ 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.

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

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

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

Check the circle before the Yes command and set up your email 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