excel - 如何从 Outlook 电子邮件中提取文本表到 Excel

Question

我目前每天收到 100 次订单失败电子邮件进入我的 Outlook 收件箱。该报告显示失败的订单、经销商代码和名称等...我正在尝试将这些数据导出到 Excel，以便我可以运行报告以查看不同经销商的 KPI。

我试图弄清楚如何编写一个 VBA 脚本来实现这一点。

这是我在电子邮件中收到的内容

如您所见，它们由空格分隔

理想情况下，我希望将以下信息导入 Excel

关于实现这一目标的最佳工具或方法的任何想法？

我试图用谷歌搜索并使用各种像这样的 vba 脚本并对其进行调整，但是嵌入的表格让我很难过。

Sub ExportToExcel()
Dim xExcel As Excel.Application
Dim xWb As Workbook
Dim xWs As Worksheet
Dim xInspector As Inspector
Dim xItem As Object
Dim xMailItem As MailItem
Dim xDoc As Document
Dim xShell As Object
Dim xFilePath As String
On Error Resume Next
    Set xShell = CreateObject("Shell.Application")
    Set xFolder = xShell.BrowseForFolder(0, "Select a Folder:", 0, 0)
    If TypeName(xFolder) = "Nothing" Then Exit Sub
    Set xFolderItem = xFolder.Self
    xFilePath = xFolderItem.Path & "\"
    Set xItem = Outlook.Application.ActiveExplorer.Selection.item(1)
    If xItem.Class <> olMail Then Exit Sub
    Set xMailItem = xItem
    Set xInspector = xMailItem.GetInspector
    Set xDoc = xInspector.WordEditor
    xDoc.Application.Selection.Range.Copy
    xInspector.Close olDiscard
    Set xExcel = New Excel.Application
    Set xWb = xExcel.Workbooks.Add
    Set xWs = xWb.Sheets.item(1)
    xExcel.Visible = False
    xWs.Activate
    xWs.Paste
    xWs.SaveAs xFilePath & "Email body.xlsx"
    xWb.Close True
    xExcel.Quit
    Set xWs = Nothing
    Set xWb = Nothing
    Set xExcel = Nothing
End Sub

标签： excelvbaoutlookpowerbiexport-to-excel