VBA & Macro Problems

How to Email Each Excel Worksheet as a PDF Attachment Using VBA

Camila Milosovich Oct 8, 2026 869 views

Question details

The user needs to export multiple individual worksheets as PDFs and automatically email them to specific addresses stored in a designated cell on each sheet.

How to Email Each Excel Worksheet as an Individual PDF Attachment

Product: Excel
Device & OS: not provided
Scenario: Distributing personalized information (such as employee records) stored on separate worksheets to individual recipients via an automated email process.
Observed behavior: A VBA macro is required to loop through sheets, save them as PDFs, read the email address from a specific cell (e.g., K1), attach the PDF to an Outlook email, send it, and clean up temporary files.

Before you start

Ensure Microsoft Outlook is installed, set as your default email client, and properly configured with your active email account before executing the macro. Save a backup of your workbook before running any new VBA code.

Solution 1Recommended

Use VBA to Export Worksheets as PDFs and Automate Outlook Emails

Create a macro that iterates through each worksheet, uses the ExportAsFixedFormat method to generate a temporary PDF, and attaches it to a new Outlook email.

By utilizing Excel's built-in PDF export feature alongside Outlook's COM object library, you can automate the entire process of generating and sending reports. The macro will dynamically read the recipient's address from cell K1 on each active worksheet.

Open the VBA Editor

Press Alt + F11 in your Excel workbook to launch the Visual Basic for Applications (VBA) editor. Click 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.

Set Up the Loop and Variables

Declare variables for the Worksheet, Outlook Application, and Mail Item. Set up a 'For Each ws In ThisWorkbook.Worksheets' loop so the code processes every tab in your workbook individually.

Export the Worksheet to PDF

Define a temporary file path string on your local drive (e.g., your Temp folder). Use the command 'ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=tempFilePath' to save the current sheet.

Create and Attach to the Email

Initialize Outlook using 'CreateObject("Outlook.Application")'. Generate a new mail item, assign 'ws.Range("K1").Value' to the '.To' field, and use '.Attachments.Add tempFilePath' to attach your newly created PDF.

Delete the Temporary PDF

After the '.Send' command, add the 'Kill tempFilePath' statement. This ensures the temporary PDF file is deleted from your hard drive before the loop moves to the next employee's worksheet.

Use VBA to Export Worksheets as PDFs and Automate Outlook Emails

Test Before Sending: During your initial test, replace the '.Send' command with '.Display' in your VBA code. This allows you to visually review the generated emails, attachments, and recipient addresses before actually dispatching them.

Free Microsoft Office alternative

A Better Way to Manage Spreadsheets and PDFs

If you are experiencing issues with complex VBA macros and Outlook integration, consider switching to WPS Office. It provides a highly compatible, easy-to-use alternative to Microsoft Excel, featuring powerful built-in PDF conversion tools without the need for complex coding.

1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open your existing Excel workbooks with full formatting preservation.
3. Use Built-in PDF Tools: Navigate to the 'Tools' tab to access native 'Export to PDF' functionalities, allowing you to bypass complicated macro setups for standard reporting.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls) formatsBuilt-in PDF toolkit to instantly export, merge, and edit documentsLightweight, fast, and completely free to useFamiliar user interface for immediate productivity and easy migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro crash when trying to create an Outlook email?

This typically happens if Microsoft Outlook is not installed locally, not set as your system's default mail client, or if the Outlook application is hanging in the background. Ensure Outlook is open and functioning normally before executing the macro.

Can I specify a different cell for the recipient's email address?

Yes. In your VBA code, locate the reference to ws.Range("K1").Value and update it to match the exact cell reference (such as "B5" or "Z10") that contains the email address in your specific worksheet layout.

How can I customize the file name of the generated PDF?

You can dynamically generate the file name by customizing the tempFilePath variable in your code. For example, you can concatenate the path with an employee's name found in cell A1 by using: tempFilePath = "C:\Temp\" & ws.Range("A1").Value & ".pdf".

Excel & Data Problems