Read a table in outlook mail using macro

Question

I'm writing a macro to read the below Email:

Start Date: July-07-2016

Name    Accept  Approved
John    Yes     No
Peter   No      No

I'm good with search the word "Start date" and get the next 13 character to copy and paste that in a text file. But my problem is the next part is in a Table format. So when I'm searching for the name "John" and trying to copy the next 10 Characters. It doesn't work.

Is there a way to search for the word "Accept" and get the First Row data(Which will be No) and then Second Row data(Which will be No)? Is that possible?

This EMail's table will have only 2 Rows. So, I don't need any dynamic way to get the data. Can someone guide me?

I've tried searching the internet first, but the solutions are too huge for me to understand. Is there any simple way? I have even tried the solution give here: How to read table pasted in outlook message body using vba? but that method works when the body has ONLY TABLE. But my EMail will have text as well as table.

Answer 1

You can actually use the Word Object Model to parse out the text from the table - assuming that the email is in HTML format.

Get a Word.Document object from the Inspector.WordEditor property and use Word objects and methods to get the text, like the following below example from MSDN. Just replace ActiveDocument with the variable you declare and set from WordEditor.

Sub ReturnCellContentsToArray() 
 Dim intCells As Integer 
 Dim celTable As Cell 
 Dim strCells() As String 
 Dim intCount As Integer 
 Dim rngText As Range 

 If ActiveDocument.Tables.Count >= 1 Then 
 With ActiveDocument.Tables(1).Range 
 intCells = .Cells.Count 
 ReDim strCells(intCells) 
 intCount = 1 
 For Each celTable In .Cells 
 Set rngText = celTable.Range 
 rngText.MoveEnd Unit:=wdCharacter, Count:=-1 
 strCells(intCount) = rngText 
 intCount = intCount + 1 
 Next celTable 
 End With 
 End If 
End Sub

Answer 2

I've never actually programmed in vba, but I think I can help (a bit) nevertheless.

In the answer on the post you linked to, there is the line

Set msg = ActiveExplorer.Selection.item(1)

I think you can change this to something like

Set msg = Right(ActiveExplorer.Selection.item(1), 25)

to get rid of the text before the table (I got the Right part from here: http://www.exceltrick.com/formulas_macros/vba-substring-function/, but it should also work in Outlook).

This way, you run the code on the table itself instead of on the whole message.
If there is also text after the table, it might be more difficult, but you might get that done by searching for the table ending.

I hope this helps!

---

Attempt 2

After some searching and thinking, I came up with the idea to get the html of the message and use that to parse the table (Ok, not really, I got it from the comments here: http://www.codeproject.com/Questions/567073/Howplustoplusrecognizeplusandplusreadplustableplus). Based on that and other sources, it is possible to write a code that gets the table from an email.

I've written some code that might work, but I couldn't test it as I do not have Outlook. Also, this is my first time writing vba, so there may be a lot of syntax errors (and the code is ugly).

Sub GetTable()
    Dim msg As Outlook.mailItem
    Dim html As String
    Dim tableBegin As String
    Dim tableEnd As String
    Dim posTableBegin As Long
    Dim posTableEnd As Long
    Dim table As String
    Dim rowBegin As String
    Dim rowEnd As String
    Dim rowCount As Long
    Dim columnBegin As String
    Dim columnBeginLen As Long
    Dim columnEnd As String
    Dim posRowBegin As Long
    Dim posRowEnd As Long
    Dim values As String(0, 3)
    Dim beginValue0 As Long
    Dim beginValue1 As Long
    Dim beginValue2 As Long
    Dim EndValue0 As Long
    Dim EndValue1 As Long
    Dim EndValue2 As Long

    ' Get the message and the html
    Set msg = ActiveExplorer.Selection.item(1)
    html = msg.HTMLbody

    ' Get the begin and end positions of the table (within the html)
    tableBegin = "<table>"
    tableEnd = "</table>"

    posTableBegin = InStr(1, html, tableBegin)
    posTableEnd = InStr(posTableBegin, html, tableEnd)

    ' Get the html table
    table = Mid(html, posTableBegin + Len(tableBegin), posTableEnd - posTableBegin - Len(tableBegin))

    ' Set the variables for the loop
    rowBegin = "<tr>"
    rowEnd = "</tr>"
    rowCount = 0

    columnBegin = "<td>"
    columnBeginLen = Len(columnBegin)
    columnEnd = "</td>"

    ' Loop trough all rows
    posRowBegin = InStr(lastPos, table, rowBegin)
    Do While posRowBegin != 0
        ' Get the end from the current row
        posRowEnd = InStr(posRowBegin, table, rowEnd)
        rowCount = rowCount + 1

        ' Make the array larger
        ReDim Preserve values(rowCount + 1, 3)

        ' Get the contents from that row
        row = Mid(table, posRowBegin + Len(rowBegin), posRowEnd - posRowBegin - Len(rowBegin))

        ' Get the three values from that row (name, Accept, Approved) and put it in the array
        beginValue0 = InStr(1, row, columnBegin) + columnBeginLen
        endValue0 = InStr(beginValue0, row, columnEnd)
        beginValue1 = InStr(endValue0, row, columnBegin) + columnBeginLen
        endValue1 = InStr(beginValue1, row, columnEnd)
        beginValue2 = InStr(endValue1, row, columnBegin) + columnBeginLen
        endValue2 = InStr(beginValue2, row, columnEnd)

        values(rowCount, 0) = Mid(row, beginValue0, endValue0)
        values(rowCount, 1) = Mid(row, beginValue1, endValue1)
        values(rowCount, 2) = Mid(row, beginValue2, endValue2)

        ' Get the beginning of the next row
        posRowBegin = InStr(lastPos, table, rowBegin)
    Loop

    ' The values are now in the (double) array 'values'.
    ' values(0, [1-3]) contains the headers.

End Sub

As said before, the original idea came from http://www.codeproject.com/Questions/567073/Howplustoplusrecognizeplusandplusreadplustableplus. Additionally, I used Word VBA how to select text between two substrings and assign to variable? and the Microsoft documentation to write this.

While it is likely that the code does not work out of the box, I think it still gets the general idea (and some specifics) across, so that it can be used as a guide. I hope this is the solution you need!