VBA & Macro Problems

How to Bulk Email Individual PDF Files with Excel VBA and Outlook

Muhammad Talha Oct 22, 2026 869 views

Question details

The user needs to automate sending multiple individual PDF files to different recipients via email using Excel VBA and Outlook.

How to Bulk Email Individual PDF Files with Excel VBA and Outlook

Product: Excel, Outlook
Device & OS: not provided
Scenario: Automating bulk emails where each email contains a specific PDF attachment, is sent to a unique recipient, and uses the PDF filename as the subject line without a message body.
Observed behavior: The goal is to successfully execute a VBA macro that loops through a spreadsheet of contacts and file paths, triggering Outlook to send the individual emails automatically.

Before you start

Ensure that Microsoft Outlook is installed and configured with your email account on your computer, and organize your Excel data with dedicated columns for recipient email addresses, subjects, and the exact local file paths of the PDF attachments.

Solution 1Recommended

Use an Excel VBA Macro to Automate Outlook Emails

This method uses a VBA script to loop through your Excel spreadsheet, creating and sending an individual Outlook email with a PDF attachment for each row.

This approach relies on the Microsoft Outlook Object Library to control Outlook directly from Excel. It requires your data to be structured systematically, allowing the loop to read the required properties cell by cell.

Open the VBA Editor

Open your Excel workbook containing the recipient data and press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Enable the Outlook Object Library

In the VBA Editor, click 'Tools' in the top menu, then select 'References'. Scroll down the list, locate 'Microsoft Outlook XX.X Object Library' (the version number may vary), check the box next to it, and click 'OK'.

Create a New Module

Click 'Insert' > 'Module' to add a blank script window. This is where you will write or paste the VBA code that loops through your target rows.

Write the VBA Loop

Define an Outlook Application object and a MailItem object in your code. Set up a 'For' loop to iterate through the rows of your worksheet, reading the recipient address, subject line, and PDF file path from their respective cells.

Configure and Send the MailItem

Inside the loop, assign the MailItem's '.To' property to the recipient, the '.Subject' property to the PDF filename, and use '.Attachments.Add' with the retrieved file path. Finally, use the '.Send' command to dispatch the email.

Use an Excel VBA Macro to Automate Outlook Emails

Testing Your Macro: Before running the macro on your entire mailing list, replace the '.Send' command with '.Display' in your VBA code. This allows you to visually review the generated emails and ensure the correct PDFs are attached without actually sending them.

Automate Workflows with WPS Office Spreadsheet VBA

WPS Office Spreadsheet includes robust VBA support, allowing you to run macros and automate repetitive tasks like bulk emailing just as you would in Microsoft Excel.

1. Download and Install: Download and install WPS Office, then open your macro-enabled workbook (.xlsm) in WPS Spreadsheet.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros' to open the VBA Editor.
3. Add Your Code: Paste your bulk email VBA code into a new module and ensure the Outlook object references are properly enabled.
4. Run the Automation: Run the macro to automatically generate and send your personalized PDF emails via the Outlook application.

Seamlessly execute VBA macros to automate complex workflows.Highly compatible with Microsoft Excel macro-enabled (.xlsm) formats.Lightweight and runs smoothly without consuming excessive system resources.Free alternative with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail on the '.Attachments.Add' line?

This typically occurs if the file path specified in your Excel cell is incorrect or the PDF file does not exist at that exact location. Double-check that the cell contains the full directory path, including the drive letter and the '.pdf' extension.

Can I use late binding instead of enabling the Outlook Reference manually?

Yes. You can declare your Outlook objects simply as 'Object' and initialize them using 'CreateObject("Outlook.Application")'. This avoids the need to manually check the Microsoft Outlook Object Library in the references menu, which is helpful when sharing the workbook, though you will lose auto-complete features in the VBA editor.

How do I prevent Outlook's security warning pop-ups when sending emails via VBA?

Outlook may display a prompt warning that a program is trying to send an email on your behalf. To prevent this, ensure your antivirus software is up-to-date and recognized by the Windows Security Center, or consider using third-party utility tools like ClickYes to auto-dismiss the prompts.

Can I add a custom message body to each email later?

Yes. While the current requirement is to leave the body blank, you can easily modify your VBA loop to include text. Just set the '.Body' or '.HTMLBody' property of the MailItem object to your desired message before the '.Send' command.

Excel & Data Problems