Excel Outlook Automation – How to integrate Excel and Outlook

Excel Outlook automation is the way to send data driven emails. You can easily bind Outlook application with Excel application. In this tutorial i will show you how you can bind the two application together and  few examples of what operations you can perform on outlook by using Excel VBA.

Binding Applications –

First thing you need to do is to have applications talk to each other. There are two ways you can bind them together.

  1. Early Binding

    You can go in VBA Reference and add the Outlook library file reference. After adding reference, you can directly create the Outlook Application object by using the code showing below.

    Dim ObjOutlook as Outlook.Application
    Set objOutlook = New Outlook.Application

    Now, You got your object created and both applications are ready to talk to each other by using the objOutlook object.

  2. Late Binding

    You can use CreateObject() function to call any other application object. This function will find the object library itself and will create object. you can use the code showing below.

    Dim ObjOutlook as Object
    Set objOutlook = CreateObject(“Outlook.Application”)

Sending Emails –

Now, when applications are ready to work together you can perform some action. Next thing after Outlook Application is an email item. you can create an email Item by using the following code.

Dim ObjOutlook as Outlook.Application
Set objOutlook = CreateObject(“Outlook.Application”)

Dim objItem as Object
Set objItem = objOutlook.CreateItem(0) ‘– Zero is email item. you can create other items too like appointments and all

Think of this objItem is like you have a blank email opened and ready to enter Receipent, Subject, Mail body etc. You can supply same parameters by VBA like showing in program below.

With Objitem
      .Subject =”This is an automated email”
      .To =”MyEmailGroup@gmail.com”
      .Body = “Hi Everyone, Hope your learning is going good”
      .Send ‘– You can use .Display if you just want to open the email and save it in Draft.
End With

You can use this code snippet in your loop and make the parameters dynamic to send bulk emails ( limited based on your service provider)

Looping Mails in Inbox ( Save All Attachments)

Ofcourse, You can iterate every email in your inbox by VBA. first thing you need to is to access the folder you want to loop.

Dim ObjFolder as Object
Dim objItem as Object
Dim strFolderPath as String
Dim objAttachment as Object

Set objFolder = objOutlook.GetNameSpace(“MAPI”).GetDefaultFolder(6) ‘– 6 is the default index for inbox

Now you can iterate every item of this folder.

strFolderPath = “YourFolderPathHere”
For each objItem in objFolder.Items

     If objItem.Attachments.Count>0 Then
            For Each objAttachment in ObjItem.Attachments
                 objAttachments.SaveAs strFolderPath & Application.PathSeparator & objAttachment.DisplayName
            Next objAttachments
     Endif
Next objItem

Using Multiple Email Acconts in Outlook

Most of the time, we have multiple email accounts configured in outlook. When you automate emails through VBA. It uses the default email account. but there is a way that you can choose which email account you want to use to send your emails. Have a look at this function.

Function GetOutlookAccount(objOutlook As Object, strEmailId As String) As Object
   Dim objOAccount As Object
    For Each objOAccount In objOutlook.Session.Accounts
        If objOAccount.DisplayName = strEmailId Then
            Set GetOutlookAccount = objOAccount
            Exit For
        End If
    Next objOAccount
End Function

In above function, you need to pass your Outlook Object and the email address you want to use to send your emails and it will return the Account object to set to sending emails account. this is how you will use this

Dim objSendingAccount as Object
Set ObjSendingAccount =GetOutlookAccount(objOutlook,”YourEmailAddress”)
With Objitem
      Set .SendUsingAccount = ObjSendingAccount
      .Subject =”This is an automated email”
      .To =”MyEmailGroup@gmail.com”
      .Body = “Hi Everyone, Hope your learning is going good”
      .Send ‘– You can use .Display if you just want to open the email and save it in Draft.
End With

Format Email Body –

If you need to send a formatted email you need to create an HTML email body. Instead of .Body you will use .HTMLBody. See the code below

With Objitem
      Set .SendUsingAccount = ObjSendingAccount
      .Subject =”This is an automated email”
      .To =”MyEmailGroup@gmail.com”
      .HTMLBody = “YourHTMlCodeHere”
      .Send ‘– You can use .Display if you just want to open the email and save it in Draft.
End With

Send Excel Range/Chart in Email Body – 

Most of the time, We want to send emaill with the reports showing on email body. but creating a formatted email body is too difficult, But dont worry, there is a way to send the Excel range directly into email body without even copy paste.

There is a feature in Excel called “Envelope”. this Envelope is the copy of an Outlook Email Item. we can use this to send selected range on email body. see the code below.

Sub SendEmail()

      ThisWorkbook.Worksheets(“YourSheetName”).Range(“RangeYouWantToSendInEmailBody”).Select
      ThisWorkbook.EnvelopeVisible=True
      With  ThisWorkbook.Worksheets(“YourSheetName”).MailEnvelope.Item
            .To =”Email ID”
            .Subject =”Subject”
            .Send
      End With
End Sub

So, Those are the few operations  you can do by integrating Excel and Outlook. there are a lot more you can do once you understand Outlook Object Model. Well, thats all from me now.

Join my VBA whatsApp group to know more

Subscribe to get news from IT Chat

See Also
Send Bulk Email

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *