VBA can be used for wonderful work simplifications in the Microsoft Office world and one can only hope that Microsoft never abolishes VBA. Let’s say you’ve created a VBA script to automatically generate an email or automatically reply to an email. Of course, in such a case, you want to format the e-mail a little according to your wishes and, for example, change the font size or font type. There are two options available here. This depends on whether you want your email to be formatted in HTML format or in rich text format.
Setting the font and font size using HTML
The script for creating a new email looks like this. Make sure that olFormatPlain is not assigned to this property, but olFormatHTML. In this case, the mail is formatted using HTML, which provides maximum freedom in the design of the text.
Public Sub NewMailWithSpecificFontSize()
Dim olItem As Outlook.MailItem
Set olItem = Application.CreateItem(olMailItem)
With olItem
.to = "john.doe@my-domain.de"
.subject = "In urgent matter"
.BodyFormat = olFormatHTML
.HTMLBody = "<BODY style=font-size:20pt;font-family:Calibri><p>Hello,</p><p>I am contacting you on urgent matter XY!</p><p>Yours sincerely,<br>Frederike van Sanghausen</p></BODY>"
.display
End With
Set olItem = Nothing
End SubIn this case, you can add CSS styles to automatically change the font and font size. In the code example above, the font size within the HTML text was set to 20pt, which is quite large.
Important: The text must not be assigned to the Body property, otherwise you will see the HTML code in the email itself. The HTML code must be assigned to the HTMLBody property.
If you execute the macro, the email will look like this:
Adjust font and font size in rich text format
Outlook also offers the alternative option of formatting emails as rich text. Here you can also customise the font and font size as you wish. The VBA code for this looks like this:
Public Sub NewMailWithSpecificFontSize2()
Dim olItem As Outlook.MailItem
Dim wdApp As Object
Dim wdDoc As Object
Dim wdRange As Object
Set olItem = Application.CreateItem(olMailItem)
With olItem
.to = "john.doe@my-domain.de"
.subject = "In urgent matter"
.bodyFormat = olFormatRichText
.body = "Hello," & vbCrLf & vbCrLf & "I am contacting you on urgent matter XY!" & vbCrLf & vbCrLf & "Yours sincerely," & vbCrLf & "Frederike van Sanghausen"
Set wdApp = .GetInspector
Set wdDoc = wdApp.WordEditor
Set wdRange = wdDoc.Range
wdRange.WholeStory
With wdRange
.Font.Name = "Arial Rounded MT Bold"
.font.size = 16.5
End With
.display
End With
Set olItem = Nothing
End SubYou can see that you have to create a Word document object (wdDoc) to be able to apply the rich text formatting. Only then do you have access to the properties Font.Name and Font.Size. The email object (olItem) itself does not offer these properties.
The result then looks like the image below:
If you use the olFormatPlain as formatting, the email is formatted as a plain text document. In this case, it is not possible to format the text of the email. But this can also be sufficient in some cases.
