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

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

Range(“A1:K5000”).Clear
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)

For Each OLMAIL In MYFOLDER.Items
Dim oHTML As MSHTML.HTMLDocument
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

Next OLMAIL

Range(“A1”).Select

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

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False

End Sub

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.