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 let’s see how we can parse or extract emails from Outlook with the click of a button in VBA and show email details such as, from and to address, subject, email receive date etc. in your Excel worksheet.

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….

Add Reference in Excel VBA

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

The VBA Code

I am wrote 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")
    
    Dim objNSpace As Object     ' Create and Set a NameSpace OBJECT.
    ' The GetNameSpace() method will represent a specified Namespace.
    Set objNSpace = objOutlook.GetNamespace("MAPI")
    
    Dim myFolder As Object  ' Create a folder object.
    Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox)
    
    Dim objItem As Object
    Dim iRows, iCols As Integer
    iRows = 2

    ' Loop through each item 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
        iRows = iRows + 1
    Next
    Set objMail = Nothing
   
    ' Release.
    Set objOutlook = Nothing
    Set objNSpace = Nothing
    Set myFolder = Nothing
ErrHandler:
    Debug.Print Err.Description
End Sub

Click the button and it will parse (or extract) the emails from your Outlook application.

Remember: You should have Outlook installed in your computer and you should log in to it.

First, I am creating an instance of Outlook Application object. It provides a method called GetNamespace(), using which I am creating a Namespace object. This 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)

Show other Email Properties

If you want you can show other email properties like the body of the email, CC, BCC etc.

' Loop through each item 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

        ' Show few more properties.
        Cells(iRows, 5) = objMail.Body
        Cells(iRows, 6) = objMail.CC
        Cells(iRows, 7) = objMail.BCC
        Cells(iRows, 8) = objMail.Recipients(1)
    End If
    iRows = iRows + 1
Next

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 add 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, showing all the details in your Excel worksheet.

And, if you want to send email with CC, BCC and Attachements from your Excel file, check out this article about How to send emails from an Excel file using VBA Macro and Outlook. Just few lines of code.

🚀 Similarly, you can import or parse your Outlook emails into a Word document. Check this out.

Conclusion

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

← PreviousNext →