How to Use an Excel VBA Macro to Email Each PDF as a Separate Outlook Message
Question details
The user needs to automate the process of scanning a specific folder and sending each PDF file as a separate email attachment using an Excel VBA script and Microsoft Outlook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating repetitive email distribution tasks where multiple distinct PDF documents stored in a directory must be attached to individual emails and sent to specified recipients.
- Observed behavior
- The user requires a macro that loops through folder contents, filters for PDF extensions, generates a new Outlook mail item for each file, dynamically populates email fields, and executes the send command.
Ensure that the desktop version of Microsoft Outlook is installed, configured with an active email profile, and set as your default mail client before running automation scripts.
Use a Late-Bound VBA Script to Automate Outlook Emails
Implement a VBA macro utilizing late binding to loop through a folder and send each PDF file without requiring manual object library references.
Late binding using the CreateObject function is highly recommended for scripts that will be shared across different computers. It prevents version compatibility errors because it does not require you to explicitly activate the Microsoft Outlook and Scripting Runtime libraries in the VBA editor.
Press ALT + F11 in your Excel workbook to open the Visual Basic Editor, then click 'Insert' > 'Module' to create a new blank script window.
Declare your variables and initiate the Scripting FileSystemObject and Outlook Application using CreateObject. For example: Set fso = CreateObject("Scripting.FileSystemObject") and Set olLook = CreateObject("Outlook.Application").
Map the target folder using fso.GetFolder(ThisWorkbook.Path) or specify a custom directory path. Define your email variables (To, CC, Subject, Body) by referencing specific worksheet cells, such as recipient = Range("L37").Value.
Use a 'For Each file In folder.Files' loop. Add an IF statement to check the file extension: If LCase(fso.GetExtensionName(file.Name)) = "pdf" Then. Inside this condition, create the mail item, add the file path as an attachment, and execute the .Send command.
Return to your worksheet, go to the Developer tab, insert a Form Control button, and assign your new macro to it for easy execution.
Enable Object References for Early Binding
Activate the required Microsoft object libraries to benefit from code auto-completion and IntelliSense while building your macro.
Automate Complex Workflows with WPS Spreadsheet Macros
WPS Office fully supports standard VBA macros in its Spreadsheet application, allowing you to seamlessly run automation scripts that interact with other programs like Outlook. Enjoy a highly compatible and efficient environment for your data automation needs.
- 1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm workbook containing the Outlook automation script.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, enable it in the WPS settings menu.
- 3. Run or Edit Your Macros: Click on 'Macros' to run your script directly, or click 'Visual Basic Editor' to tweak the folder paths and cell references exactly as you would in standard Excel.
Frequently Asked Questions
How do I change the macro to send Word documents instead of PDFs?
You can modify the file extension filter in your VBA script. Change the condition to: If LCase(fso.GetExtensionName(file.Name)) = "docx" Then. This will instruct the macro to only process and attach Microsoft Word documents.
Can I hardcode the target folder path instead of using the workbook's directory?
Yes. Instead of using 'ThisWorkbook.Path', you can explicitly state the path by modifying the folder declaration to: Set folder = fso.GetFolder("C:\Users\YourUsername\Documents\MyPDFs"). Ensure the path is enclosed in quotation marks.
Will this macro execute if Microsoft Outlook is closed?
Yes, using the 'CreateObject("Outlook.Application")' method will launch a background process of Outlook if it isn't already running, process the email generation, and send the messages utilizing your default email profile.
Why is the macro stopping and displaying a user-defined type not defined error?
This error occurs if you are using early-bound declarations (like 'Dim olLook As Outlook.Application') without activating the necessary object libraries. You must either enable the 'Microsoft Outlook Object Library' via Tools > References, or rewrite the code using late binding ('Dim olLook As Object').
