Mail Outlook

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.

*La Tournette, Alpes, France – Photo taken by me*

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.

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (Address never made public)

Name

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s