您好

I am hoping for some help on this. I currently have no experience in VBA coding and i'm looking for some guidance on how to solution this. I currently have emails coming in with a data table in the body formatted like below where there are 2 rows and 2 columns. The first row has the cells merged as a sort of header, and the second row has 2 columns where multiple lines of data are stacked in a single cell. I would like to parse the data in the second column into a single row into a specific excel file in text format (as the account had zips have leading zeros) and, if possible, to also include meta data (date and time received, sender). 

We receive multiple emails a day, and ideally would run this job once daily on all emails received in the inbox and parse the data to a specific Excel file. I've tried multiple codes and have received mostly errors, and the one VBA code that i could get to work without errors, provided by VBATools, parsed the whole table in the same format into excel. Please help!

_____________________
|____merged cell______|
| Account   | data          |
| Name      | data          |
| Address   | data          |
| State       | data          |
| Zip          | data          |
| Option     | data          |
|_Email___|_date_____|

Hi Mellownando,

I got one example from one old thread may help you to solve your issue.

I modify the code to insert data in a row in Excel.

Sub demo()


Const strMail As String = "Your Email Address"
Dim oApp As Outlook.Application
Dim oMapi As Outlook.MAPIFolder
Dim oMail As Outlook.MailItem

On Error Resume Next
Set oApp = GetObject(, "OUTLOOK.APPLICATION")
    If (oApp Is Nothing) Then Set oApp = CreateObject("OUTLOOK.APPLICATION")
On Error GoTo 0

Set oMapi = oApp.GetNamespace("MAPI").Folders(strMail).Folders("inbox")
Set oMail = oMapi.Items(oMapi.Items.Count)


Dim oHTML As MSHTML.HTMLDocument: Set oHTML = New MSHTML.HTMLDocument
Dim oElColl As MSHTML.IHTMLElementCollection
With oHTML
    .Body.innerHTML = oMail.HTMLBody
    Set oElColl = .getElementsByTagName("table")
End With


Dim x As Long, y As Long

For x = 0 To oElColl(0).Rows.Length - 1
    For y = 0 To oElColl(0).Rows(x).Cells.Length - 1
        If y = 1 Then
            Range("A1").Offset(y, x).Value = oElColl(0).Rows(x).Cells(y).innerText
            Debug.Print oElColl(0).Rows(x).Cells(y).innerText
        End If
    Next y
Next x

Set oApp = Nothing
Set oMapi = Nothing
Set oMail = Nothing
Set oHTML = Nothing
Set oElColl = Nothing
End Sub

Mail in Outlook:

Output in Excel:

Note: This is just a sample code which can work for only first email in your inbox. To process all the mail, you need to modify the code to loop through all the mails in folder.

Reference:

Extract Table from Outlook Message to Excel using VBA

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Regards

Deepak