Chandoo Email.xlsx Macro - changing output format

[Brett.Austin]

Hi All,


I am hoping someone can point me in the right direction - I was wondering if it is at all possible in the below code, to have some formatting on the output text?


So example, when the email is written, where it says "Cost Centre:" I would like this to be bold tex, but the result of Range("CostCentres") to be non-bold text


Thanks

Brett


Set olApp = New Outlook.Application

Dim olNs As Outlook.Namespace

Set olNs = olApp.GetNamespace("MAPI")

olNs.Logon

Set olMail = olApp.CreateItem(olMailItem)

olMail.To = strEmailTo

olMail.CC = strEmailCC

olMail.BCC = strEmailBCC

olMail.Subject = "Delegations Update Request"

olMail.Body = vbCrLf & "Hello Accounting Operations," _

& vbCrLf & vbCrLf & "Please action the following delegations changes." _

& vbCrLf & vbCrLf & "Cost Centre: " & Range("CostCentres") _

& vbCrLf & vbCrLf & "Change Type: " & Range("ChangeType") _

& vbCrLf & vbCrLf & "Date End: " & Range("DateEnd") _

& vbCrLf & vbCrLf & "Level: " & Range("SelectLevel") _

& vbCrLf & vbCrLf & "Change From Name: " & Range("ChangeFrom") _

& vbCrLf & vbCrLf & "Change To Name: " & Range("ChangeTo") _

& vbCrLf & vbCrLf & "Other Instructions: " & Range("OtherInstructions") _

& vbCrLf & vbCrLf & "Regards,"

 

[Jordan]

Instead of "olMail.body", use "olMail.HTMLBody". This will allow you to add HTML markup to the body text of your email message. What you want to make bold, surround with <b> and </b> tags i.e. "<b>Cost Centre:</b>". You can also use other HTML tags as necessary.

 

[Brett.Austin]

Thanks Jordan - mostly got it to work. However, how do I format the "Range" bits. All the other text formats.


I got it so far to this, only the bits where free text in the " " are works.


olMail.Subject = Range("BatchType") & " Posting Request"

olMail.HTMLBody = "<font face='arial' size=2> Hello Accounting Operations Team,</font>


" _

& "<font face='arial' size=2>Please post the following " & Range("BatchType") & "es:</font>


" _

& Range("Batch1") & "


" _

& "<font face='arial' size=2>Regards,</font>
" _

& Range("BPRequestor")

 

[Jordan]

For the "range parts" you would just surround them with strings of HTML tags as you did with your labels. For example,

[pre]

<br />
olMail.HTMLBody = _<br />
"<font face='arial' size=2>Please post the following <strong><b>" & Range("BatchType") & "es: </b></strong></font>"<br />[/pre]

However, if you want the entire body text to be Arial you need only set its font face once - then close its corresponding tag at the end of the message. 


Also, if writing "<b> </b>" becomes cumbersome and makes your writing hard to follow, you might consider creating User Defined Function to automatically format parts of your message to be bold. For example:




Public Function FormatToBoldText(sTextToFormat as String) As String

FormatToBoldText = "<b>" & sTextToFormat & "</b>"

End Function

Then in your code, you would write HTMLBody = "BlahBlah: " & FormatToBoldText(Range("Batches").Value) & ". Thank you!"