VBA & Macro Problems

How to Automatically Convert an Excel Worksheet to PDF and Email It

Natalie Taylor Oct 8, 2026 868 views

Question details

The user needs to automate the process of saving an active Excel worksheet as a PDF file and sending it as an attachment through Outlook.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating periodic reports or invoices in Excel and sending them directly to colleagues or clients without manual saving and attaching.
Observed behavior: The user currently has to manually export the file, open their email client, attach the document, and delete the local file, and is looking for a VBA solution to perform all these actions automatically.

Before you start

Ensure that Microsoft Outlook is installed, configured as your default email client, and that the Developer tab is enabled in your Excel ribbon to allow VBA macro creation.

Solution 1Recommended

Use a VBA Macro to Export to PDF and Send via Outlook

Create and run a VBA script that saves the current worksheet as a temporary PDF, attaches it to a new Outlook email, sends the message, and cleans up the temporary file.

This automated method relies on integrating Excel with the Outlook application object via VBA. It temporarily saves a PDF on your system, attaches it to a generated email, and then deletes the local file so your hard drive stays clutter-free.

Open the VBA Editor

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

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank workspace for your code.

Add the Automation Code

Copy and paste a standard VBA script designed to ExportAsFixedFormat (PDF) and trigger 'CreateObject("Outlook.Application")'. Ensure the script includes a command to attach the generated file path.

Update File Path and Recipient Details

Modify the temporary file path, the recipient's email address (.To), subject line (.Subject), and body text (.Body) within the script to match your exact requirements.

Run the Macro

Press F5 to run the macro. Verify in Outlook that the email has been created or sent, and check that the temporary PDF file was successfully deleted.

Use a VBA Macro to Export to PDF and Send via Outlook

Macro Security and Formatting: You must save your workbook as an Excel Macro-Enabled Workbook (.xlsm) for the code to persist. If you encounter errors, ensure your Trust Center settings allow macros to run.

Easily Export and Share PDFs with WPS Office

While VBA macros are powerful, WPS Office simplifies document sharing with intuitive built-in features. You can export spreadsheets to PDF and email them instantly without writing a single line of code.

1. Open Your Spreadsheet: Launch WPS Office and open the worksheet you want to share.
2. Export to PDF: Navigate to the 'Menu' tab and click on 'Export to PDF'. Adjust your layout settings and click 'Export'.
3. Share via Email: Go to the 'Menu' again, select 'Share', and choose 'Email'. This will instantly attach your newly created PDF to your default mail client.

Built-in PDF conversion tool completely free of charge.One-click sharing to email directly from the spreadsheet interface.Highly compatible with Microsoft Excel (.xlsx) formats and standard macros.Lightweight software that runs smoothly on both older and modern PCs.

Frequently Asked Questions

Why am I getting a 'User-defined type not defined' error when running the macro?

This typically occurs if the Microsoft Outlook Object Library is not enabled. In the VBA editor, go to Tools > References, scroll down, and check the box next to 'Microsoft Outlook [version] Object Library'.

Can I have the macro send the email automatically without reviewing it?

Yes. In your VBA code, look for the '.Display' command under the email object and change it to '.Send'. This will bypass the Outlook preview window and dispatch the email immediately.

How do I ensure the temporary PDF file is deleted after sending?

Your VBA script must include the 'Kill' command followed by the variable storing your temporary file path (e.g., Kill TempFilePath). Ensure this line is placed after the '.Send' or '.Display' command.

Will this VBA macro work on a Mac computer?

VBA macros that interact with COM objects (like 'Outlook.Application') are designed specifically for Windows. On a Mac, you will need to use AppleScript integration to automate Outlook.

Excel & Data Problems