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

Does this answer your question? [How to add default signature in Outlook](https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) — braX, Jul 19 '23 at 18:49
No not really because my issue isn't that I don't know how to include the signature, the issue is that I can't get it to be positioned right. But thank you for trying to help — David Dada, Jul 19 '23 at 19:07
If it is just a range, I would look at Ron De Bruin [RangetoHTML](https://www.rondebruin.nl/win/s1/outlook/bmail2.htm) — Kavorka, Jul 19 '23 at 19:33
Thanks for the reply, I might not have explained properly but I meant to say the range part works fine and it shows up in the email but the issue is it's underneath the signature when it should be above. — David Dada, Jul 19 '23 at 19:37

score 1 · Answer 1 · answered Jul 19 '23 at 19:35

The problematic line of code is listed below:

.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

When you need to add something before the signature added automatically you need to find the opening <body> tag in the HTML markup of your message body an then paste the HTML snippet right after the found HTML tag.

Use the InStr function available in VBA to get the position of the <body> tag and then use the Replace function to replace the body tag with your content (and of course the body tag to keep the markup well-formed).

Hi thank you very much for your response, I don't mean to be a bother but can you further explain. The range I want to paste is done through this line "oWrdRng.PasteSpecial DataType:=wdPasteHTML" so how should I edit my code in order to fix the issue. — David Dada, Jul 19 '23 at 19:42

Tim Williams · Accepted Answer · 2023-07-19T20:58:55.420

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

Thank you very much for you response and taking the time to help. I'll definitely try this out as soon as possible and let you know how it goes! — David Dada, Jul 19 '23 at 20:48
Thanks you for your help this worked perfectly, much appreciated! — David Dada, Jul 20 '23 at 07:15

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

2 Answers2