VBA & Macro Problems

Fix Excel VBA Macro Saving PDF but Not Sending Email

Partner Editor Sep 28, 2026 869 views

Question details

The user's Excel VBA macro successfully generates and saves a PDF but fails to attach it and send or display an email.

Fix Excel VBA Macro Saving PDF but Not Sending Email

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the creation of a PDF from an Excel worksheet and emailing it as an attachment using a VBA macro.
Observed behavior: The macro saves the PDF to the specified file path but does not open the email client, create the email, or send the attachment as intended.

Before you start

Before modifying your VBA code, ensure that your default email client (such as Microsoft Outlook) is properly installed, set up, and running in the background.

Solution 1Recommended

Verify Mail Item Creation and Attachment Paths in VBA

Ensure the macro correctly initializes the mail object, specifies the exact file path for the attachment, and triggers the display or send command.

The most common reason a VBA macro creates a PDF but fails to email it is a missing or incorrect command in the Outlook automation portion of the script. The script must explicitly create the mail item, attach the file using the exact path it was saved to, and then call the display or send method.

Open the Visual Basic Editor

Press ALT + F11 in Excel to open the Visual Basic Editor (VBE).

Locate the Email Macro

Find the module containing your PDF export and email macro in the Project Explorer.

Check Mail Item Creation

Ensure you have code that initializes the email, such as 'Set OutMail = OutApp.CreateItem(0)'.

Verify the Attachment Path

Check the line containing '.Attachments.Add'. Ensure the variable passed to it exactly matches the string or cell reference used to save the PDF.

Add the Display or Send Command

Confirm that your code includes '.Display' (to open the email window) or '.Send' (to send it directly) inside the 'With OutMail' block.

Verify Mail Item Creation and Attachment Paths in VBA

Testing Your Macro: It is highly recommended to use '.Display' instead of '.Send' while testing your macro so you can visually verify the email and attachment before it is sent.

Solution 2

Enable Missing Object Library References

Resolve issues where the VBA code fails to recognize Outlook applications due to missing library references.

Automate with WPS Office

Automate Workflows with WPS Spreadsheet VBA

WPS Office provides excellent support for VBA macros. You can seamlessly run your existing macros to generate PDFs and automate emails using WPS Spreadsheet, enjoying a fast and familiar experience.

1. Open Your Workbook in WPS: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click the 'Visual Basic' icon.
3. Review Your VBA Code: Check your macro code to ensure the file paths and email application variables are correctly configured for your system.
4. Run the Macro: Click 'Run' or trigger the macro via your assigned button to successfully export the PDF and generate the email.

Highly compatible with Microsoft Excel VBA syntaxBuilt-in PDF conversion features without extra add-insFamiliar user interface for seamless migrationFree and lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro fail on the .Send command without showing an error?

Security settings in Microsoft Outlook often prevent external applications from sending emails automatically to protect against viruses. Try changing '.Send' to '.Display' to let the user manually click send, or adjust Outlook's Programmatic Access security settings in the Trust Center.

How do I dynamically attach the newly saved PDF instead of hardcoding the file path?

You can store the file path in a string variable when you export the PDF (e.g., 'strFilePath = ActiveWorkbook.Path & "\Report.pdf"'), and then pass that same variable to the '.Attachments.Add strFilePath' method.

Can I use VBA to send emails via Gmail or other clients instead of Outlook?

Yes, but you cannot use the Outlook Object Library for this. Instead, you need to use CDO (Collaboration Data Objects) in your VBA code to configure the SMTP server, port number, and login credentials for your specific email provider.

Excel & Data Problems