write a formatted email using excel macro

[Mark Lee]

i have created a macro to edit a spreadsheet and i wish to add a step to create and edit an email. So far i have the following which works but is creating a paragraph space between the bottom two lines. i have attached an example to show the difference. i know its minor but if im going to use it i would like it to be right.

Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients

Set OutApp = CreateObject("Outlook.Application")
Set objOutlookMsg = OutApp.CreateItem(olMailItem)

objOutlookMsg.SentOnBehalfOfName = "FinancialSystems@infrastructure-ni.gov.uk"
objOutlookMsg.CC = "RoadsandRivers.FBP@infrastructure-ni.gov.uk"
objOutlookMsg.Subject = "Payment Due Tomorrow"
objOutlookMsg.HTMLBody = "Please find attached a report detailing invoices in workflow which need completed within 1 day to avoid breaching the Department's 10 day Prompt Payment target.<p>For each payment that breaches this target an explanation is required from the Approving Officer on the monthly exception report.<p>I would be grateful if you could complete the required actions within the deadlines to help the Department improve its overall statistics.</br></br><b><p style=font-size:11pt;font-family:Calibri>Issued on behalf of DFI Financial Systems Branch</b><p style=font-size:11pt;font-family:Calibri>Email: FinancialSystems@infrastructure-ni.gov.uk</br>"

objOutlookMsg.Display

Set OutApp = Nothing

[#2 · Accepted solution]

If I'm not mistaken, you would like it displayed as a line of text, a double space, a line of text, a double space, a line of text, a double space, a bold line of text, and a line of text.  I noticed a lot of <p> tags in the body, which actually stands for Paragraph, so outlook is doing what you're telling it to do.  The last 2 sections also have the font size and family specified, and they're both the same, so those two lines will be Calibri / 11 regardless of what your default font is for outlook.  That being said, you can change the body line to this and I think it should correct your formatting.

objOutlookMsg.HTMLBody = "<p>Please find attached a report detailing invoices in workflow which need completed within 1 day to avoid breaching the Department's 10 day Prompt Payment target.</p><p>For each payment that breaches this target an explanation is required from the Approving Officer on the monthly exception report.</p><p>I would be grateful if you could complete the required actions within the deadlines to help the Department improve its overall statistics.<b><p style=font-size:11pt;font-family:Calibri>Issued on behalf of DFI Financial Systems Branch</b><br>Email: FinancialSystems@infrastructure-ni.gov.uk</p>"

[#3]

Bob Thank you, that is exactly what i needed.

I have only recently started trying to teach myself macros using the recorder and forums to put together what i need and sometimes just cant find the right combination