Excel VBA：复制Word内容生成邮件时如何添加自定义开头文本？

你当前的VBA代码能实现从Excel提取邮箱、复制Word模板内容（保留格式）并添加附件，但尝试在邮件开头添加Dear <姓名>时失败——直接设置.HTMLBody会被后续的editor.Content.Paste覆盖，因为粘贴操作会替换邮件的全部内容。

解决方法：先插入前缀再粘贴模板内容 #

利用Outlook邮件内置的Word编辑器，先定位到内容起始位置插入问候语，再粘贴Word模板内容，就能保证前缀显示在最前面。修改后的完整代码如下：

Sub ThanksEmailFromDoc()
    Dim wd As Object, editor As Object
    Dim doc As Object
    Dim oMailItem As Object, oOLapp As Object
    Dim Sender As String 
    Dim AttachmentA As String
    Dim AttachmentB As String
    Dim Template As String
    Dim recipientName As String '存储收件人姓名

    Sender = "send@test.com" 
    recipientName = Range("B" & ActiveCell.Row).Value '从当前行B列获取姓名

    Template = "C:\Files\Template.docx"
    AttachmentA = "C:\Files\AttachmentA.pdf"
    AttachmentB = "C:\Files\AttachmentB.pdf"
    
    Set wd = CreateObject("Word.Application") 
    Set doc = wd.Documents.Open(Filename:=Template, ReadOnly:=True) 
    doc.Content.Copy 
    doc.Close 
    Set wd = Nothing
    
    Set oOLapp = CreateObject("Outlook.Application") 
    Set oMailItem = oOLapp.CreateItem(0) 
    With oMailItem 
        .SentOnBehalfOfName = Sender 
        .BodyFormat = olFormatHTML 
        Set editor = .GetInspector.WordEditor 
        
        '插入带格式的问候语到邮件开头
        editor.Range(0, 0).Text = "Dear " & recipientName & ":" & vbCrLf & vbCrLf
        '如果需要姓名为斜体，取消下面两行注释：
        'editor.Range(5, 5 + Len(recipientName)).Font.Italic = True
        'editor.Range(5, 5 + Len(recipientName)).Font.Bold = False '可选：取消加粗
        
        '粘贴Word模板内容，保留原格式
        editor.Content.PasteAndFormat wdFormatOriginalFormatting
        
        .To = Range("D" & ActiveCell.Row).Value 
        .Subject = "Thanks for your interest" 
        .Attachments.Add AttachmentA 
        .Attachments.Add AttachmentB 
        .Display 
    End With

    Set oOLapp = Nothing
    Set oMailItem = Nothing
    
End Sub

关键改动说明 #

• 新增recipientName变量专门存储B列的收件人姓名，代码逻辑更清晰
• 通过editor.Range(0, 0)精准定位到邮件内容的起始位置，插入问候语并添加换行符分隔前缀与正文
• 使用PasteAndFormat wdFormatOriginalFormatting替代普通粘贴，确保Word模板的格式（如字体、段落样式）完全保留
• 可选添加斜体格式：通过调整Range的起止位置，给姓名单独设置斜体属性

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