SOLVED

write a formatted email using excel macro

%3CLINGO-SUB%20id%3D%22lingo-sub-254855%22%20slang%3D%22en-US%22%3Ewrite%20a%20formatted%20email%20using%20excel%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-254855%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20have%20created%20a%20macro%20to%20edit%20a%20spreadsheet%20and%20i%20wish%20to%20add%20a%20step%20to%20create%20and%20edit%20an%20email.%20So%20far%20i%20have%20the%20following%20which%20works%20but%20is%20creating%20a%20paragraph%20space%20between%20the%20bottom%20two%20lines.%20i%20have%20attached%20an%20example%20to%20show%20the%20difference.%20i%20know%20its%20minor%20but%20if%20im%20going%20to%20use%20it%20i%20would%20like%20it%20to%20be%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20OutApp%20As%20Outlook.Application%3CBR%20%2F%3EDim%20objOutlookMsg%20As%20Outlook.MailItem%3CBR%20%2F%3EDim%20objOutlookRecip%20As%20Recipient%3CBR%20%2F%3EDim%20Recipients%20As%20Recipients%3C%2FP%3E%3CP%3ESet%20OutApp%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3ESet%20objOutlookMsg%20%3D%20OutApp.CreateItem(olMailItem)%3CBR%20%2F%3E%3CBR%20%2F%3EobjOutlookMsg.SentOnBehalfOfName%20%3D%20%22FinancialSystems%40infrastructure-ni.gov.uk%22%3CBR%20%2F%3EobjOutlookMsg.CC%20%3D%20%22RoadsandRivers.FBP%40infrastructure-ni.gov.uk%22%3CBR%20%2F%3EobjOutlookMsg.Subject%20%3D%20%22Payment%20Due%20Tomorrow%22%3CBR%20%2F%3EobjOutlookMsg.HTMLBody%20%3D%20%22Please%20find%20attached%20a%20report%20detailing%20invoices%20in%20workflow%20which%20need%20completed%20within%201%20day%20to%20avoid%20breaching%20the%20Department's%2010%20day%20Prompt%20Payment%20target.%3C%2FP%3E%3CP%3EFor%20each%20payment%20that%20breaches%20this%20target%20an%20explanation%20is%20required%20from%20the%20Approving%20Officer%20on%20the%20monthly%20exception%20report.%3C%2FP%3E%3CP%3EI%20would%20be%20grateful%20if%20you%20could%20complete%20the%20required%20actions%20within%20the%20deadlines%20to%20help%20the%20Department%20improve%20its%20overall%20statistics.%3CB%3E%3C%2FB%3E%3C%2FP%3E%3CP%20style%3D%22font-size%3A11pt%3Bfont-family%3ACalibri%22%3EIssued%20on%20behalf%20of%20DFI%20Financial%20Systems%20Branch%3C%2FP%3E%3CP%20style%3D%22font-size%3A11pt%3Bfont-family%3ACalibri%22%3EEmail%3A%20FinancialSystems%40infrastructure-ni.gov.uk%22%3C%2FP%3E%3CP%3EobjOutlookMsg.Display%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20OutApp%20%3D%20Nothing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-254855%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-255221%22%20slang%3D%22en-US%22%3ERe%3A%20write%20a%20formatted%20email%20using%20excel%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-255221%22%20slang%3D%22en-US%22%3E%3CP%3EBob%20Thank%20you%2C%20that%20is%20exactly%20what%20i%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20only%20recently%20started%20trying%20to%20teach%20myself%20macros%20using%20the%20recorder%20and%20forums%20to%20put%20together%20what%20i%20need%20and%20sometimes%20just%20cant%20find%20the%20right%20combination%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-255188%22%20slang%3D%22en-US%22%3ERe%3A%20write%20a%20formatted%20email%20using%20excel%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-255188%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I'm%20not%20mistaken%2C%20you%20would%20like%20it%20displayed%20as%20a%20line%20of%20text%2C%20a%20double%20space%2C%20%3CSPAN%3Ea%20line%20of%20text%2C%20a%20double%20space%2C%26nbsp%3B%3C%2FSPAN%3Ea%20line%20of%20text%2C%20a%20double%20space%2C%20a%20bold%20line%20of%20text%2C%20and%20a%20line%20of%20text.%26nbsp%3B%20I%20noticed%20a%20lot%20of%20%3C%2FP%3E%3CP%3E%20tags%20in%20the%20body%2C%20which%20actually%20stands%20for%20Paragraph%2C%20so%20outlook%20is%20doing%20what%20you're%20telling%20it%20to%20do.%26nbsp%3B%20The%20last%202%20sections%20also%20have%20the%20font%20size%20and%20family%20specified%2C%20and%20they're%20both%20the%20same%2C%20so%20those%20two%20lines%20will%20be%20Calibri%20%2F%2011%20regardless%20of%20what%20your%20default%20font%20is%20for%20outlook.%26nbsp%3B%20That%20being%20said%2C%20you%20can%20change%20the%20body%20line%20to%20this%20and%20I%20think%20it%20should%20correct%20your%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EobjOutlookMsg.HTMLBody%20%3D%20%22%3C%2FP%3E%3CP%3EPlease%20find%20attached%20a%20report%20detailing%20invoices%20in%20workflow%20which%20need%20completed%20within%201%20day%20to%20avoid%20breaching%20the%20Department's%2010%20day%20Prompt%20Payment%20target.%3C%2FP%3E%3CP%3EFor%20each%20payment%20that%20breaches%20this%20target%20an%20explanation%20is%20required%20from%20the%20Approving%20Officer%20on%20the%20monthly%20exception%20report.%3C%2FP%3E%3CP%3EI%20would%20be%20grateful%20if%20you%20could%20complete%20the%20required%20actions%20within%20the%20deadlines%20to%20help%20the%20Department%20improve%20its%20overall%20statistics.%3CB%3E%3C%2FB%3E%3C%2FP%3E%3CP%20style%3D%22font-size%3A11pt%3Bfont-family%3ACalibri%22%3EIssued%20on%20behalf%20of%20DFI%20Financial%20Systems%20Branch%3CBR%20%2F%3EEmail%3A%20FinancialSystems%40infrastructure-ni.gov.uk%3C%2FP%3E%22%3CP%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies
best response confirmed by Mark Lee (Occasional Contributor)
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>"

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