June 30, 2021

Automatically Extract Table Data from Outlook Mail

How to extract table data from Outlook mail automatically using VBA. Watch the video below:

Extract Table Data from Outlook Mail Automatically

Extracting data from tables in an email in Outlook is a very useful activity in an office. We can extract data from a single table or multiple tables from the email body quickly and easily using VBA. Next the data can be analyzed or processed using Excel’s powerful functions and formulas.

Here’s the complete macro code to perform an extraction of tables data from Outlook emails:

Sub ExtractTablesDataFromOutlookEmails()
‘On Error Resume Next
‘Place a reference to Microsoft Outlook xx.x Object Library
‘because Excel will interact with Outlook
‘Place a reference to Microsoft HTML Object Library
‘because each mail item in Outlook has an HTML-Body property

Dim OLApp As Outlook.Application
Set OLApp = New Outlook.Application

‘The Namespace object provides methods for logging in and out,
‘accessing storage objects directly by ID,
‘accessing certain special default folders directly,
‘and accessing data sources owned by other users.
Dim ONS As Outlook.Namespace
‘MAPI: Messaging Application Programming Interface
‘It is a client protocol that lets users access their mailbox by using Outlook
Set ONS = OLApp.GetNamespace(“MAPI”)
Dim MYFOLDER As Outlook.Folder
Set MYFOLDER = ONS.Folders(“[email protected]”).Folders(“Inbox”)
‘Set MYFOLDER = MYFOLDER.Folders(“test”)
Dim OLMAIL As Outlook.MailItem
Set OLMAIL = OLApp.CreateItem(olMailItem)

Set oHTML = New MSHTML.HTMLDocument
Dim oElColl As MSHTML.IHTMLElementCollection
With oHTML
.Body.innerHTML = OLMAIL.HTMLBody
Set oElColl = .getElementsByTagName(“table”)
End With
‘import data into Excel

Dim t As Long, r As Long, c As Long
Dim eRow As Long

‘HTML Collection elements start their numbering with 0

For t = 0 To oElColl.Length - 1
    eRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    For r = 0 To (oElColl(t).Rows.Length - 1)
        For c = 0 To (oElColl(t).Rows(r).Cells.Length - 1)
            Range("A" & eRow).Offset(r, c).Value = oElColl(t).Rows(r).Cells(c).innerText
        Next c
    Next r
    eRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Next t

Cells(eRow, 1) = "Sender's Name:" & " " & OLMAIL.Sender
Cells(eRow, 1).Interior.Color = vbRed
Cells(eRow, 1).Font.Color = vbWhite
Cells(eRow, 2) = "Date & Time of Receipt:" & " " & OLMAIL.ReceivedTime
Cells(eRow, 2).Interior.Color = vbBlue
Cells(eRow, 2).Font.Color = vbWhite
Range(Cells(eRow, 1), Cells(eRow, 2)).Columns.AutoFit



Set OLApp = Nothing
Set OLMAIL = Nothing
Set oHTML = Nothing
Set oElColl = Nothing

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False

End Sub

Automatically Extract Table Data from Outlook Mail with VBA
Automatically Extract Table Data from Outlook Mail with VBA

You can also learn how to get data from Outlook based on specific conditions.

Images of the Outlook messages with 1, 2 and 3 tables respectively.

Outlook Message with one table
Outlook Message with one table
Outlook Message with two tables
Outlook Message with two tables
Outlook Message with three tables
Outlook Message with three tables

One thought on “Automatically Extract Table Data from Outlook Mail

  1. I’ve written a whole VBA macro script which works in extracting all the responses provided for questions within a Questionnaire in word to excel. However, the only problem I’ve run into is when questions have tick box options. My current VB macro script just pulls in the box symbol, but not actual tick within the box or the words that are next to the tick box. Is there a VB macro script you can recommend I can write which allows me to pick up the text associated with the ticked box instead?

Comments are closed.