none
Parse Table in Email Body to Excel RRS feed

  • Question

  • 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_____|
    Thursday, April 5, 2018 4:27 PM

All replies

  • 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


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 6, 2018 2:38 AM
    Moderator
  • Hi Mellownando,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 19, 2018 9:15 AM
    Moderator