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)
'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
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âŚ
2. Select âMicrosoft Outlook XX.X Object Libraryâ and click on âOKâ button
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âŚâ
8. Select âSendOutlookEmailsâ from the list and click on âOkâ button
9. Done
VBA Tools To Create Folders In VBA, you can use MkDir function to create folders in your system or shared drive. To make your work simple, we bring a free Excel VBA tool to create…
Working with huge data is always exciting and challenging. From 2007 version onward, Excel is supporting more than a million rows in each worksheet. One of the common problems with huge data is âDuplicatesâ and the bigger problem is to identify and remove these duplicates. In this article, we will be sharing 4 ways to delete duplicate records from your data.
VBA Code to Find Last used Column or Row in Excel Sometimes as a developer, you need to take actions in Excel sheets based on last row or column. In Excel, there are two kinds…
VBA CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
In this article we will learn about VBA code to get computer name. Excel VBA, or Visual Basic for Applications, is a programming language that can be used to automate tasks within the Microsoft Excel…