excel - VBA将电子邮件正文复制到Excel表格

Question

您好，我有以下代码，它成功循环遍历我的文件夹并提取我想要的电子邮件并将正文(以表格格式)复制到 Excel 中。但是，当我粘贴它时，整个正文会被粘贴到单元格 A1 中，而此时它应该填充范围 A1:K92，就像我手动复制并粘贴它一样。有没有办法使用vba将其粘贴到正确的范围内？ 谢谢!

Sub GetFXEmail()
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMi As Variant
Dim i As Integer
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
Set Fldr = Fldr.Folders("MyFolder")
Set inboxItems = Fldr.Items

pnldate = Format((Date - 1), "mm/dd/yyyy")

Set inboxItems = Fldr.Items
inboxItems.Sort "[ReceivedTime]", True
For i = 1 To Fldr.Items.Count Step 1
    Set olMi = Fldr.Items(i)
        If Format(olMi.ReceivedTime, "mm/dd/yyyy") = pnldate Then
            Debug.Print olMi.ReceivedTime
            Debug.Print olMi.Subject
            If InStr(1, olMi.Subject, "Breakdown") > 0 Then
                Sheets("Sheet1").Range("A1") = olMi.Body
                GoTo AllDone
            End If
        End If
Next i

AllDone:
End Sub

Answer 1

如果您的电子邮件中只有 1 个表格，并且它被识别为实际表格，则此代码(放置在第一个 If block 内)将起作用(并且已经过测试)。如果需要，您可以修改部件以满足您的具体需求。

另请注意，它需要对 Microsoft Word 对象库的引用(因为您已经拥有 Outlook 对象库)。

If Format(olMi.ReceivedTime, "mm/dd/yyyy") = pnldate Then

    With olMi

        Debug.Print .ReceivedTime
        Debug.Print .Subject

        Dim olInsp As Outlook.Inspector
        Set olInsp = .GetInspector

        Dim wdDoc As Word.Document
        Set wdDoc = olInsp.WordEditor

        Dim tb As Word.Table
        For Each tb In wdDoc.Tables 'assumes only 1 table
            Dim y as Long, x as Long
            For y = 0 To tb.Rows.Count
                For x = 0 To tb.Columns.Count
                    Sheets("Sheet1").Range("A1").Offset(y, x).Value = tb.Cell(y, x).Range.Text
                Next
            Next
        Next

    End With

    GoTo AllDone

End If