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
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
Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…
VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…
Table of Content Introduction Benefits of Using Outlook and Excel for Work Allocation Setting Up Your Outlook-Based Excel Tool Managing Work Allocation Conclusion Download Free Excel Template Introduction Efficient work allocation is crucial for organizations…
Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…
VBA Code To Delete All Shapes On A Excel sheet Here is a VBA code which deletes all the shapes from an Excel sheet. Code is simple but you have to be bit careful while…