Sending Emails from Excel using VBA Macro and Outlook

I had got a task of sending emails to the users listed in an Excel spreadsheet. Each email had to contain some information individual for each user. I tried to do it using a VBA macro in Excel that sent emails from a configured Outlook profile on the computer. Here is my solution.

Suppose, you have an Excel file with the following columns:

Email | Full Name | Last Password Change Date | Account status

My task is to send such an e-mail from this template to each user in the list:

Create a new macro: View -> Macros. Specify the name of the macro (send_email) and click Create:

Copy and paste the following code to the VBA editor that appears (I have made relevant comments to it). To automate the sending of emails, I’ll use CreateObject (“Outlook.Application”) function that allows to create and use an Outlook object inside VBA scripts.

Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' Subject
strSubj = "Your account status on woshub.com domain"
On Error GoTo dbg
' Create a new Outlook object
Set olApp = CreateObject("Outlook.Application")
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
' Create a new item (email) in Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 1).Value
FullUsername = Cells(iCounter, 2).Value
Status = Cells(iCounter, 4).Value
pwdchange = Cells(iCounter, 3).Value
'Make the body of an email
strBody = "Dear " & FullUsername & vbCrLf
strBody = strBody & " Your account in woshub.com domain is in" & Status & “ state” & vbCrLf
strBody = strBody & "The date and time of the last password change is" & pwdchange & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 1
' 1 – text format of an email, 2 -  HTML format
olMailItm.Body = strBody
olMailItm.Send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Save this Excel file as .xlsm (a format of Excel workbook supporting macros). To send emails, select the created procedure (the macro) and click Run.

The macro will go through all rows on the Excel worksheet one by one, generate and send an email to the each recipient in the list.