VBA macro to highlight selected text in current email message

Question

I am trying to create a VBA macro for outlook 2013 that will take the selected text in the e-mail message that I am currently writing (which is in HTML format) and set the font size/color/boldness/highlighting.

My macro has two if/then blocks. One block is for Outlook 2003, and gives the desired outcome for all four of the text characteristics. However, after 2003, Outlook uses the Word EditorType for HTML e-mails, and thus I need a different VBA block with different syntax to the change the font of the selected text. The VBA in my 2013 block works correctly for changing the boldness/point size, but it does not apply highlighting to the text. Instead, the command for highlighting the text (rng.Range.HighlightColorIndex = wdYellow) is causing the background color of the selection window to change to clear (so that the text no longer appears to be selected, even though it is still really selected), but no highlighting is applied to the selected text.

When highlighting the text did not work, I tried something else. I tried using the vba command for setting the background to yellow (which has an equivalent visual effect, when manually applied without vba). rng.Shading.BackgroundPatternColor = wdColorYellow. But instead of turning the background yellow, the background changes to black.

Also the 2013 block does not cause the font color to change. Font color stays black despite the statement (rng.Font.Color = wdColorBlue)

Please advise me how I can set the highlight the selected text to yellow and set the color of the selected text to blue.

The full VBA macro is below.

 Sub ChangeSelectedFontBold14HiYellow()
 Dim msg As Outlook.MailItem
 Dim insp As Outlook.Inspector

 Set insp = Application.ActiveInspector

 If insp.CurrentItem.Class = olMail Then 
     Set msg = insp.CurrentItem

     If insp.EditorType = olEditorHTML Then ' outlook 2003
         Set hed = msg.GetInspector.HTMLEditor
         Set rng = hed.Selection.createRange
         rng.pasteHTML "<b><font style='color: blue; background: yellow; font-size: 14pt;'>" & rng.Text & "</font></b>"
     End If

     If insp.EditorType = olEditorWord Then ' outlook 2013
         Set hed = msg.GetInspector.WordEditor
         Set word = hed.Application
         Set rng = word.Selection
         rng.Font.Size = 14
         rng.Font.Color = wdColorBlue ' color does not change
         rng.Font.Bold = True
         ' rng.Shading.BackgroundPatternColor = wdColorYellow ' changes background color to black instead of yellow
         ' rng.HighlightColorIndex = wdYellow ' does not work  ' error 438 object doesn't support this property
         rng.Range.HighlightColorIndex = wdYellow ' does not work - changes the background to clear for the selection indicator color

     End If

 End If
 Set insp = Nothing
 Set rng = Nothing
 Set hed = Nothing
 Set msg = Nothing
 End Sub

Answer 1

You need to add a VBA Project reference to the Word object library, or define those constants such that Outlook can understand what the true values of wdColorBlue and wdYellow are.

When I did that, your code had the desired effect (but if you add a reference then you can't use Word as a variable name)

Here's what worked for me (more or less - I was at work when I tested, but not there now...) The Collapse part works fine in Word, so should work also in Outlook.

Sub ChangeSelectedFontBold14HiYellow()
 Dim msg As Outlook.MailItem
 Dim insp As Outlook.Inspector

 Set insp = Application.ActiveInspector

 If insp.CurrentItem.Class = olMail Then 
     Set msg = insp.CurrentItem

     If insp.EditorType = olEditorHTML Then ' outlook 2003
         Set hed = msg.GetInspector.HTMLEditor
         Set rng = hed.Selection.createRange
         rng.pasteHTML "<b><font style='color: blue; background: yellow; font-size: 14pt;'>" & rng.Text & "</font></b>"
     End If

     If insp.EditorType = olEditorWord Then ' outlook 2013
         Set hed = msg.GetInspector.WordEditor
         Set appWord = hed.Application
         Set rng = appWord.Selection
         rng.Font.Size = 14
         rng.Font.Color = wdColorBlue 
         rng.Font.Bold = True
         rng.Range.HighlightColorIndex = wdYellow

         rng.Collapse Direction:=wdCollapseEnd 'UNTESTED, but something like this...
     End If

 End If

 Set appWord = Nothing
 Set insp = Nothing
 Set rng = Nothing
 Set hed = Nothing
 Set msg = Nothing

 End Sub