将电子邮件正文中的表解析为Excel [英] Parse Table in Email Body to Excel

问题描述

我希望能得到一些帮助。我目前没有VBA编码的经验，我正在寻找一些如何解决这个问题的指导。我目前有电子邮件进入正文中的数据表格式如下，其中有2行和2
列。第一行将单元格合并为一种标题，第二行有两列，其中多行数据堆叠在一个单元格中。我想将第二列中的数据解析为
文本格式的特定excel文件中的单行（因为该帐户的拉链具有前导零），并且如果可能，还包括元数据（日期和收到的时间，发件人）。 
$


我们每天收到多封电子邮件，理想情况下，每天都会在收件箱中收到的所有电子邮件中运行此作业并解析数据到特定的Excel文件。我已经尝试了多个代码并且已经收到大部分错误，并且我可以获得的
工作的一个VBA代码没有错误，由VBATools提供，将整个表格以相同的格式解析为excel。请帮忙！
$


_____________________

| ____合并单元______ |

|帐户    |数据          |

|名称      |数据          |

|地址    |数据          |

|国家        |数据          |

|邮编及NBSP;         |数据          |

|选项      |数据          |

| _Email ___ | _date _____ |

解决方案

您好

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

这篇关于将电子邮件正文中的表解析为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！