Adding signature in Outlook email after .Body using Excel VBA

0
votes

In Excel VBA I want to create a macro to send a letter via Outlook.

I need a signature, but I cannot create it, because I have a .Body part and I need to paste a dynamic range of cells after it.

With newEmail
    .Display
    .SentOnBehalfOfName = ""
    .To = ""
    .CC = ""
    .Subject = ""
    .BodyFormat = olFormatHTML
    .Body = "Good day" & vbCrLf & "bla bla "

    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor

    Sheet1.Range("G128", ActiveSheet.Range("G128").End(xlDown)).Copy
    
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.Paste
    
    .Display
    .Send

What and where should I add, to add a signature after the code pastes the range?

P.S. .Body before the range is necessary.

3
excelvbaoutlook
Cristian's answer just have to be tweaked so that you get the signature file (which is just some html) file from whatever location your team's signature is. – Ricardo Diaz

3 Answers

0
votes

Create a new range in the Word document and call Range.insertFile() to insert the HTML file of the signature.

0
votes

If you construct a signature in the code and store it in a variable, e.g. Signature, you could do something like this...

With newEmail
    .SentOnBehalfOfName = ""
    .To = ""
    .CC = ""
    .Subject = ""
    .BodyFormat = olFormatHTML
    .Body = "Good day" & vbCrLf & "bla bla "
    
    Sheet1.Range("G128", ActiveSheet.Range("G128").End(xlDown)).Copy
    
    With .GetInspector.WordEditor.Range
        .Collapse 0
        .Paste
        .Collapse 0
        .Text = vbCrLf & Signature
        .Style = "No Spacing"
    End With
    
    .Display
    '.Send
End With
-1
votes

Try to add at the end .body = .body & .HTMLbody