Here we will explains how to retrieve mail item details from these Outlook mail folders and update the retrieved data to Excel sheet.

  • Inbox
  • Sent Items
  • Junk Folder
  • Deleted Items
  • Outbox
  • Online Archive Inbox
  • Online Archive Sent Items
  • Online Archive Draft

How to get mail data from Outlook’s Inbox folder and update it to excel using VBA macro ?

VBA Project References required for this project.

  • Visual Basic For Applications
  • Microsoft Excel 16.0 Object Library
  • Microsoft Forms 2.0 Object Library
  • Microsoft Outlook 16.0 Object Library
VBA references for outlook Sample Code VBA Excel Macro Get Mail Items from archive inbox deleted junk

Sample Code: This VBA macro code will retrieve mail items from Inbox and update it to Active Excel Sheet.

Sub GetOutlookFolder()
Dim objOutlook, objItem, MailFolder As Object, oOutlook, objHTTP As Object, MailQuery, MailBody As String, row, i As Integer
On Error Resume Next
row = 3
i = 1

' Clear Contents of sheet 1 ; Range A1:G37
Worksheets("Sheet1").Range("A1:G37").ClearContents

'Creating Outlook Object
Set objOutlook = CreateObject("Outlook.Application")

'Get MAPI Name space Messaging Application Programming Interface (MAPI)
Set oOutlook = objOutlook.GetNamespace("MAPI")

'  ######## Get Inbox Folder ##########
Set MailFolder = oOutlook.GetDefaultFolder(olFolderInbox)

' Query to retrive items from selected folder
MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2022-02-11 00:00:01'"
' Looping through each items in folder
For Each objItem In MailFolder.Items.Restrict(MailQuery) 
            ' Update Excel status bar
            Application.StatusBar = "Scanning Mails " & i 
            Cells(row, 1) = objItem.ReceivedTime
            Cells(row, 2) = objItem.Sender
            Cells(row, 3) = objItem.subject
            ' Remove new lines and convering mail body text to a singe line.
            MailBody = Mid(objItem.body, 1, 11500)
            MailBody = Replace(MailBody, vbLf, "")
            MailBody = Replace(MailBody, vbCr, "")
            MailBody = Replace(MailBody, vbTab, "")
            MailBody = Replace(MailBody, Chr$(160), "")
            Cells(row, 4) = MailBody
    DoEvents
    row = row + 1
    i = i + 1
Next

End Sub

Code Explanation

Create Outlook object “Outlook.Application” and set the object to “objOutlook”

Set objOutlook = CreateObject("Outlook.Application")

Get MAPI Name space Messaging Application Programming Interface (MAPI) from “objOutlook” and assign it it “oOutlook” object

Set oOutlook = objOutlook.GetNamespace("MAPI")

Set/Connect to Inbox Folder 

Set MailFolder = oOutlook.GetDefaultFolder(olFolderInbox)

DASL query to retrieve mail items with received date greater than or equal to a given date.

MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2022-02-11 00:00:01'"

Filter mail items in the inbox folder based on the given query above and looping through each items

For Each objItem In MailFolder.Items.Restrict(MailQuery)

Update Excel status bar with retrieved item count

Application.StatusBar = "Scanning Mails " & i '

Updating retireved data to Excel cells.

Cells(row, 1) = objItem.ReceivedTime
Cells(row, 2) = objItem.Sender
Cells(row, 3) = objItem.subject

Converting Mail body text as a single line string by removing newlines, tab spaces etc.

MailBody = Mid(objItem.body, 1, 11500)
MailBody = Replace(MailBody, vbLf, "")
MailBody = Replace(MailBody, vbCr, "")
MailBody = Replace(MailBody, vbTab, "")
MailBody = Replace(MailBody, Chr$(160), "")
Cells(row, 4) = MailBody ' Updating to Excel Cell

Calling DoEvents – To allow user to control execution (break/canceL) for long running process.

DoEvents

How to get mail data from Outlook’s Sent Items folder and update it to excel using VBA macro ?

Example Code to retrieve Sent items folder mail records.

Sub GetOutlookFolder()
Dim objOutlook, objItem, MailFolder As Object, oOutlook, objHTTP As Object, MailQuery, MailBody As String, row, i As Integer
On Error Resume Next
row = 3
i = 1

' Clear Contents of sheet 1 ; Range A1:G37
Worksheets("Sheet1").Range("A1:G37").ClearContents

'Creating Outlook Object
Set objOutlook = CreateObject("Outlook.Application")

'Get MAPI Name space Messaging Application Programming Interface (MAPI)
Set oOutlook = objOutlook.GetNamespace("MAPI")

