如何在Outlook邮件插入Excel表格后添加正文文本

问题核心在于.Body属性仅支持纯文本内容，一旦用它拼接新内容，会覆盖掉之前的富文本格式（包括表格）。要保留格式，必须全程通过Outlook内嵌的Word编辑器（WordEditor）来操作邮件内容。

修改后的代码 #

Sub RangeToOutlook_Single()

Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector
Dim wsSmy As Worksheet, wsCP As Worksheet
Dim LastRow As Integer, LastColumn As Integer
Dim StartCell As Range
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range
Dim ExcRng As Range

On Error Resume Next

Set oLookApp = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Err.Clear
        Set oLookApp = New Outlook.Application
    End If
    
Set oLookItm = oLookApp.CreateItem(olMailItem)

Set wsSmy = Workbooks("HRDA Audit_Master.xlsb").Sheets("SUMMARY")
Set wsCP = Workbooks("HRDA Audit_Master.xlsb").Sheets("Control Panel")
Set StartCell = wsSmy.Range("A1")
LastRow = wsSmy.Cells(wsSmy.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = wsSmy.Cells(StartCell.Row, wsSmy.Columns.Count).End(xlToLeft).Column
Set ExcRng = wsSmy.Range("A1:R" & LastRow)

With oLookItm
    .To = wsSmy.Range("S" & LastRow) ' 补充工作表限定，避免当前工作表歧义
    .CC = ""
    .Subject = "HRDA Audit - " & wsCP.Range("B1")
    .Display ' 必须先显示邮件才能获取Word编辑器
    
    ' 获取Word编辑对象
    Set oLookIns = .GetInspector
    Set oWrdDoc = oLookIns.WordEditor
    Set oWrdRng = oWrdDoc.Content
    
    ' 写入开头文本
    oWrdRng.Text = "Dear " & wsSmy.Range("T" & LastRow) & "," & vbCrLf & vbCrLf & _
                   "Here is the first half of the text before the table." & vbCrLf & vbCrLf
    ' 将光标移到文本末尾
    oWrdRng.Collapse Direction:=wdCollapseEnd
    
    ' 粘贴Excel表格
    ExcRng.Copy
    oWrdRng.Paste
    
    ' 再次将光标移到表格末尾，添加后续文本
    Set oWrdRng = oWrdDoc.Content
    oWrdRng.Collapse Direction:=wdCollapseEnd
    ' 添加换行和自定义后续内容
    oWrdRng.Text = vbCrLf & vbCrLf & "This is the text after the table. You can add any content here."
End With
       
End Sub

关键说明 #

• 必须先调用.Display才能获取WordEditor，未显示的邮件尚未初始化Word编辑环境
• 全程通过Word.Range对象操作内容，彻底避免使用.Body导致格式丢失
• 每次操作后用Collapse(wdCollapseEnd)将光标移到当前内容末尾，确保新内容追加在正确位置
• 用vbCrLf代替vbLf，更符合Word/Outlook的换行规范

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