VBA & Macro Problems

How to Save a PDF and Email via Outlook Using VBA Macro

Phi Hung Vo Sep 30, 2026 868 views

Question details

The user needs to automate exporting an active worksheet to a dated PDF and emailing it via Outlook using a single macro button, while keeping the main workbook on a local shared drive.

How to Create a VBA Macro to Save as PDF and Email via Outlook

Product: Spreadsheet
Device & OS: not provided
Scenario: A user wants to quickly share daily shift notes from a shared workbook by automating the PDF creation and email attachment process without altering the original file location.
Observed behavior: The goal is to implement a VBA procedure that confirms the PDF exists, attaches it to an Outlook email, sends it, and deletes the temporary PDF file afterward.

Before you start

Ensure that Microsoft Outlook is installed, logged in, and configured as your default email client. You also need to verify that the Developer tab is enabled in your spreadsheet program so you can access the VBA Editor.

Solution 1Recommended

Create a VBA Macro to Export PDF and Send via Outlook

This method uses standard VBA code to define a temporary file path, export the active worksheet as a PDF, and automate the Outlook application to attach and send the email.

This VBA procedure automates the entire reporting workflow. It creates a dated PDF of your active worksheet, opens Outlook in the background, attaches the newly created PDF, and optionally deletes the temporary PDF file once the email is successfully sent.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu bar of the VBA editor and select 'Module' to create a blank script window.

Write the VBA Code

Write a VBA script using the 'ActiveSheet.ExportAsFixedFormat' method to save the PDF to a temporary path. Next, use 'CreateObject("Outlook.Application")' to create a new mail item, add the recipient's address, attach the saved PDF path, and use the '.Send' command.

Assign the Macro to a Button

Return to your worksheet, insert a shape or button from the Developer or Insert tab, right-click it, select 'Assign Macro', and choose the procedure you just created.

Create a VBA Macro to Export PDF and Send via Outlook

Clean Up Temporary Files: Include the 'Kill' statement at the end of your VBA script targeting the temporary PDF file path. This ensures the PDF is deleted from your local drive after the email is sent, preventing unnecessary file clutter.

Powerful VBA Support in WPS Office

Automate PDF Export and Emails with WPS Spreadsheet

WPS Spreadsheet offers excellent compatibility with VBA (Visual Basic for Applications), allowing you to run your existing macros seamlessly to export PDFs and automate Outlook emails without extensive code rewriting.

1. Download WPS Office: Download and install the free version of WPS Office from the official website.
2. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm or .xls macro-enabled workbook.
3. Access the VBA Editor: Navigate to the 'Tools' tab and click on 'Developer' to access the built-in VBA Editor.
4. Run Your Macro: Paste or run your existing VBA macro to automatically generate the PDF and send your Outlook email.

Fully compatible with Microsoft Excel VBA macros and scripts.High compatibility with Microsoft Office file formats like .xlsx and .xlsm.Built-in robust PDF conversion features.Lightweight, fast, and completely free for everyday productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro sending the email through Outlook?

This usually happens if Outlook is not set as your default email client or if the Outlook desktop application is not open/configured. Ensure Outlook is running and that your script accurately references 'CreateObject("Outlook.Application")'.

Can I specify multiple ranges to export as separate PDFs in the same macro?

Yes, you can modify the VBA code to loop through specific sheet ranges, export each as a separate temporary PDF file using 'ExportAsFixedFormat', and then attach each file individually to the same Outlook email item before sending.

How do I automatically add today's date to the PDF file name?

In your VBA code, you can dynamically append the current date to your file name string using the Format function. For example, you can set your file name variable to: 'FileName := "ShiftNotes_" & Format(Date, "yyyymmdd") & ".pdf"'.

Will this macro work if my workbook is on a shared network drive?

Yes, it will work perfectly. Simply ensure your macro is configured to save the temporary PDF to a local directory (such as the user's local Temp folder or Desktop) before emailing it. The original workbook will remain untouched on the shared network drive.

Excel & Data Problems