VBA & Macro Problems

Use Excel VBA to Send Grouped Emails with Multiple Attachments

Muhammad Talha Oct 22, 2026 869 views

Question details

The user wants to automate sending emails via Outlook using Excel VBA, ensuring that multiple attachments intended for the same recipient are grouped into a single email.

How to Use Excel VBA to Send Grouped Emails with Multiple Attachments

Product: Excel
Device & OS: Windows
Scenario: Automating bulk email distribution where specific recipients need to receive multiple distinct file attachments based on spreadsheet data.
Observed behavior: Instead of generating separate emails for every individual attachment, the goal state is to consolidate all relevant files into one Outlook message per recipient before displaying or sending it.

Before you start

Ensure your worksheet is sorted by the recipient's email address (e.g., Column A) so that all attachments for a single person are placed in consecutive rows. Verify that Microsoft Outlook is installed and configured on your computer.

Solution 1Recommended

Use a VBA Loop to Group Attachments by Recipient

Create a single Outlook MailItem per recipient by evaluating consecutive rows. Use a nested loop to attach all associated files before finalizing the email.

To prevent Excel from generating a separate email for every single file, the VBA macro must compare the current row's email address with the active Outlook MailItem. By utilizing a 'Do While' loop, the script continues adding attachments as long as the recipient remains unchanged.

Organize your worksheet data

Ensure your data is sorted by recipient email addresses in Column A. Place the corresponding attachment file names (including extensions) in Column B. This contiguous sorting is required for the loop to correctly group the files.

Open the Visual Basic Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor. Go to the top menu and click Insert > Module to create a new blank script window.

Enable the Outlook Object Library

In the VBA editor, click Tools > References. Scroll down the list, check the box next to 'Microsoft Outlook XX.X Object Library', and click OK. This allows Excel to communicate directly with Outlook.

Paste the grouped email script

Write an outer loop that creates a new MailItem and assigns the email address from Column A. Inside it, create an inner loop that adds the attachment from Column B (using a defined folder path) and increments the row as long as the email address matches the current recipient.

Test and run the macro

Press F5 or click the Run button to execute the script. The macro will process the rows, group the attachments by recipient, and use the 'mail.Display' command to open the draft emails in Outlook for your final review.

Use a VBA Loop to Group Attachments by Recipient

Testing Recommendation: Always use 'mail.Display' instead of 'mail.Send' when running the macro for the first time. This allows you to visually verify that all attachments are grouped correctly before the emails are actually dispatched.

Powerful VBA Support

Automate Email Tasks Effortlessly with WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to execute complex scripts, automate repetitive tasks, and seamlessly connect with tools like Outlook without needing Microsoft Excel.

1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and launch WPS Spreadsheet.
2. Enable Developer Tools: Go to the Developer tab on the top ribbon to access the built-in VBA environment.
3. Write or Import Your Macro: Click on the Visual Basic Editor (VBE), insert a new module, and paste your email automation script directly.
4. Run the Macro Seamlessly: Execute your script to group attachments and send bulk emails instantly.

Fully compatible with Microsoft Excel VBA macros (.xlsm and .xlsb formats)Built-in Developer tab with a complete Visual Basic EditorLightweight installation that runs smoothly on older hardwareFree, familiar spreadsheet interface with no steep learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro creating separate emails for the same recipient?

This usually happens if your spreadsheet rows are not sorted by the recipient's email address. The macro reads rows sequentially; if a recipient's entries are interrupted by another email address, the script assumes it is a new recipient and generates a new email.

How can I automatically send the emails instead of just displaying them?

In your VBA script, replace the `mail.Display` command with `mail.Send`. However, it is highly recommended to use `mail.Display` during initial testing to ensure your nested loops are attaching files to the correct recipients.

What should I do if the macro cannot find the attachment files?

Double-check the folder path variable in your script (e.g., `folder = "C:\path\to\folder\"`) and ensure it ends with a backslash. Additionally, verify that the file names listed in your worksheet column include the correct file extensions, such as .pdf or .xlsx.

Can I add CC or BCC recipients using this VBA script?

Yes. Inside the outer loop where the MailItem is created, you can define additional fields by adding `mail.CC = "email@domain.com"` or `mail.BCC = "email@domain.com"` before the `mail.Display` command.

Excel & Data Problems