How to read a table inserted into an Outlook message body using vba?

Question

I need to read a data table, as in picure, using vba. I used Msg.Body to read the main text, but in fact I need to find the first row as a header and leave it as a data field and update the DBMS table accordingly. So you can read the table in the way that I could excel?

Answer 1

This procedure should help. I recreated your spreadsheet in Excel, pasted it into an Outlook email, and sent it to myself. Then I used this procedure to read cell values.

Sub GetLines() Dim msg As Outlook.mailItem Dim rows As Variant Dim numberofColumns As Long Dim numberofRows As Long Dim headerValues As Variant Dim headerRow() As String Dim data() As String Dim i As Long, j As Long ' get currently selected email Set msg = ActiveExplorer.Selection.item(1) ' tokenize each line of the email rows = Split(msg.Body, vbCrLf) ' calculate array size numberofColumns = Len(rows(0)) - Len(Replace(rows(0), Chr(9), "")) numberofRows = UBound(rows) + 1 ' put header row into array ReDim headerRow(1 To numberofColumns) headerValues = Split(rows(0), Chr(9)) For i = 1 To numberofColumns headerRow(i) = Trim$(headerValues(i - 1)) Next i ' calculate data array size numberofRows = numberofRows - 1 ' put data into array ReDim data(1 To numberofRows, 1 To numberofColumns) For i = 1 To numberofRows For j = 1 To numberofColumns data(i, j) = Trim$(Split(rows(i), Chr(9))(j - 1)) Next j Next i End Sub 

First, we mark each line of the letter in an array. We calculate the size of the array, then create an array to store only the first row of the table ("header").

Then we subtract one from the number of lines, because we are going to skip the title bar. Then we loop through each line, split it, and check its values, assigning them to our 2D array as we move.

In the end, the variable "headerRow" can be repeated to get the values ​​of the fields that you want to use for your DBMS. The variable "data" contains only the values ​​corresponding to each field. Therefore, headerRow (1) and data (n, 1) must match the values ​​in the first column of your table.

Answer 2

I used it exactly in Outlook 2016, but I cannot get it to work. This gives me an error in this row ReDim headerRow (1 to the number of columns)

Out of Range Index

I am newbie