Send Bulk Emails with Attachments from Excel and Outlook

Sending bulk emails with different attachments from Excel, VBA and Outlook is sometimes a real need, and we wonder why Microsoft hasn't built it in. That's exactly why we've covered this topic here on the blog and on my YouTube channel.

We previously published a post about this kind of tool combination (Word, Excel, Outlook) for sending bulk emails, which you can see here. Below, we'll go through a combination of Excel and Outlook using a macro that lets you send up to 500 emails every 24 hours, with attachments taken from a folder.

Get the template and the code in the store

How Do You Send Emails from Excel?

Sending email from Excel requires communicating with other Microsoft tools, specifically from Office, including the Outlook email client (not to be confused with the website).

To send emails from Excel with attachments to different accounts, we need to write code in the macro language better known as VBA.

Below, we'll give you a solution so you can send emails from a template containing the list of main recipients, the list of files you want to attach and the email accounts you want to send a copy or a blind copy to.

How Does the Email Macro Work?

The first thing to understand is that the template uses a macro that connects to Outlook through a library, which lets us use all the features of the Outlook email client to send bulk emails.

The first thing to check is that the files you want to attach are in a folder defined beforehand. These files can be of any type.

Learn how to mail merge into separate documents

Next, we need to add the following macro code to the template.

---

---

Get the template and the code in the store

Finally, we add a button or a shape to run the macro and start sending the bulk emails.

More Features of the Email Template

1. Send the Email to More Than One Recipient

To send an email to more than one recipient, use a semicolon ";" between each email address. This also works for CC and BCC (CCO in Spanish) recipients.

Get the template and the code in the store

2. Send Copies of the Emails to CC and BCC Recipients

This feature makes it easier to verify and track the emails you send, since sometimes we need to send them to other people so they can follow up or keep a record of the work.

3. Customise the Subject of Each Email

We know every email has its own purpose and even different underlying information, which is why the template lets you include a different subject for each recipient, using a column in the template.

4. Attach More Than One File

1. Prepare the Excel sheet:

First of all, make sure your Excel sheet is well organised and contains all the information needed to send the emails. This includes:

• Recipient email addresses in one column.
• Names of the files to attach, separated by a specific delimiter (such as a vertical bar |), in another column.
• Subject, message body, CC and BCC (optional), each in its own column.

2. Select the folder containing the files:

The macro should let the user select the folder where the files to attach are stored. This is typically done with a folder-picker dialog box.

3. Read and process each row of the sheet:

The macro should loop through each row of the Excel sheet that contains email data. For each row:

• Create a new email.
• Read the names of the files to attach from the corresponding cell and split them using the defined delimiter (|).

4. Find and attach the files:

For each file name listed in the sheet:

• Look for the file in the selected folder.
• If the file exists, attach it to the email.
• It's important to make sure the file name in the sheet matches the name of the file in the folder exactly.

5. Customise the Email Body

You can use this feature in two ways. The first is simply from the VBA code of the bulk email macro.

You can even include HTML code to customise your message.

The second is to adjust the line of code above so you can customise the email body from a column.

---

Prefer to have it ready?

More guides in Excel automation for office work