VBA & Macro Problems

How to Save an Excel Worksheet as PDF and Email It via Outlook using VBA

Rana Garcia Oct 22, 2026 868 views

Question details

The user needs to automate the process of saving an Excel worksheet as a PDF and sending it as an email attachment via Outlook, dynamically pulling the recipient's email address from cell W1.

How to Save an Excel Worksheet as PDF and Email It With VBA

Product: Excel
Device & OS: not provided
Scenario: Automating document export and email distribution using Excel macros.
Observed behavior: The user requires specific VBA code to create an Outlook mail item, set the recipient from a specific cell, attach the newly generated PDF, and dispatch the email.

Before you start

Ensure that the Microsoft Outlook desktop application is installed and configured with an active email account on your system, as this macro requires the Outlook application to generate the email.

Solution 1Recommended

Use Late Binding VBA Code to Create an Outlook Email with a PDF Attachment

This method utilizes Late Binding in VBA to create an Outlook application instance, attach your exported PDF, and define the email recipient using the value stored in a specific worksheet cell.

By utilizing the 'CreateObject' function, this macro works dynamically without requiring you to manually enable Outlook reference libraries in the Excel VBA editor. Ensure your code saves the PDF to a known file path before attempting to attach it.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications editor. Locate and open the specific module where your existing PDF export code is saved.

Declare Outlook Variables

Add 'Dim objOL As Object' and 'Dim objMsg As Object' to your macro script. Initialize the Outlook application object by adding 'Set objOL = CreateObject("Outlook.Application")'.

Configure the Email Item

Create a new email message by typing 'Set objMsg = objOL.CreateItem(0)'. You can define the subject line and body text using 'objMsg.Subject = "Your Subject Here"' and 'objMsg.Body = "Your Message Here"'.

Set Recipient from Cell

To set the 'To' field based on cell W1 in the 'Costing' sheet, add the following line: 'objMsg.To = Sheets("Costing").Range("W1").Value'.

Attach PDF and Display/Send

Attach your generated PDF file using 'objMsg.Attachments.Add strTempFile' (assuming your PDF file path is stored in the variable strTempFile). Finally, type 'objMsg.Display' to review the email before sending, or 'objMsg.Send' to send it automatically.

Automatic Sending Consideration: Using the 'objMsg.Send' command works best when the Outlook application is already running in the background. If you prefer to review the email formatting and attachments first, stick with 'objMsg.Display'.

Built-in Macro Editor

Execute Advanced VBA Macros with WPS Spreadsheet

WPS Spreadsheet provides robust support for running complex VBA macros, making it easy to automate PDF generation and Outlook email dispatch directly from your workbooks.

1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xls file containing the existing VBA macro.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or the 'Visual Basic Editor' to access your code.
3. Run the Automation: Execute your macro. WPS Spreadsheet will smoothly process the script, generate the PDF, and communicate with Outlook to draft your email.

Highly compatible with Microsoft Excel formats (.xlsx, .xlsm) and VBA syntaxEasily execute complex VBA scripts for PDF generation and email automationLightweight, fast, and free to downloadFamiliar ribbon interface for seamless migration

Frequently Asked Questions

Why does my macro fail at the 'Attachments.Add' line?

This error typically occurs if the PDF file has not finished saving before the email code executes, or if the file path variable (e.g., strTempFile) contains an incorrect or blank value. Ensure the PDF export completes successfully and the path is valid before attempting to add it as an attachment.

Can I send the email to multiple recipients using different cell values?

Yes. You can concatenate multiple email addresses stored in different cells by separating them with a semicolon. For example: objMsg.To = Range("W1").Value & ";" & Range("W2").Value.

What is the difference between Early Binding and Late Binding in Outlook VBA?

Early binding requires you to manually activate the Microsoft Outlook Object Library in the VBA editor (via Tools > References). Late binding, which uses CreateObject("Outlook.Application"), does not require this manual step, making your macro much more adaptable when shared with users running different versions of Office.

How do I add CC or BCC recipients in my macro?

You can include CC and BCC recipients by setting the corresponding properties of the mail object. Simply add lines like objMsg.CC = "email@example.com" or objMsg.BCC = Sheets("Costing").Range("W3").Value below your 'To' field definition.

Excel & Data Problems