Conditional formatting by Field Criteria with VBA (e.g. From address contains) RRS feed

  • Question

  • I would like to have a Macro to use conditional formatting to highlight all emails in my inbox from a specific person.  

    I created the code below based on the example here:

    Sub HighlightByName() Dim objView As TableView Dim objRule As AutoFormatRule

    ' Input box to input the name to be used as the filter HighlightBy = InputBox("Name to highlight (from field)") ' Check if the current view is a table view. If Application.ActiveExplorer.CurrentView.ViewType = olTableView Then ' Obtain a TableView object reference to the current view. Set objView = Application.ActiveExplorer.CurrentView ' Create a new rule that displays any message with a ' the value of HighlightBy in the sender's email address in ' the formatting below.

    ' Remove current AutoFormatRule "HighlightByPerson" to avoid duplicates objView.AutoFormatRules.Remove ("HighlightByPerson") Set objRule = objView.AutoFormatRules.Add("HighlightByPerson") With objRule .Filter = """""" & _ " CI_PHRASEMATCH '" & HighlightBy & "'" ' From ' PR_SENDER_EMAIL_ADDRESS (Contains the message sender's e-mail address) Property tag: 0x0C1F001F With .Font .Color = olColorBlue End With End With ' Save and apply the table view. objView.Save objView.Apply End If End Sub

    The code works initially, however there are two strange things.

    1. When I navigate within Outlook away from the Inbox and come back, all my messages are blue.
    2. When I look into the View Settings, even before navigating away and when only the correct emails are blue, I cannot see any Condition being set within the Condition dialog box of the Conditional Formatting setting in View Settings.

    Can someone help me with 1., where I guess there's something I'm doing wrong with my code or where I'm putting it in the Visual Basic Editor in Outlook (I've created a Module)?

    For 2. I have absolutely no idea.  How can Outlook apply a conditional format without a conditional format shown?

    What I would like to have is that the VBA code above sets a Field criteria on the Advanced tab of the Condition Filter dialog box of the Conditional Formatting setting in View Settings, as shown in this image (where I set it manually).

    Finally, a few small problems that I would appreciate help with but can live without if the above are answered :) :

    • How can I check if the Format Rule exists before trying to remove it?
    • How can I disable the rule if I set the InputBox value to be blank, and re-enable it if I set a name?

    Thanks heaps!


    • Edited by N.2 Thursday, March 6, 2014 3:14 PM
    • Moved by Steve Fan Friday, March 7, 2014 5:41 AM coding related post
    Thursday, March 6, 2014 8:40 AM

All replies