0

I am trying to change the text so certain values from cells are either bold, underlined, red, or otherwise stand out from the surrounding text in the body of the email.

How can I do that?

For i = 10 To 18
    If Not Cells(i, "A").Text = vbNullString Then
        'Add to growing string
        concatString = concatString + Cells(i, "A").Text & ", " & _
          Cells(i, "B").Text & vbCr
        concatString = concatString + "Assignment/Zone: " & _
          Cells(i, "C").Text & vbNewLine & vbCr
    End If
Next i

myMail.Body = Range("B2") & " Shift" & "  -  " & Format(Date, "mmmm dd, yyyy") _
    & vbNewLine & vbNewLine & "Sergeant: " & Range("A6") & ", " & Range("B6") & _
    vbNewLine & "          Status: " & Range("C6") _
    & vbNewLine & vbNewLine & "Corporal: " & Range("A8") & ", " & Range("B8") & _
    vbNewLine & "          Status: " & Range("C8") _
    & vbNewLine & vbNewLine & "Assigned Deputies" & vbNewLine & vbNewLine & concatString
Community
  • 1
  • 1
Loren Scholes
  • 43
  • 6
  • 1
    Previously: https://stackoverflow.com/questions/28868877/formatting-email-body-from-excel-contents. Basically you need to look into using HTML-formatted content to apply the colors etc you want. – Tim Williams Jan 21 '20 at 00:28
  • I appreciate you taking the time to respond, but the link you provided doesn't link to an article addressing formatting the text. The one you sent tells how to attach it, but not how to change it to Bold, Italic, etc. Would you mind sending another one? – Loren Scholes Jan 21 '20 at 01:04
  • 1
    There's a good example here: https://www.extendoffice.com/documents/excel/5030-excel-vba-send-email-format-bold-text.html – Tim Williams Jan 21 '20 at 01:17
  • does https://stackoverflow.com/q/34914664 or https://stackoverflow.com/q/22200910 help? are you working with `.HTMLBody`? – hongsy Jan 21 '20 at 03:33

2 Answers2

0

You need to look into using HTML-formatted content to apply the colors etc you want:

Dim oApp As Object, oMail As Object

Set oApp = CreateObject("outlook.application")

Set oMail = oApp.createitem(0)

oMail.Display

oMail.htmlBody = "<h1>This is a heading</h1>" & _
                 "<p style='color:#F00'>Some red text</p>" & _
                 "<p><u>Underlined</u></p>" & _
                 "<p><b>Bold</b></p>" & _
                 "<p><i>Italic</i></p>"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I have put my first line of code on here. I am getting a "Compile error: Expected: Expression" error message, and the debugger is highlighting "= <" and I can't figure out what I'm entering wrong. Everything I have found, including the above link, shows how to do this with text you are entering into the code, but mine is text from a cell reference. Any ideas? – Loren Scholes Jan 21 '20 at 02:17
  • myMail.htmlBody = Range("B2") & " Shift" & " - " & Format(Date, "mmmm dd, yyyy") _ – Loren Scholes Jan 21 '20 at 02:17
  • I'm getting close with your code. Can you tell me what in your code is causing the lines to drop? Heading, Some red text, Underlined, Bold, Italic are all on different lines and I can't figure out why... – Loren Scholes Jan 21 '20 at 02:46
  • That's just for clarity - the `_` allows you to continue on the next line. It could just as well have all been jammed into a single line, but that wouldn't be very readable. Unless I'm misunderstanding what you mean by "drop". You will need to do a little basic HTML study to make this work though. – Tim Williams Jan 21 '20 at 03:35
  • Yes, sir, I knew how it was MAKING the line, but there are also lines in there that aren't set with the "_" and I can't figure it out. I noticed, though, that taking the final "p" out of the html code "" also forces the drop to the next line. I'm not talking about the lines in the code itself, I'm talking about the lines on the resultant body of the email. – Loren Scholes Jan 21 '20 at 03:59
  • I'm sorry for not being clear, I am really new to this and have been trying to figure this out for two solid days, doing nothing but this. – Loren Scholes Jan 21 '20 at 03:59
  • In HTML, `

    Content here

    ` represents a paragraph of text: the opening `

    ` creates a new line in the rendered content. HTML does not honor whitespace such as new lines or tabs (and multiple spaces will get collapsed to a single space). All layout/formatting is via the HTML elements and css styles.

     – Tim Williams Jan 21 '20 at 04:10
0

I needed to use <br> to put the resultant answer in the email body. <p> creates a new PARAGRAPH, while <br> just puts it on the next line.

& "<br><b><u>Status:</u></b>"

gives:

& "
Status:" &

Instead of: & "<p><b><u>Status:</u></b>"

Which gives:

& "

Status:"

Thank you for your help!

Loren Scholes
  • 43
  • 6