如何将Excel单元格区域导出到Outlook邮件签名上方？

尝试将Excel单元格区域导出到Outlook邮件发送，但代码会把区域粘贴到签名下方，而非期望的「初始HTML内容 → 单元格区域 → 默认签名」顺序。使用.HTMLBody调用Outlook默认签名，但无法调整位置；尝试从.htm/.rtf文件调用签名，不符合自动插入用户签名的需求。

原代码片段：

'Get the Active instance of Outlook if there is one
Set oLookApp = GetObject(, "Outlook.Application")

    'If Outlook isn't open then create a new instance of Outlook
    If Err.Number = 429 Then

        'Clear Error
        Err.Clear

        'Create a new instance of Outlook
        Set oLookApp = New Outlook.Application

    End If

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)
Set CopyRange1 = ThisWorkbook.Worksheets("TEST EMAILS").Range("Z2").CurrentRegion

'Create an array to hold ranges
With oLookItm

    'Define some basic info of our email
    .To = "xyz@abc.com"
    .CC = "Team@email.com"
    .BCC = EmailAddresses
    .Subject = "Here are all of my Prices"
    .Display

   .HTMLBody = "<span style='background:yellow;mso-highlight:yellow'>" & "SENSITIVE INFORMATION" & "<a href=""SENSITIVE INFORMATION""><u><b>SENSITIVE INFORMATION </a></u></b></span><br><br>" & "<img src='C:\Users\User\Pictures\Picture1.png'><br>" & "SENSITIVE INFORMATION<br>" & "SENSITIVE INFORMATION &" & "<b><u> SENSITIVE INFORMATION </b></u>" & "SENSITIVE INFORMATION<br>" & "SENSITIVE INFORMATION" & "<b><font color=red> SENSITIVE INFORMATION </font></b>" & "SENSITIVE INFORMATION<br>" & "<b>SENSITIVE INFORMATION</b>" & .HTMLBody

    'Get the Active Inspector
    Set oLookIns = .GetInspector

    'Get the document within the inspector
    Set oWrdDoc = oLookIns.WordEditor

    CopyRange1.Copy

    'Define the range, insert a blank line, collapse the selection.
    Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
        oWrdRng.Collapse Direction:=wdCollapseEnd

    'Add a new paragragp and then a break
    Set oWrdRng = oWdEditor.Paragraphs.Add
        oWrdRng.InsertBreak

    'Paste the object.
    oWrdRng.PasteSpecial DataType:=wdPasteHTML

CopyRange1.Delete
End With

Unload UserForm2

核心问题是当前代码在文档末尾（签名后）粘贴内容，调整思路是通过占位标记定位初始内容与签名的中间位置，将表格粘贴到该位置。以下是修改后的代码：

'Get the Active instance of Outlook if there is one
Set oLookApp = GetObject(, "Outlook.Application")

    'If Outlook isn't open then create a new instance of Outlook
    If Err.Number = 429 Then
        Err.Clear
        Set oLookApp = New Outlook.Application
    End If

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)
Set CopyRange1 = ThisWorkbook.Worksheets("TEST EMAILS").Range("Z2").CurrentRegion

With oLookItm
    'Define basic email info
    .To = "xyz@abc.com"
    .CC = "Team@email.com"
    .BCC = EmailAddresses
    .Subject = "Here are all of my Prices"
    
    '先Display获取默认签名的HTML并保存
    .Display
    Dim signatureHTML As String
    signatureHTML = .HTMLBody
    
    '构建初始HTML + 占位标记 + 签名，设置为邮件正文
    Dim initialHTML As String
    initialHTML = "<span style='background:yellow;mso-highlight:yellow'>" & "SENSITIVE INFORMATION" & _
        "<a href=""SENSITIVE INFORMATION""><u><b>SENSITIVE INFORMATION </b></u></a></span><br><br>" & _
        "<img src='C:\Users\User\Pictures\Picture1.png'><br>" & _
        "SENSITIVE INFORMATION<br>" & _
        "SENSITIVE INFORMATION &amp;" & _ '修正HTML语法错误，&转成&amp;
        "<b><u> SENSITIVE INFORMATION </b></u>" & _
        "SENSITIVE INFORMATION<br>" & _
        "SENSITIVE INFORMATION" & _
        "<b><font color=red> SENSITIVE INFORMATION </font></b>" & _
        "SENSITIVE INFORMATION<br>" & _
        "<b>SENSITIVE INFORMATION</b>" & _
        "<p><!--PASTE_TABLE_HERE--></p>" '添加占位标记
    
    .HTMLBody = initialHTML & signatureHTML
    
    '获取Word编辑器对象
    Set oWrdDoc = .GetInspector.WordEditor
    
    '查找占位标记的位置
    Set oWrdRng = oWrdDoc.Content
    With oWrdRng.Find
        .Text = "<!--PASTE_TABLE_HERE-->"
        .Forward = True
        .Execute
    End With
    
    '如果找到占位标记，删除标记并粘贴表格
    If oWrdRng.Find.Found Then
        oWrdRng.Delete
        CopyRange1.Copy
        oWrdRng.PasteSpecial DataType:=wdPasteHTML
    End If
    
    CopyRange1.Delete
End With

Unload UserForm2

• 先调用.Display获取默认签名HTML并保存，避免后续操作覆盖签名
• 在初始HTML中添加<!--PASTE_TABLE_HERE-->占位标记，明确表格插入位置
• 使用Word查找功能定位占位标记，删除后粘贴表格，确保内容顺序符合预期
• 修正原HTML语法错误：将&转成&，避免破坏HTML结构

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