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
