VBA & Macro Problems

Fix Excel VBA Creates PDFs But Doesn't Send Outlook Emails

Huda Qurayshi Sep 30, 2026 868 views

Question details

User is experiencing an issue where an Excel VBA macro successfully generates PDF files but fails to open Outlook or send the corresponding email messages.

How to Fix Excel VBA Creating PDFs But Not Sending Outlook Emails

Product: Microsoft Excel / Outlook
Device & OS: not provided
Scenario: Automating PDF document generation and email distribution via Excel VBA scripts.
Observed behavior: The macro completes the PDF creation step but silently halts or fails before opening Outlook and sending the emails, likely due to suppressed errors or object model issues.

Before you start

Ensure that Microsoft Outlook is installed, set as the default email client on your computer, and actively running in the background before executing the macro.

Solution 1Recommended

Diagnose Suppressed VBA Errors and Filter Criteria

Remove error suppression to identify the exact line of code causing the failure, which is often related to AdvancedFilter range mismatches.

VBA scripts often use the 'On Error Resume Next' command to bypass minor glitches. However, this can mask critical failures, such as invalid AdvancedFilter criteria, preventing the script from reaching the email automation stage.

Disable Error Suppression

Open your VBA editor by pressing Alt + F11, locate your macro, and temporarily comment out any 'On Error Resume Next' statements by typing an apostrophe (') at the beginning of the line.

Debug Step-by-Step

Click inside your macro code and press F8 repeatedly to step through the code line by line. Observe exactly which line produces an error before the email script triggers.

Check AdvancedFilter Criteria

If the error occurs during filtering, verify that your 'CriteriaRange' is not set to an empty string. Ensure the source data range has a valid header row and the 'CopyToRange' contains exactly matching headers.

Diagnose Suppressed VBA Errors and Filter Criteria

Use Error Logging: Consider replacing 'On Error Resume Next' with proper error handling (e.g., 'On Error GoTo ErrorHandler') to display a message box with the specific error description and number.

Solution 2

Verify Outlook Object Model and References

Ensure your Excel VBA project has the proper library references and permissions to interact with Microsoft Outlook.

Free Microsoft Office alternative

Experience Seamless Document Automation with WPS Office

If troubleshooting Microsoft Excel's VBA and Outlook COM objects becomes too complex, consider switching to WPS Office. It provides a lightweight, highly compatible environment for your spreadsheet needs with built-in PDF tools.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing data file.
2. Export to PDF: Go to the 'Menu' or 'Tools' tab and click 'Export to PDF' to instantly generate a high-quality PDF document.
3. Share via Email: Use the built-in 'Share' feature to directly attach the generated PDF to your default email client with a single click.

Highly compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formatsBuilt-in PDF conversion and sharing without needing complex macrosFree, lightweight, and features a familiar, easy-to-navigate user interfaceSeamless migration of your existing spreadsheet workflows

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel macro stop silently before opening Outlook?

This usually happens because an earlier line of code failed but the error was hidden by an 'On Error Resume Next' statement, causing the script to exit or bypass the email generation block entirely.

How do I fix AdvancedFilter errors in my VBA macro?

Ensure your source data has accurate and unique headers, the 'CriteriaRange' is not pointing to a blank address, and that the 'CopyToRange' headers perfectly match the spelling and format of the source headers.

Can I send an email with a PDF attachment without opening Outlook via VBA?

Yes, you can use CDO (Collaboration Data Objects) to send emails directly via an SMTP server using VBA, which bypasses the need for the Outlook application to be installed or open.

Excel & Data Problems