VBA Code to send Outlook Emails
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
Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim lCounter As Long
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
objMail.Body = Sheet1.Range("D" & lCounter).Value
objMail.Attachments.Add (Sheet1.Range("E" & lCounter).Value)
'Close the object
Set objMail = Nothing
'Show confirmation message to user
MsgBox "Done", vbInformation
VBA Code to send Outlook Emails Explanation:-
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
Download Practice File
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Excel VBA Course : Beginners to Advanced
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community