在excel VBA代码中提取电子邮件正文中的表

Sub ExtractTableFromEmail()
    Dim outlookApp As Outlook.Application
    Dim outlookNamespace As Outlook.Namespace
    Dim inboxFolder As Outlook.MAPIFolder
    Dim mailItem As Outlook.MailItem
    
    ' 创建Outlook应用程序对象
    Set outlookApp = New Outlook.Application
    
    ' 获取当前用户的收件箱文件夹
    Set outlookNamespace = outlookApp.GetNamespace("MAPI")
    Set inboxFolder = outlookNamespace.GetDefaultFolder(olFolderInbox)
    
    ' 遍历收件箱中的电子邮件
    For Each mailItem In inboxFolder.Items
        ' 判断邮件主题或发件人，以确定要处理的邮件
        
        ' 示例：提取主题包含"表格"的邮件
        If InStr(1, mailItem.Subject, "表格", vbTextCompare) > 0 Then
            ' 提取邮件正文中的表格
            ExtractTableFromEmailBody mailItem
        End If
        
        ' 示例：提取发件人为特定邮箱地址的邮件
        'If mailItem.SenderEmailAddress = "example@example.com" Then
        '    ExtractTableFromEmailBody mailItem
        'End If
    Next mailItem
    
    ' 释放对象
    Set inboxFolder = Nothing
    Set outlookNamespace = Nothing
    Set outlookApp = Nothing
End Sub

Sub ExtractTableFromEmailBody(mailItem As Outlook.MailItem)
    Dim excelApp As Excel.Application
    Dim excelWorkbook As Excel.Workbook
    Dim excelWorksheet As Excel.Worksheet
    Dim tableRange As Excel.Range
    Dim tableData As Variant
    
    ' 创建Excel应用程序对象
    Set excelApp = New Excel.Application
    
    ' 打开一个新的工作簿
    Set excelWorkbook = excelApp.Workbooks.Add
    
    ' 获取工作簿的第一个工作表
    Set excelWorksheet = excelWorkbook.Worksheets(1)
    
    ' 获取邮件正文中的HTML内容
    Dim htmlBody As String
    htmlBody = mailItem.HTMLBody
    
    ' 使用HTML解析库解析HTML内容，并提取表格数据
    ' 这里需要使用合适的HTML解析库，比如mshtml库、HTML Agility Pack等
    
    ' 示例：使用mshtml库提取表格数据
    Dim htmlDocument As MSHTML.HTMLDocument
    Set htmlDocument = New MSHTML.HTMLDocument
    
    ' 将HTML内容加载到HTML文档对象
    htmlDocument.body.innerHTML = htmlBody
    
    ' 查找HTML文档中的表格元素
    Dim tableElement As MSHTML.IHTMLElement
    Set tableElement = htmlDocument.getElementsByTagName("table")(0)
    
    ' 将表格数据复制到Excel工作表
    If Not tableElement Is Nothing Then
        ' 将表格数据复制到Variant数组
        tableData = tableElement.outerHTML
        
        ' 将表格数据写入Excel工作表
        excelWorksheet.Range("A1").Value = tableData
    End If
    
    ' 保存并关闭工作簿
    excelWorkbook.SaveAs "C:\path\to\output.xlsx"
    excelWorkbook.Close
    
    ' 释放对象
    Set excelWorksheet = Nothing
    Set excelWorkbook = Nothing
    Set excelApp = Nothing
    Set htmlDocument = Nothing
End Sub