Need help adding color to Excel VBA code for Creating an Emaill

Question

What is the coding to adjust the size, font and color and background color of the body.... I need to be able to get it so that I can potentially make different references different colors and background colors. For example Worksheets("Email").Range("A38").Value to be black and bold with a yellow background and then Worksheets("Email").Range("A2").Value to be red font with a grey background color. Is this possible to change just specific reference colors or can i just only change the color of the body as a whole?

So far my coding is as follows cant figure out how to add color...

Private Sub CreateMail()

    Dim oOutlook As Object

    Dim oMailItem As Object

    Dim oRecipient As Object

    Dim oNameSpace As Object

    Set oOutlook = CreateObject("Outlook.Application")

    Set oNameSpace = oOutlook.GetNameSpace("MAPI")

    oNameSpace.Logon , , True

    Set oMailItem = oOutlook.CreateItem(0)

    With oMailItem

        .To = Worksheets("Data").Range("L11").Value

        .CC = Worksheets("Data").Range("L8").Value & "; " & _

        Worksheets("Data").Range("L5").Value

        .Subject = "*****Confirmed Online Intrusion - " & Worksheets("Data").Range("F10").Value & "*****"

        .Body = Worksheets("Email").Range("A38").Value & Worksheets("Email").Range("A2").Value & vbNewLine & Application.UserName

        .Display

    End With

End Sub

Answer 1

Hi,

You have to use .HTMLBody, not .Body, and you have to code in HTML :o(

This is a helpful page: http://www.computerhope.com/learnhtm.htm, as is http://www.computerhope.com/htmcolor.htm

Try:

        Dim strMsg As String

        strMsg = "<P>Some text here " _

            & "<FONT style=""BACKGROUND-COLOR: yellow""><STRONG>" _

            & Worksheets("Email").Range("A38").Value _

            & "</FONT></STRONG>" _

            & " some more text " _

            & "<FONT color=""red"" style=""BACKGROUND-COLOR: gray"">" _

            & Worksheets("Email").Range("A2").Value _

            & "</FONT>" _

            & " closing text here.</P>" & Application.UserName

         .HTMLBody = strMsg

         .Display

or

        Dim strMsg As String

        strMsg = "<P><FONT style=""BACKGROUND-COLOR: yellow""><STRONG>" _

            & Worksheets("Email").Range("A38").Value _

            & "</FONT></STRONG>" _

            & " " _

            & "<FONT color=""red"" style=""BACKGROUND-COLOR: gray"">" _

            & Worksheets("Email").Range("A2").Value _

            & "</FONT></P>" & Application.UserName

        .HTMLBody = strMsg

        .Display

Note that when building your string in VBA, any quotation marks to appear within the string need to be doubled-up, like this:

    myString="Here is a string containing a quote ""My quote"" which is nice."

would return:   Here is a string containing a quote "My quote" which is nice.

Make sense?

Hope that helps.

Cheers

Rich