Hello, Tech Enthusiasts!
In our professional lives, we are frequently seeking methods to automate mundane tasks to save time and enhance productivity. Email communication is one of those tasks that, despite its importance, can sometimes be repetitive. Whether it’s sending regular reports, team updates, or reminders, wouldn’t it be fantastic if we could automate it?
In this blog post, we’ll walk you through a step-by-step process to use VBA (Visual Basic for Applications) to send emails from Outlook, with the recipients list in an Excel sheet. Let’s get started!
What Do You Need?
- An installed and configured Outlook client on your system.
- Microsoft Excel: We will use VBA, which is built into Microsoft Excel.
- A list of recipients in an Excel sheet.
Getting Started
Step 1: Enable Developer Tab
First, we need to enable the Developer tab in Excel to access VBA. Go to File > Options > Customize Ribbon, and then check the box that says “Developer”.
Step 2: Open the VBA Editor
Next, open your Excel sheet where you have the list of recipients. Click on the Developer tab, and then select “Visual Basic” or simply use the shortcut Alt + F11.
Step 3: Insert a New Module
In the VBA editor window, go to Insert > Module. This will insert a new module where you will write your VBA script.
Step 4: Write the VBA Script
In the new module, you will write your VBA script:
Sub Send_Emails()
Dim olApp As Object
Dim olMail As Object
Dim ws As Worksheet
Dim lRow As Long
Dim sTo As String
Dim sCC As String
Dim sSubject As String
Dim sBody As String
' Set reference to the sheet and the last row
Set ws = ThisWorkbook.Sheets("Sheet1")
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Create a new Outlook session
Set olApp = CreateObject("Outlook.Application")
' Loop through each row
For i = 2 To lRow
sTo = ws.Cells(i, 1).Value
sSubject = "Automated Email Using VBA"
sBody = "Hello, this is a test email sent using VBA and Outlook!"
' Create a new mail item
Set olMail = olApp.CreateItem(0)
With olMail
.To = sTo
.Subject = sSubject
.Body = sBody
.Send
End With
' Clean up
Set olMail = Nothing
Next i
Set olApp = Nothing
End Sub