Fix Excel VBA PDF Export and Outlook Attachment Errors
Question details
An Excel VBA macro fails with a 'file not found' error when attempting to attach a newly exported PDF document to an Outlook email.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user is running an Excel macro designed to export a specific cell range as a PDF file and automatically attach that resulting file to a new Outlook email draft.
- Observed behavior
- The macro successfully runs initially but stops and reports that the PDF cannot be found during the Outlook attachment phase, usually because the exported filename and the attachment filename do not match.
Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and locate the module containing your PDF export and Outlook email macro.
Use a Single Explicit Variable for the File Path
By declaring one unified string variable for the PDF's full file path, you ensure both the export method and the attachment method are referencing the exact same file.
This error almost always occurs when the path or filename in the ExportAsFixedFormat method differs slightly from the path used in the Attachments.Add method. Hardcoding paths in multiple places increases the risk of a typo or mismatched filenames (such as exporting a file named 'Feedback.pdf' but telling Outlook to attach 'Coaching.pdf'). Using a single explicit variable eliminates this inconsistency.
At the beginning of your macro, declare a string variable to hold the complete file path. For example, type 'Dim pdfPath As String'.
Assign the full destination path and filename to your variable, such as 'pdfPath = "C:\Users\YourName\Documents\Report.pdf"'.
Modify your ExportAsFixedFormat line to use the new variable. It should look similar to: 'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath'.
Locate the section of code generating the Outlook email and update the attachment line to reference the same variable: '.Attachments.Add pdfPath'.
Press F5 or run the macro from your workbook to verify that the PDF is correctly generated and immediately attached to the Outlook draft without errors.
Experience Seamless VBA Compatibility with WPS Office
If you frequently encounter complex macro issues or need a more reliable environment for your spreadsheets, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. It offers robust support for advanced Excel features, including VBA macros (in supported versions), seamless PDF exporting, and effortless document sharing.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device in minutes.
- 2. Open your Excel files: Open your existing .xlsx or .xlsm files directly in WPS Spreadsheet. The formatting and data will remain perfectly intact.
- 3. Export to PDF instantly: Use the built-in 'Export to PDF' button on the main toolbar to instantly convert your spreadsheets into high-quality PDFs without writing a single line of VBA code.
Frequently Asked Questions
Why does my VBA macro attach an older version of the PDF?
This usually happens because the macro fails to overwrite the existing file during the export process, often due to the file being open in another program. Ensure the PDF is closed before running the macro, or add a line of code to delete the existing file before exporting.
Can I export just a specific cell range to a PDF using VBA?
Yes. Instead of applying the ExportAsFixedFormat method to the ActiveSheet or ActiveWorkbook, you can apply it directly to a specified range, such as Worksheets("Sheet1").Range("A1:D20").ExportAsFixedFormat.
What causes a 'File Not Found' error when attaching a document in Outlook VBA?
A 'File Not Found' error occurs if the file path is misspelled, if the directory does not exist, or if the code attempts to attach the document before the operating system has finished saving it to the hard drive. Using a unified path variable and sometimes adding a brief pause (DoEvents) can resolve this.
