Using VBA to export an excel table to outlook but the signature is pasting above the range not beneath

Question

I'm relatively new to VBA and I'm attempting to export a range of cells to outlook to send off in an email. I want whoever is running the macro to have their default signature pasted underneath the range however when running my code it pastes the range below the signature and not above it.

I've tried to use the .HTMLBody function to to create the default signature in outlook but I can't get it to be positioned in the right place.

Ideally I would add the initial htmlbody text then paste the range then add the default signature using & .htmlbody but I can't do that.

I've tried alternatives such as calling the signature from a .htm file or a .rtf file but this doesn't fit my purposes as I need whoever is running the macro's signature to be inputed automatically.

Any help or advice would be appreciated.

This is an exercpt of my code

'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
       
        'Display the email
       
        
        '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 here

Let me know if any more information is needed

Answer 1

This worked for me - replaces a placeholder in the content with the pasted range from Excel:

'add references to Word and Outlook object models...
Sub Tester77()
    Const TABLE_PLACEHOLDER As String = "XXXtableXXX"
    Dim oLookApp As Outlook.Application, oLookItm As Outlook.MailItem
    Dim oLookIns As Outlook.Inspector, oWrdDoc As Word.Document, oWrdRng As Word.Range, CopyRange1 As Range
    
    Set CopyRange1 = ThisWorkbook.Worksheets("TEST EMAILS").Range("Z2").CurrentRegion

    On Error Resume Next
    Set oLookApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If oLookApp Is Nothing Then Set oLookApp = New Outlook.Application
           
    With oLookApp.CreateItem(olMailItem)
        '.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><br><br>" & _
         TABLE_PLACEHOLDER & .HTMLBody
       
        Set oLookIns = .GetInspector
        Set oWrdDoc = oLookIns.WordEditor
        Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
        
        'find the table placeholder and paste the Excel range
        With oWrdRng.Find
            .Text = TABLE_PLACEHOLDER
            If .Execute Then
                CopyRange1.Copy
                'If the `Find` succeeded then `oWrdRng` is 
                ' now pointing to the range for the found text
                oWrdRng.PasteSpecial DataType:=wdPasteHTML
            End If
        End With
    
    End With
End Sub