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.



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?