Do you have to send an email every Monday to your team members to remind them of tasks? Do you have a client list in Excel and want to use it to send personalized emails? Do you want to send an automatic email to wish birthdays? Would you like to send recurring payslips or common information to share?
The solution, therefore, goes through VBA macros.
It is easy through VBA to create applications and therefore objects of the Outlook messaging tool. You can, thanks to a macro in Excel, automatically create and send emails from the Outlook application.
If you have recurring emails to send and which require some data processing from other Excel workbooks, why not automate these tasks? It is possible and easy to set up and unlike other tools, it is completely free.
Before starting, it is best to make sure that the following reference Microsoft Outlook X.0 Object Library is activated in your VBA.

Create and send a email in VBA
To create an email using Outlook with Excel through VBA you must first use the CreateObject function which creates and returns a reference to an ActiveX object. Here we create an Outlook application with the CreateItem method.

To create an email, you must then create an item mail in this application. You can then use the methods and properties of the mail object (such as recipients, subject, body of the mail, display, automatic sending …).
Again, don’t forget the best practices of turning off alert messages as well as refreshing the screen.
Sub SendMail()
'Send automatically an email
'Deactivate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'Initialisation of variable
Set WbMacro = ActiveWorkbook
WbMacro.Activate
'Fill with right data
MailAdressTo = "" 'To
MailAdressCc = "" 'Cc
MailBody = ""
MailSubject = ""
'Create a mail
Set OutApp = CreateObject("Outlook.Application") 'Create an outlook app
Set Outmail = OutApp.CreateItem(0) 'Create the mail
With Outmail
.SentOnBehalfName = "" 'Address mail used to send the mail
.To = MailAdressTo 'Recipients
.CC = MailAdressCc 'Recipients in copy
.BCC = "" 'Recipients in hide copy
.Subject = MailSubject 'Subject of the mail
.Display 'Display the mail
.Send 'If it is written the mail is send automatically
End With
'Cleaning variables
Set OutApp = Nothing
Set Outmail = Nothing
'Reactivate at the end
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Save an email as a draft without sending
Backing up an email automatically without sending with VBA can be useful, to send the mail later. To do this, simply use the Save method to your mail object.
Sub CreateAndSaveMailAsDraft()
'Save an email as a draft without sending
'Declaration
Dim OutApp As Object
Dim Outmail As Object
'Initialisation
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)
With Outmail
.Body = ""
.Attachments.Add ""
.Subject = ""
.To = ""
.Save 'to save the mail
End With
'Cleaning variables
Set OutApp = Nothing
Set Outmail = Nothing
End Sub
Send a workbook as an attachment by email in VBA
In the example below using the Attachments.Add method of the email object that we have created through an Outlook application, we can attach any document, giving its path to our mail. Here, the email is then displayed but not sent automatically.
To send several attachments in the same email, simply use the Attachments.Add method several times in your email object.
To send the email automatically without proofreading, you must also use the Send method.
Sub WriteMailWithAttachFile()
'Mail with several attachments
'Deactivate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'Initialisation of variable
Set WbMacro = ActiveWorkbook
WbMacro.Activate
'Create a mail
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)
With Outmail
.SentOnBehalfName = ""
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.Attachments.Add "C:\Documents\Accounting.xlsx" the attach method with a xlsx file
.Attachments.Add "C:\Documents\Finance.pdf" the attach method with a pdf file
.Attachments.Add "C:\Documents\Management.docx" the attach method with a docx file
.Attachments.Add "C:\Documents\Business.txt" the attach method with a txt file
.Display 'Display the mail without sending
End With
'Cleaning variables
Set OutApp = Nothing
Set Outmail = Nothing
'Reactivate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Layout your email using HTML in VBA
To personalize and format your email, you can use HTML code inside your module.
HTML (HyperText Markup Language) is a computer language used on the Internet. This language is used to create web pages.
After creating your email, you would like to format, bold or even colour words. To format the mail, you must use the HTML language inside your VBA code and use the property HTMLBody of your mail object.
In this example, we create a function BuilHtmlBody and we call the function for the properties HTMLBody.
Sub WriteMailWithHtml()
'Layout your email with HTML code
'Deactivate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'Declaration
Dim OutApp As Object
Dim Outmail As Object
Dim MailAdressTo, MailAdressTCc, MailBody, MailSubject As String
'Initialisation
Set WbMacro = ActiveWorkbook
WbMacro.Activate
MailAdressTo = ""
MailAdressTCc = ""
MailBody = ""
MailSubject = ""
'Create a mail
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)
With Outmail
.SentOnBehalfName = "" 'you address mail
.To = MailAdressTo 'To
.CC = MailAdressCc 'Cc
.BCC = "" 'Bcc
.Subject = "Weather Maps..."
.BodyFormat 2 ' HMTL Body Format
.HTMLBody = BuildHtmlBody() 'Call the Html function
.Display
End With
'Cleaning variables
Set OutApp = Nothing
Set Outmail = Nothing
'Reactivate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Function BuildHtmlBody()
'Function with the format of the mail using HTML code
'Declarations of variables
Dim html As String
'We fill the HTML string variable as we go along
'by incrementing it with character strings
html = "<!DocType html><html><body>"
html = html & "<div style =""font-family:'Segoe UI',Calibri,Arial,Helvetica; font-size: 14px; max-width:768px;"">"
html = html & "Dear all, <br /><br />You cannot have a positive life and a negative mind. Joyce Meyer.<br/><br />"
html = html & "<B> <U> Quote </B></U><br /><br />"
html = html & "<ul><li></li><li></li><li></li></ul>"
html = html & "<B><p><font color=green> Author </font></p></B><br /><br />"
html = html & "<B><p><font color=gray> Date </font></p></B><br /><br />"
html = html & "<table style='border-spacing: 0px; border-style: solid; border-color : #ccc; border-width: 0 0 1px 1px;'>"
html = html & "</table></div></body></html>"
End Function
Add an image in the body of the mail with HTML
To format your mail, and make it more attractive to read, it is often useful to insert images. Our eyes are very attached to the image reading.
To add an image in the body of the email, simply add this image first as attachments, then use the HMTL tags in the HTMLBody method to insert the image. Most of the code is in this line: “src = ‘cid:“.
Sub WriteMailWithHtmlAndAnImageinBody()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set WbMacro = ActiveWorkbook
WbMacro.Activate
MailAdressTo = ""
MailAdressTCc = ""
MailBody = ""
MailSubject = ""
'Create a mail
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)
With Outmail
.SentOnBehalfName = ""
.To = MailAdressTo
.CC = MailAdressCc
.BCC = ""
.Subject = "Weather Maps ..."
.BodyFormat 2 ' HMTL Body Format
.Attachments.Add "C:\Users\VBASKILLS\News.jpg", olByValue, 0
.HTMLBody = .HTMLBody & "<br><img src='cid:MyImage.jpg'><br>"
.Display
End With
'Cleaning variables
Set OutApp = Nothing
Set Outmail = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Add contact in your contact mail list
You receive an Excel file with a list of several thousand contacts that you will need. You want to add them automatically to your Outlook contact list.
Using the Outlook application and the MAPIFolder object, we will be able to access the contact list and add the new data.
Sub AddContactinContactList ()
'Declaration
Dim OutApp As New Outlook.Application
Dim NameSpace As NameSpace
Dim Folder, ContactFolder As MAPIFolder
Dim NewContact As Outlook.ContactItem
Dim iRow, NbRows As Long
Dim ColFirstName, ColLastName, ColMail As String
'Initialisation
Set OutApp = CreateObject("Outlook.Application")
Set NameSpace = OutApp.GetNamespace("MAPI")
Set Folder = NameSpace.Folders("Common files")
Set ContactFolder = Folder.Folders("Common contacts")
ColFirstName = 2 'Column with first name data in your sheet
ColLastName = 3 'Column with Last name data in your sheet
ColMail = 4 'Column with mail addresses data in your sheet
NbRows = 1000 'Number of rows in your sheet
'Loop in your excel list to add each row as a new contact
For iRow = 1 To NbRows
Set NewContact = ContactFolder.Items.Add(olContactItem)
With NewContact
.FirstName = Cstr(Cells(iRow,ColFirstName).Value2)
.LastName = Cstr(Cells(iRow,ColLastName).Value2)
.Email1Address = Cstr(Cells(iRow,ColMail).Value2)
.Save 'Save the new contact
End With
Next iRow
'Close Outlook Application
OutApp.Close
'Cleaning variables
Set OutApp = Nothing
Set NewContact = Nothing
End Sub
VBA in Outlook Application
VBA can be used in all applications of the Microsoft Office suite. This blog is focused on using VBA for Excel and communicating from Excel with other tools, but note that you can also create your code modules directly in the Outlook application.
For example, you could create a code that will allow you to be defined as rules, and that will automatically sort certain mail.
You can also create a code that says that if the subject of the message is as follows, then automatically save the attachment to a specified folder on your computer.
Below an example of a VBA function which can extract the attach file depending on the subject’s mail. Then, you can define this code as a messaging rule which will be applied as soon as your application is open.
Sub SaveMailAttachment (outmail As Outlook.MailItem)
'Declaration
Dim Inspector As Outlook.Inspector
Dim Outmail As Outlook.MailItem
Dim Attachment As Outlook.Attachments
'Initialisation
'The inspector is the window in which an Outlook item is displayed
Set Inspector = Application.ActiveInspector
If not TypeName(Inspector) = "Nothing" Then
If TypeName(Inspector.CurrentItem) = "MailItem" Then
Set Outmail = outmail
Set Attachment = Outmail.Attachments
AttachmentItem(1).SaveASFile "C:\Documents\" & _
Attachments.Item(1).DisplayName
End If
End If
End Sub
The application of VBA can be extended to multiple needs.