How to read table pasted in outlook message body using vba?

The Problem (Q-score 4, ranked #94th of 95 in the VBA Core archive)

The scenario as originally posted in 2011

I need to read table of data as in picure using vba. I used Msg.Body to read the body text but actually i need to find first row as header and rest as data field and update DBMS table accordingly.So is it possible to read the table as I would in excel?

Why community consensus is tight on this one

Across 95 VBA Core entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — niche answer (below median) (+6)

43-line VBA Core pattern (copy-ready)

This sample procedure should help. I recreated your table in Excel, pasted it into an Outlook email and sent it to myself. Then I used this procedure to read the “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 tokenize each line of the email into an array. We calculate the array size, then create an array to hold just the first line of the table (the “header”).

Then we subtract one from the row count because we are going to skip the header row. We then loop through each row, split it and loop through its values, assigning them to our 2D array as we go.

In the end, the variable “headerRow” can be iterated to retrieve the field values you want to use for your DBMS. The variable “data” contains only the values corresponding to each field. So headerRow(1) and data(n,1) should correspond to the values in the first column of your table.

Loop-performance notes specific to this pattern

The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.

When to Use It — vintage (14+ years old, pre-2013)

Ranked #94th in its category — specialized fit

This pattern sits in the 95% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2011 and 2026

The answer is 15 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.