How to Parse Outlook Emails and Show in Excel Worksheet using VBA

I am sure you must have read my previous article on how to send emails from Excel using VBA macro and Outlook. Now, here in this post I am sharing an example on how to Parse or Extract Outlook emails and show details like senders address, to, email receive date, email subject etc. in Excel worksheet using VBA.

Parse or Extract Outlook Emails and Show Excel using VBA

Before writing your macro, you must first add a reference of Microsoft Outlook 12.0 Object Library (or a higher version) to your application. Follow these steps.

1) Open Excel and press Alt+F11 keys, to open the Editor.

2) In your VBA editor, find the Tools option from the top menu and choose References….

3) In the References dialog box, find Microsoft Outlook 12.0 Object Library, check the option and click OK.

The VBA Code

I am writing my Macro inside a Button Click event.

Option Explicit

Private Sub CommandButton1_Click()
    On Error GoTo ErrHandler
    
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' CREATE AND SET A NameSpace OBJECT.
    Dim objNSpace As Object
    ' THE GetNameSpace() METHOD WILL REPRESENT A SPECIFIED NAMESPACE.
    Set objNSpace = objOutlook.GetNamespace("MAPI")
    
    ' CREATE A FOLDER OBJECT.
    Dim myFolder As Object
    Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox)
    
    Dim Item As Object
    Dim iRows, iCols As Integer
    iRows = 2

    ' LOOP THROUGH EACH ITEMS IN THE FOLDER.
    For Each objItem In myFolder.Items
        If objItem.Class = olMail Then
        
            Dim objMail As Outlook.MailItem
            Set objMail = objItem

            Cells(iRows, 1) = objMail.SenderEmailAddress
            Cells(iRows, 2) = objMail.To
            Cells(iRows, 3) = objMail.Subject
            Cells(iRows, 4) = objMail.ReceivedTime
        End If
        
        ' SHOW OTHER PROPERTIES, IF YOU WISH.
        'Cells(iRows, 6) = objMail.Body
        'Cells(iRows, 5) = objMail.CC
        'Cells(iRows, 6) = objMail.BCC
        'Cells(iRows, 4) = objMail.Recipients(1)
        
        iRows = iRows + 1
    Next
    Set objMail = Nothing
   
    ' RELEASE.
    Set objOutlook = Nothing
    Set objNSpace = Nothing
    Set myFolder = Nothing
ErrHandler:
    Debug.Print Err.Description
End Sub

First, I am creating an instance of Outlook Application object. It provides a method called GetNamespace() method, with which I’ll create a Namespace object. It is like creating a Session. The method takes a parameter of type MAPI. This represents the namespace.

MAPI stands for Messaging Application Programming Interface. MAPI provides the messaging architecture for Outlook. In simple words, it provides the necessary classes, functions and properties, with which you can access Outlook application objects, like folders, you can manipulate emails, send emails, read Inbox contents etc.

Here, we are accessing the Outlook Inbox folder (see olFolderInbox in the example), extracts items in the Inbox, using the Namespace GetdefaultFoder() method.

Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox)

Note: If you want to see the contents of a particular Inbox, you can add the below code to the above Macro.

Dim myFolder As Object
Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox)

Dim myItem As Object
Set myItem = myFolder.Items(1)
myItem.Display

The Display method will open the first email in your Inbox folder and show it in Outlook.

You can other folder types such as olFolderCalander, olFolderContacts etc and view the contents in the folders.

Finally, I’ll loop through the Inbox Folder and parse the emails properties using Outlook.MailItem object. Show the values in your Excel worksheet.

Conclusion

This automation method is useful, when you want to automatically extract or parse email addresses from your Outlook application. Its useful if you are running an email campaign and you do not have to open each email to check the contents. Simply parse the email details, write it in your Excel worksheet and you will have detail in front of you.

Thanks for reading.

Previous - How to Open a Word Document from Excel and Copy Excel Data to the Word Document using VBA



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!