How to bulk send emails from excel spreadsheet?

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