VBA to Send Email from Excel with Attachment and Image in Mail Body

Topics covered:

  • Create Excel file and design the ‘Home’ page and user email list table
  • Open VBA window and insert blank module
  • Write VBA code to send automated emails
  • Attach an Excel file to the email
  • Show image in mail body without losing the reference (using content id)
  • HTML to create the mail body

https://youtu.be/SrmOKOneVAo

We can use VBA in Excel to automate our mailing feature to send emails to multiple recipients at a time or send a specific email to all users on a single click.

To automate this email process, we will use MS Excel, VBA, and HTML.

Please follow the below steps to develop this automated tool.

Creating Excel file and design Home page and user email list

Create an Excel file and save it with the name “Email Automation” with extension ‘.xlsm’.

save Excel file

Insert two worksheets in the Excel workbook and rename these ‘Home’ and ‘Email List’ respectively.

Two worksheets for Home and Email List

Design the Home page with required header, image, and a rectangular shape button to run the Macro/VBA.

Home worksheet

Create the list of email and user details in ‘Email List’ worksheet.

Email List worksheet with details

Now, lets move to the VBA to start writing the code.

To open VBA, click on Developer Tab and then click on VBA button available in Code Group. Alternatively, you can also press ALT + F11 as a shortcut key to open VBA window.

Developer Tab

Writing VBA code in Microsoft Visual Basic for Applications window

To write the code, we need to insert a module in current project. To add a blank module, click on Insert menu and then click on Module.

Inserting Module in current project

Sample emails and support files (image and Excel file)

We are almost done with the basic requirement to write the VBA code to automate the email automation. Before moving further, let’s have a look at the sample email, attachment file and image for mail body.

Sample email

Here, we are using TheDataLabs banner (image) in mail body and Excel file as attachment.

To use the image in mail body and Excel as attachment, you need to keep both the files in same folder where you have saved the Email Automation file. Please see the below snapshot.

Excel and Image for usage

Let’s start writing the code in blank module.

Move to module window and start writing the below code for creating and sending/displaying email.

HTML Body code in formatted style for better understanding

Let’s create on more sub routine to start email sending process. We will assign this sub procedure on button available on Home page.

We are done with required code to automate the process. Let’s move to the Excel window and assign the Start_Email sub procedure to the button created on Home page.

Right click on ‘Send Email’ button available on Home page and then click on Assign Macro.

assigning macro on a button

Select the macro name ‘Start_Email’ from the available list and then click on OK button.

Selecting macro

Now, everything this done. You can click on Send Email button to test the tool. Remember, we are currently using Display function while creating the email. Post revieing the emails you can go back to ‘SendEmail’ procedure and uncomment Send and comment Display.

Click on below button to download the demo tool and sample files.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here