'  ######## Get Sent Items Folder ##########
Set MailFolder = oOutlook.GetDefaultFolder(olFolderSentMail)

' Query to retrive items from selected folder
MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2022-02-11 00:00:01'"
' Looping through each items in folder
For Each objItem In MailFolder.Items.Restrict(MailQuery) 
            ' Update Excel status bar
            Application.StatusBar = "Scanning Mails " & i 
            Cells(row, 1) = objItem.ReceivedTime
            Cells(row, 2) = objItem.Sender
            Cells(row, 3) = objItem.subject
            ' Remove new lines and convering mail body text to a singe line.
            MailBody = Mid(objItem.body, 1, 11500)
            MailBody = Replace(MailBody, vbLf, "")
            MailBody = Replace(MailBody, vbCr, "")
            MailBody = Replace(MailBody, vbTab, "")
            MailBody = Replace(MailBody, Chr$(160), "")
            Cells(row, 4) = MailBody
    DoEvents
    row = row + 1
    i = i + 1
Next

End Sub

How to select Outlook Sent Items folder ?

Use oDefaultFolder enumeration “olFolderSentMail” to get Sent items folder data.

Set MailFolder = oOutlook.GetDefaultFolder(olFolderSentMail)

How to get mail data from Outlook’s Drafts folder and update it to excel using VBA macro ?

Use oDefaultFolder enumeration “olFolderDrafts” to get Drafts folder data.

Set MailFolder = oOutlook.GetDefaultFolder(olFolderDrafts) 
Sub GetOutlookFolder()
Dim objOutlook, objItem, MailFolder As Object, oOutlook, objHTTP As Object, MailQuery, MailBody As String, row, i As Integer
On Error Resume Next
row = 3
i = 1

' Clear Contents of sheet 1 ; Range A1:G37
Worksheets("Sheet1").Range("A1:G37").ClearContents

'Creating Outlook Object
Set objOutlook = CreateObject("Outlook.Application")

'Get MAPI Name space Messaging Application Programming Interface (MAPI)
Set oOutlook = objOutlook.GetNamespace("MAPI")

'  ######## Get Drafts Folder ##########
Set MailFolder = oOutlook.GetDefaultFolder(olFolderDrafts)

' Query to retrive items from selected folder
MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2022-02-11 00:00:01'"
' Looping through each items in folder
For Each objItem In MailFolder.Items.Restrict(MailQuery) 
            ' Update Excel status bar
            Application.StatusBar = "Scanning Mails " & i 
            Cells(row, 1) = objItem.ReceivedTime
            Cells(row, 2) = objItem.Sender
            Cells(row, 3) = objItem.subject
            ' Remove new lines and convering mail body text to a singe line.
            MailBody = Mid(objItem.body, 1, 11500)
            MailBody = Replace(MailBody, vbLf, "")
            MailBody = Replace(MailBody, vbCr, "")
            MailBody = Replace(MailBody, vbTab, "")
            MailBody = Replace(MailBody, Chr$(160), "")
            Cells(row, 4) = MailBody
    DoEvents
    row = row + 1
    i = i + 1
Next

End Sub

How to get mail data from Outlook’s Deleted folder and update it to excel using VBA macro ?

Example Code to retreive Sent items folder mail records.

Sub GetOutlookFolder()
Dim objOutlook, objItem, MailFolder As Object, oOutlook, objHTTP As Object, MailQuery, MailBody As String, row, i As Integer
On Error Resume Next
row = 3
i = 1

' Clear Contents of sheet 1 ; Range A1:G37
Worksheets("Sheet1").Range("A1:G37").ClearContents

'Creating Outlook Object
Set objOutlook = CreateObject("Outlook.Application")

'Get MAPI Name space Messaging Application Programming Interface (MAPI)
Set oOutlook = objOutlook.GetNamespace("MAPI")

'  ######## Get Deleted Items Folder ##########
Set MailFolder = oOutlook.GetDefaultFolder(olFolderDeletedItems)

' Query to retrive items from selected folder
MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2022-02-11 00:00:01'"
' Looping through each items in folder
For Each objItem In MailFolder.Items.Restrict(MailQuery) 
            ' Update Excel status bar
            Application.StatusBar = "Scanning Mails " & i 
            Cells(row, 1) = objItem.ReceivedTime
            Cells(row, 2) = objItem.Sender
            Cells(row, 3) = objItem.subject
            ' Remove new lines and convering mail body text to a singe line.
            MailBody = Mid(objItem.body, 1, 11500)
            MailBody = Replace(MailBody, vbLf, "")
            MailBody = Replace(MailBody, vbCr, "")
            MailBody = Replace(MailBody, vbTab, "")
            MailBody = Replace(MailBody, Chr$(160), "")
            Cells(row, 4) = MailBody
    DoEvents
    row = row + 1
    i = i + 1
