How to bulk send emails from excel spreadsheet?

automation feed
5 min readAug 16, 2019

--

Excel automation

While we can do many things with excel , such as storing data or creating a bar graph. Have you ever wonder if it was possible to send an email from your own excel spreadsheet with vba?In this article , I will share with you my attempt to to bulk send emails in excel.

First let’s create an excel spreadsheet …

In this excel spreadsheet there are 9 columns . This is just a very basic example that I thought of ... , you can customize the column in any way that you deem fit.

Column A : Receiver’s email address

Column B: First name of the receiver

Column C: Last name of the receiver

Column D:CC

Column E:Email’s subject

Column F:Email’s content

Column G:Any attachment’s path address i.e Pictures,videos etc

Column H:Date to send the email

Column I:Status of the email(Sent or not Sent)

So , the idea of this project is to create a script to send an email to the receiver at a specific date ,with the CC , body and attachment of the email filled according to your spreadsheet.

It’s finally time to delve into the vba code. For starters ,make sure that you have the developer tab on the excel toolbar because it is not going to be on display by default. Check this url on how to set up the developer tab on excel. Also , remember to save your excel spread sheet as macro-enabled.

Now open visual basic ,or ALT+11 and create a new module, this is where all your code will be written in . Now copy and paste the code below.

Sub send_email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.Application")
Dim each_row As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

For each_row = 2 To last_row
Set msg = OA.createitem(0)
msg.To = sh.Range("A" & each_row).Value
first_name = sh.Range("B" & each_row).Value
last_name = sh.Range("C" & each_row).Value
msg.cc = sh.Range("D" & each_row).Value
msg.Subject = sh.Range("E" & each_row).Value
msg.body = sh.Range("F" & each_row).Value
date_to_send = sh.Range("H" & each_row).Value
date_to_send= Format(date_to_send, "dd/mm/yyyy")
Status = sh.Range("I" & each_row).Value
current_date = Format(Date, "dd/mm/yyyy")
If date_to_send = current_date Then
If sh.Range("G" & each_row).Value <> "" Then
msg.attachments.Add sh.Range("G" & each_row).Value
Cells(each_row, 9).Value = "Sent"
Content = Replace(msg.body, "<>", first_name + " " + last_name)
msg.body = Content
msg.send
Else
Cells(each_row, 9).Value = "Sent"
Content = Replace(msg.body, "<>", first_name + " " + last_name)
msg.body = Content
msg.send
End If
End If

Next each_row
End Sub

For starters, let me write out a simple explanation for the code.

    Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.Application")
Dim each_row As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

So in this code extract, we create an object -sh that refers to “Sheet1” which is the spreadsheet that you are working on, an object called OA, msg ,an integer called each_row and last_row(refers to the last filled up row in you excel spreadsheet). I also want to highlight that

Set OA = CreateObject("Outlook.Application")

This code links outlook and excel , which is essentially the most important piece of code in this project.

For each_row = 2 To last_row
Set msg = OA.createitem(0)
msg.To = sh.Range("A" & each_row).Value
first_name = sh.Range("B" & each_row).Value
last_name = sh.Range("C" & each_row).Value
msg.cc = sh.Range("D" & each_row).Value
msg.Subject = sh.Range("E" & each_row).Value
msg.body = sh.Range("F" & each_row).Value
date_to_send = sh.Range("H" & each_row).Value
date_to_send= Format(date_to_send, "dd/mm/yyyy")
Status = sh.Range("I" & each_row).Value
current_date = Format(Date, "dd/mm/yyyy")

Next each_row

Subsequently in this extract, we loop from the 2nd row till the last row and find the value of each cell from column A-I and store them as their respective variable. We also create a variable called current_date which is equivalent to today’s date.

If date_to_send = current_date Then
If sh.Range("G" & each_row).Value <> "" Then
msg.attachments.Add sh.Range("G" & each_row).Value
Cells(each_row, 9).Value = "Sent"
Content = Replace(msg.body, "<>", first_name + " " + last_name)
msg.body = Content
msg.send
Else
Cells(each_row, 9).Value = "Sent"
Content = Replace(msg.body, "<>", first_name + " " + last_name)
msg.body = Content
msg.send
End If
End If

Next, we will need to check if the date_to_send = current_date, we will send the email only if they match. Also, in this step we will replace “<>” in Column F:Email’s content to the receiver’s name and change the Status to Sent,last but not least,we will send the message. I will recommend that you add a button on the excel sheet and assign the send_email() macro to it ,so that you will be able to send email directly from the button.

For starters, this is what I am referring to. Click developer tab>>insert>>button and drag the button to the spreadsheet. Make sure that the button is located above the last_row so that it will not create any issues in the backend code. This is how it should look like.

Now, right click on the button >>assign macro>>click send_email. Thus,whenever you need to run this function ,just click the button.

Now let’s verify it on outlook!

👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽

CONGRATULATIONS !!! You have just automated sending emails with vba!!

Link for spreadsheet https://docs.google.com/spreadsheets/d/1hWFCbBU3yYg45OgkWhCUQ1H2_3g-v7Xq/edit#gid=950732714

Link for source code

https://github.com/kaikiat/automation/blob/master/excel_automation/Module1.bas

--

--