VBA - Outlook Email

[30136353]

Hi Guys,

I have the below code, working almost to how I need it. The 3 additional things I need ot get right, and I amstrugling with:

1 - The body text is in the wrong font / size, I need this configured to calibri(body) size 11, black font.
2 - My email signature does pastes in at the bottom as needed, but its in a different font/ colour as my original and loses the hyperlinks etc...
3 - Within the body to replace the middle blank line " " & vbNewLine & _ - I would like to paste in a range of cells A8:J39...

(Don't worry about the stars within the quotes, its to blank out sens info)

Any help would be appreciated.

    Sub Send_Emails()

    Dim oLook As Object, oMail As Object, Signature As String
 

    Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)

With oMail
    .Display

    End With

    Signature = oMail.Body

     With oMail

     .To = "***;" & "***;"
.CC = "***;" & "***;"
.Subject = "*** - " & Range("c2").Value & " " & Range("I2").Value & " @ " & Range("K8").Value & " ***"
.Body = "Hi Guys," & vbNewLine & vbNewLine & _
"**** " & Range("K10").Value & " *** " & Range("c2").Value & " " & Range("I2").Value & " of " & Range("K4") & " *** " & Range("K8").Value & " SHP." & vbNewLine & _
" " & vbNewLine & _
"DL - Please find below *** " & Range("k12") & " ****" & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Thanks." & Signature

    End With

    Set oMail = Nothing
    Set oLook = Nothing

    End Sub

 

[daverunt]

Hi,

Look at using .HTMLBody for the email instead of Text
Ron De Bruin has a function here called RangetoHTML that keeps the format of the range you are copying from Excel to the mail body.

Mail Range/Selection in the body of the mail

You can modify it to keep hyperlinks:

Retain hyperlinks after rangetohtml paste Outlook?

Hi all, Can anyone help with an amendment to the below code to retain hyperlinks in an email generated using VBA, namely the RangetoHTML function? I am able to get the necessary range to paste to an email but the column with hyperlinks is lost (they still show up as blue text but can't be...

www.mrexcel.com

Using HTML in the body requires that vbNewline be replaced with html break tags '<br>' and you treat it as a string e.g "<br><br>"