VBA Code to send Outlook Emails

Complete Excel VBA Course

Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may require to add attachments to the emails. Here is a VBA code that does this task, below VBA code reads recipients, subject, mail body, and attachment details from the Excel sheet and sends emails through MS Outlook.


Public Sub SendOutlookEmails()
'Microsoft Outlook XX.X Object Library is required to run this code
'Variable declaration
Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim lCounter As Long
'Set objects
Set objOutlook = Outlook.Application
'Read details from Excel sheet and send emails
For lCounter = 6 To 8
'Create a new email item
Set objMail = objOutlook.CreateItem(olMailItem)
objMail.To = Sheet1.Range("A" & lCounter).Value
objMail.CC = Sheet1.Range("B" & lCounter).Value
objMail.Subject = Sheet1.Range("C" & lCounter).Value
'Email Body
objMail.Body = Sheet1.Range("D" & lCounter).Value
'Add Attachment
objMail.Attachments.Add (Sheet1.Range("E" & lCounter).Value)
'Send email
'Close the object
Set objMail = Nothing
'Show confirmation message to user
MsgBox "Done", vbInformation
End Sub

VBA Code to send Outlook Emails Explanation:-

It is worth to mention that you must have MS Outlook installed in your system to use this code and you also need to add Office reference (Microsoft Outlook XX.X Object Library) in Excel VBA from Menu Bar (Tools>References…). Below are the steps to add Office reference in Excel VBA:

Complete Excel VBA Course

1. From the Menu Bar, click on Tools > References…

send outlook emails

2. Select ‘Microsoft Outlook XX.X Object Library’ and click on ‘OK’ button

send outlook emails

To use this code in your Excel file, follow below steps:



1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module
5. Now add a shape in Excel sheet
6. Give a name to the shape like ‘Draft Outlook Emails’
7. Right click on the shape and select ‘Assign Macro…’

Send outlook emails

8. Select ‘SendOutlookEmails’ from the list and click on ‘Ok’ button

Send outlook emails

9. Done

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *