Monday, October 14, 2013

Email Using VBA., Part II: Getting the Body Style right

In Email Using VBA., Part I: Keeping the Sig Alive I solved the problem of the disappearing signature.  As I was working on that issue I noticed the Font and Size of the text in the message was all wrong.  Here is the solution to that problem.

Because in my solution I use .HTMLBody instead of the plain text or RichText, I needed a CSS Style Sheet to control the body's format.

This meant  adding another parameter to the module, HTMLMsgStyle as String, and when I called it I used
    HTMLMsgStyle:="<html><head><style> p.msgstyle{font: " & cellFontSize & "pt " & cellFontName & "}</style></head>" 

The variables cellFontName and cellFontSize refer to specific cells (ranges) in the workbook.  The line for to add the message now reads

       .HTMLBody = HTMLMsgStyle & "<p class=""msgstyle"">" & Msg & "</p>" & EmailItem.HTMLBody