Excel VBA：如何让Outlook邮件同时显示正文与单元格区域图片？

问题说明 #

需求是通过Excel VBA将指定单元格区域复制到带有正文内容的Outlook邮件中，但原代码运行后只能显示单元格图片或仅显示文本正文，无法同时展示两者。

原代码 #

Sub SendEmail_Class_Name()

Dim r As Range
Set r = Range("A1:AA3")
r.Copy

Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application

Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)

EmailItem.To = "Name@gmail.com"
EmailItem.CC = "Name1@gmail.com; Name2@gmail.com;" & _
                "Name3@gmail.com; Name4@gmail.com"
EmailItem.Subject = "Test Email From Excel VBA"
EmailItem.HTMLBody = "Hey,<p>" & "Seeing if this Macro will send an Email<p>" & _
                    "Thanks,<p>"
Source = ThisWorkbook.FullName
EmailItem.Attachments.Add Source

EmailItem.Display
Dim wordDoc As Word.Document
Set wordDoc = EmailItem.GetInspector.WordEditor

wordDoc.Range.PasteAndFormat wdChartPicture

End Sub

解决方案 #

问题根源在于直接对整个文档范围执行粘贴或设置HTMLBody操作会覆盖原有内容。只需调整代码，将粘贴操作定位到文档末尾，即可在正文后追加单元格图片。修改后的代码如下：

Sub SendEmail_Class_Name()
    Dim r As Range
    Set r = Range("A1:AA3")
    r.Copy

    Dim EmailApp As Outlook.Application
    Dim Source As String
    Set EmailApp = New Outlook.Application

    Dim EmailItem As Outlook.MailItem
    Set EmailItem = EmailApp.CreateItem(olMailItem)

    ' 设置收件人、抄送人、主题
    EmailItem.To = "Name@gmail.com"
    EmailItem.CC = "Name1@gmail.com; Name2@gmail.com;" & _
                    "Name3@gmail.com; Name4@gmail.com"
    EmailItem.Subject = "Test Email From Excel VBA"
    
    ' 设置HTML格式的正文内容
    EmailItem.HTMLBody = "Hey,<p>Seeing if this Macro will send an Email<p>Thanks,"
    
    ' 添加附件
    Source = ThisWorkbook.FullName
    EmailItem.Attachments.Add Source

    ' 显示邮件并获取Word编辑器对象
    EmailItem.Display
    Dim wordDoc As Word.Document
    Set wordDoc = EmailItem.GetInspector.WordEditor

    ' 定位到文档末尾，再粘贴单元格区域图片
    With wordDoc.Range
        .Collapse Direction:=wdCollapseEnd
        .PasteAndFormat wdChartPicture
    End With
End Sub

代码说明 #

1. 先设置好邮件的收件人、主题、HTML正文和附件，确保基础内容已写入邮件。
2. 调用EmailItem.Display后，获取邮件对应的Word编辑器对象Word.Document。
3. 使用.Collapse Direction:=wdCollapseEnd将光标定位到文档末尾，避免覆盖原有正文内容。
4. 在末尾执行粘贴操作，将单元格区域以图片形式追加到正文之后。

内容的提问来源于stack exchange，提问作者Trat246