VBA & Macro Problems

Use Excel Cell Value for PDF Filename and Email Macro

Aamir Naveed Akram Oct 7, 2026 869 views

Question details

The user wants an Excel VBA macro to export a worksheet as a PDF using a specific cell or named range for the file path and filename. The macro must also draft an Outlook email using named cells for the recipient, subject, and message, and display it without sending.

How to Use an Excel Cell Value for a PDF Filename and Email Macro

Product: Excel
Device & OS: not provided
Scenario: Automating the generation of PDF reports from an Excel worksheet and staging personalized emails with the PDF attached based on cell values.
Observed behavior: The user needs to successfully generate a PDF to a dynamic path defined in a cell, launch an Outlook email populated with specific cell data, and attach the generated PDF while leaving the email open for manual review.

Before you start

Ensure that the Microsoft Outlook desktop client is installed and configured on your computer, and verify that the named ranges in your Excel workbook exactly match the ones referenced in the VBA code.

Solution 1Recommended

Use VBA Macro to Export PDF and Display Email

This solution provides the complete VBA code to read file paths and email details from named cells, generate the PDF, and draft an Outlook email.

This macro leverages the ExportAsFixedFormat method to save your active worksheet as a PDF. It then uses CreateObject to launch the Outlook application, populates the required email fields from your spreadsheet's named ranges, and uses the .Display method to keep the email open for review.

Define Named Ranges in Excel

Before running the macro, ensure your workbook has cells named 'Win_Form_Path_and_Filename', 'Email_To', 'Email_Subject', and 'Email_Body' containing the respective data.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module from the top menu to create a new blank module.

Enter the VBA Code

Paste the following code into the module window: Sub SaveSheetAsPDFAndCreateEmail() Dim p As String p = Range("Win_Form_Path_and_Filename").Value ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p, OpenAfterPublish:=True With CreateObject("Outlook.Application").CreateItem(0) .To = Range("Email_To").Value .Subject = Range("Email_Subject").Value .Body = Range("Email_Body").Value .Attachments.Add p .Display End With End Sub

Run the Macro

Close the VBA editor and press Alt + F8 in Excel. Select 'SaveSheetAsPDFAndCreateEmail' from the list and click 'Run' to generate the PDF and draft the email.

Use VBA Macro to Export PDF and Display Email

Reviewing the Email: Using the '.Display' command ensures the Outlook email window opens for your review instead of sending automatically. This allows you to verify the attachment and messaging before clicking send.

Advanced Macro Support in WPS Office

Automate PDF and Email Workflows in WPS Spreadsheets

WPS Office provides excellent compatibility with Excel macros and VBA code. You can easily automate saving PDFs and drafting emails directly within WPS Spreadsheets.

1. Open your Workbook in WPS: Launch WPS Spreadsheets and open the .xlsm workbook containing your data and VBA macros.
2. Enable Macros: Go to the Developer tab on the ribbon and click on 'Macros' or press Alt + F8 to access your automated scripts.
3. Execute the Code: Select your PDF and Email macro from the list and hit 'Run' to automatically generate your file and draft the Outlook email.

Full compatibility with Microsoft Excel VBA macros and named rangesHigh-fidelity PDF export natively built into the spreadsheet toolLightweight and faster execution of complex spreadsheet tasksFree alternative to Microsoft Office with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro return an error on the 'CreateObject' line?

This error typically occurs if the Outlook desktop application is not installed on your computer, or if your default email client is set to a web browser or a different application. Ensure Outlook is installed and configured locally with an active profile.

How do I automatically send the email instead of just drafting it?

To send the email automatically without reviewing it first, change the '.Display' command in your VBA code to '.Send'.

Can I use standard cell references instead of named ranges in the macro?

Yes. If you prefer not to use named ranges, you can replace Range("Email_To").Value with standard cell references. For example, use Range("A1").Value or Sheets("Sheet1").Range("A1").Value for your specific data.

Excel & Data Problems