Next

End Sub

How to select Outlook Deleted Items folder?

Set MailFolder = oOutlook.GetDefaultFolder(olFolderDeletedItems)

How to get mail data from Online Archive Inbox folder and update it to excel using VBA macro ?

The code we used here is similar to the examples given above. First we get the “Parent.Name” of the default Inbox folder and then we get the archive folder using “.Folders(‘Online Archive -‘ + UserId)”.

Set ArchiveFolder = oOutlook.Folders("Online Archive - " + UserId).Folders("Inbox")

One important thing to note is the received date in the SQL query string has to be changed according to the mails in Archive Inbox.

In this example given below i changed the date in the string to last year.

MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2021-02-11 00:00:01'"

Example Code to retrieve “Online Archive Inbox” folder mail records.

Option Explicit
Sub GetOnlineArchiveInbox()
Dim objOutlook, objItem, MailFolder, ArchiveFolder As Object, oOutlook, objHTTP As Object, MailQuery, UserId, MailBody As String, row, i As Integer

On Error Resume Next
row = 3
i = 1

' Clear Contents of sheet 1 ; Range A1:G37
Worksheets("Sheet1").Range("A1:G37").ClearContents

'Creating Outlook Object
Set objOutlook = CreateObject("Outlook.Application")

'Get MAPI Name space Messaging Application Programming Interface (MAPI)
Set oOutlook = objOutlook.GetNamespace("MAPI")

'  ######## Get Inbox Folder ##########
Set MailFolder = oOutlook.GetDefaultFolder(olFolderInbox)

' Retreving Mail box id/user id
UserId = MailFolder.Parent.Name

' ####### Using retrived user id to get Online Archive Inbox folder ######
Set ArchiveFolder = oOutlook.Folders("Online Archive - " + UserId).Folders("Inbox")


' Query to retrive items from selected folder '' Please note the date selection
MailQuery = "@SQL=" + "urn:schemas:httpmail:datereceived" + ">=" + "'2021-02-11 00:00:01'"

' Looping through each items in folder
For Each objItem In ArchiveFolder.Items.Restrict(MailQuery)
            ' Update Excel status bar
            Application.StatusBar = "Scanning Mails " & i
            Cells(row, 1) = objItem.ReceivedTime
            Cells(row, 2) = objItem.Sender
            Cells(row, 3) = objItem.subject
            ' Remove new lines and convering mail body text to a singe line.
            MailBody = Mid(objItem.body, 1, 11500)
            MailBody = Replace(MailBody, vbLf, "")
            MailBody = Replace(MailBody, vbCr, "")
            MailBody = Replace(MailBody, vbTab, "")
            MailBody = Replace(MailBody, Chr$(160), "")
            Cells(row, 4) = MailBody
    DoEvents
    row = row + 1
    i = i + 1
Next

End Sub



Set MailFolder = oOutlook.GetDefaultFolder(olFolderDeletedItems)

VBA : Outlooks DefaultFolder enumeration values

Get ValueGet FolderDefaultFolder Enumeration
Get the Calendar folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderCalendar)olFolderCalendar
Get the Conflicts folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderConflicts)olFolderConflicts
Get the Contacts folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderContacts)olFolderContacts
Get the DeletedItems folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderDeletedItems)olFolderDeletedItems
Get the Drafts folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderDrafts)olFolderDrafts
Get the Inbox folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderInbox)olFolderInbox
Get the Journal folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderJournal)olFolderJournal
Get the Junk folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderJunk)olFolderJunk
Get the LocalFailures folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderLocalFailures)olFolderLocalFailures
Get the Notes folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderNotes)olFolderNotes
Get the Outbox folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderOutbox)olFolderOutbox
Get the SentMail folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderSentMail)olFolderSentMail
Get the ServerFailures folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderServerFailures)olFolderServerFailures
Get the SuggestedContacts folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderSuggestedContacts)olFolderSuggestedContacts
Get the SyncIssues folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderSyncIssues)olFolderSyncIssues
Get the Tasks folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderTasks)olFolderTasks
Get the ToDo folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderToDo)olFolderToDo
Get the RssFeeds folderSet MailFolder = oOutlook.GetDefaultFolder(olFolderRssFeeds)olFolderRssFeeds

RELATED ARTICLESMORE FROM AUTHOR