Parse HTML Formatted Email (not URL) into VBA

Question

Context

I am receiving emails formatted to HTML. The email has a table in it. I would like to parse the table so that I can access the individual cells in the table.

As per the numerous questions and answers on SO, I have found out that the way to access this is to do something like this:

objIE.document.getElementsByTagName("td")

...which will create an array (group) of the columns in the HTML file. So that's sorted.

Question:

Instead of objIE.document. which in the numerous SO examples points to some URL, I want to point it to the email in this specific case. In this specific case my email is called "Msg" which I have made by doing:

Dim Msg As Outlook.MailItem

In my code, I have therefore done the following:

 Msg.Body.getElementsByTagName ("td")

However I get an error saying "invalid qualifier" error on that line when trying to run it.

Any ideas on how to access the tables cells/elements?

Answer 1

Here's an approach that should get you pretty close. What you want to do is store the HTML of the email into a HTML file, that way you can use element selectors to find what you are interested in. This code requires a reference to the Outlook Object.

Here is a brief example.

Option Explicit

Public Sub SOTest()
    Dim outlook     As outlook.Application
    Dim ns          As outlook.NameSpace: Set ns = GetNamespace("MAPI")
    Dim folder      As outlook.MAPIFolder: Set folder = ns.PickFolder
    Dim item        As outlook.MailItem
    Dim html        As Object: Set html = CreateObject("htmlfile")
    Dim elements    As Object
    Dim element     As Object

    For Each item In folder.Items
        If item.Class = olMail Then ' Make sure it's a Mail Item...change if not needed
            html.Body.Innerhtml = item.HTMLBody ' set the body of the email equal to the html from outlook email
            Set elements = html.getElementsByTagName("td")
            For Each element In elements
                Debug.Print element.InnerText
            Next
        End If
    Next

End Sub