CODEWITHSUNDEEP
excelvbaemailoutlook
Bartek Burek
Bartek Burek

Reputation: 3

How to bold Outlook text from dynamic Excel cells in VBA?

I want to format the dynamic cells in the body to be bold.

I want the email body to look like that:

Dear User,

The following request is in I8.1 Check Resource Availability:

Test Project

The estimated effort below is called out in the request's ROM (in hours): 10 and duration (in business days): 2 Do you have a named resource I can put in for the effort called out? Please provide me with an update at the earliest

Thank you and best regards,

Team.

This code is working but I can't find a way to bold the desired values.

Sub Sendmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("D").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
            LCase(Cells(cell.Row, "E").Value) <> "0" Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Resources awaiting assignment"
                .Body = "Dear " & Cells(cell.Row, "C").Value _
                & ", " _
                & vbNewLine & vbNewLine & _
                     "The following request is in I8.1 Check Resource Availability: " _
                     & vbNewLine & vbNewLine & _
                     Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                    "The estimated effort below is called out in the request's ROM (in hours):  " & Cells(cell.Row, "E").Value _
                    & _
                    " and duration (in business days):  " & Cells(cell.Row, "F").Value _
                    & vbNewLine & vbNewLine & _
                    "Do you have a named resource I can put in for the effort called out? Please provide me with an update at the earliest" _
                    & vbNewLine & vbNewLine & _
                    "Thank you and best regards, " & vbNewLine & _
                    "Team."

                .Display
            End With

            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

I checked some posts on formating body of an email in VBA (e.g. How to format email body?, formatting email body using VBA or Bolding Text with VBA).

Upvotes: 0

Views: 3607

Answers (2)

Tony Dallimore
Tony Dallimore

Reputation: 12413

BraX finished their answer, which contained all the important points, before me. Perhaps you need my longer answer.

Body is the text body for an email. BodyHtml is the Html body.

For Html, whitespace is defined as any of carriage return, linefeed, tab, space and some other more obscure characters. Any string of whitespace is the equivalent of a single space. So:

", " & vbNewLine & vbNewLine & "The following request is in I8.1"

is the same as: ", The following request is in I8.1"

because the string of whitespace characters - space newline newline - becomes a single space. You need to start a paragraph with <P>. A linebreak is created with <BR>.

The easiest, if old fashioned, way of formatting some text as bold is by enclosing it in <B>xxx</B>.

You have some double spaces in your text. If you want extra spaces, you need to use a non-break space which you can specify with &NBSP;

So try:

.BodyHtml =  "<P>Dear " & Cells(cell.Row, "C").Value & ", " _
             "<P>The following request is in I8.1 Check Resource Availability: " _
             "<P><B>" & Cells(cell.Row, "A").Value & "</B>" _
             "<P>The estimated effort below is called out in the request's ROM (in hours): &NBSP;" & _
                 "<B>" & Cells(cell.Row, "E").Value & "</B> and duration (in business days): &NBSP;" & _
                 "<B>" & Cells(cell.Row, "F").Value  & "</B>" _
             "<P>Do you have a named resource I can put in for the effort called out? " & _
                 "Please provide me with an update at the earliest" _
             "<P>Thank you and best regards, <BR>" _
             "<P>Team."

I have not tested this VBA specified Html. I hope there are no mistakes but I am sure you can see what I am attempting if there are mistakes.

Upvotes: 0

braX
braX

Reputation: 11755

Use .HTMLBody instead of .Body

Then you can use HTML - Change your vbNewLine to <br> and use <b> text here </b> to make things bold.

Upvotes: 2

Related Questions