VBA Code to Send Outlook Emails

Outlook Tricks VBA

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

 

Excel Tricks

 

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)
        'To
        objMail.To = Sheet1.Range("A" & lCounter).Value
        'Cc
        objMail.CC = Sheet1.Range("B" & lCounter).Value
        'Subject
        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
        objMail.Send
        'Close the object
        Set objMail = Nothing
    Next
    'Show confirmation message to user
    MsgBox "Done", vbInformation
End Sub

 

 

Excel Tricks

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:

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

 

Excel Tricks

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

 

Excel Tricks

Outlook Bulk Email Tool

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…’

 

Excel Tricks

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

 

Excel Tricks

9. Done

 

Outlook Email Management Tool

Leave a Reply

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