Extract Emails from Outlook to Excel with code example

Extract Emails from Outlook to Excel with code example

Extract Emails

In this PoC, I will write code to extract emails form outlook to excel which can easy your day to day task. VBA gives power to automate Outlook from Excel. Let’s do some code to understand how it can be done.

Step 1: Add Microsoft Outlook Object reference as following:

Step 2: Put following code in a standard module

Sub extractEmails()
    'Outlook application object declaration
    Dim oApp As Outlook.Application
    
    'bind current outlook instance
    Set oApp = GetObject("", "Outlook.Application")
    
    'Namespace object
    Dim oNameSpace As Namespace
    
    'Bind MAPI Namespace
    Set oNameSpace = oApp.GetNamespace("MAPI")
    
    'Declare folder object
    Dim oFolderInbox As Folder
    
    'Bind inbox folder
    Set oFolderInbox = oNameSpace.GetDefaultFolder(olFolderInbox)
    
    'Declare Mail object
    Dim oMailItem As MailItem
    
    'Declare row number to keep track over data writing in each row
    Dim rowNumber As Integer
    rowNumber = 2
    
    'Loop to iterate each email from inbox
    For Each oMailItem In oFolderInbox.Items
        'Get date time
        ActiveSheet.Range("A" & rowNumber) = oMailItem.ReceivedTime
        'Get subject
        ActiveSheet.Range("B" & rowNumber) = oMailItem.Subject
        'Get sender name
        ActiveSheet.Range("C" & rowNumber) = oMailItem.SenderName
        'Get mail address of sender
        ActiveSheet.Range("D" & rowNumber) = oMailItem.SenderEmailAddress
        rowNumber = rowNumber + 1
    Next oMailItem
    
    'CleanUp
    If Not oMailItem Is Nothing Then
        Set oMailItem = Nothing
    End If
    If Not oFolderInbox Is Nothing Then
        Set oFolderInbox = Nothing
    End If
    If Not oNameSpace Is Nothing Then
        Set oNameSpace = Nothing
    End If
    If Not oApp Is Nothing Then
        Set oApp = Nothing
    End If
End Sub

Output

Next >> Web browser control in Excel with code example

Leave a Reply

Your email address will not be published.