Solved

Outlook Conditional Formatting for BCC

Posted on 2010-08-25
24
1,822 Views
1 Endorsement
Last Modified: 2012-05-10
I use Outlook's conditional formatting to highlight messages for a number of different criteria.  Recently, i responded to a message that i was BCC'd on accidentally.  If i go to the Advanced Criteria filter and select Field, BCC, and contains my name (friendly Exchange name or email address), the message does not get highlighted.  I have seen others setup the rule to be where the To and the CC lines do NOT contain your Exchange name/email, the problem is that Distribution lists are included in this.  So i need to get the BCC criteria exactly, does anyone know what to do or what i am overlooking to make this work?

1
Comment
Question by:zeotech
  • 11
  • 9
  • 4
24 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 33527003
Hi, zeotech.

A BCC rule can only work on messages you've sent since received messages do not contain the list of addresses the message was BCC'd to.  You may be able to achieve your goal though by using a reverse approach.  Create a rule with a condition of you are not in the To or CC lines.  If you aren't in either of those, then you were either BCC'd or a member of a distribution list that the message was copied to.
0
 
LVL 6

Author Comment

by:zeotech
ID: 33527566
That's the issue I'm having, distinguishing between distribution lists and BCC'd message. Anyway to identify distribution lits without being explicit?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33529481
Sorry, I don't know how I missed that.  I'm not aware of any built-in means of detecting that a message was sent to a distribution list without specifying list names.  The only solution I can think of, and I'm not 100% sure that this is possible, is to write a macro that expands and checks each list a message is addressed to looking for your name/address.  If it comes up empty, then it'd assign the message a category of "BCC" (or something like that).  The conditional rule would then check for the category.  Nested lists are the potential problem here.  A list within a list within a list, etc.  I'm willing to give it a go if you'd like to try this approach.  Outlook 2003 will be a problem since it includes security features that make accessing address fields difficult.  
0
 
LVL 6

Author Comment

by:zeotech
ID: 33534530
I would certainly be interest in a script that could accomplish this.  I am very surprised MS has not built in this ability since replying to BCC messages can be quite the faux pas.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 33569856
Sorry to be slow.  Here's the script.  Follow these instructions to add it to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor
10. Create a rule that fires for all inbound items (i.e. a rule without a condition)
11. Set the rule's action to "run a script" and select this script as the one to run

The script checks to see if your address appears in the To or CC fields or if you are a member of any distribution list the message is addressed to.  If your address does not appear in any of those places, then it assumes you were BCC'd and adds a category called BCC.  You can then create a conditional rule that checks for a category of BCC and color codes the item if that category is found.

Two notes.

1.  The code only checks a top-level distribution list.  It does not test nested lists.
2.  I did not test the code rigorously.  I tested against a couple of messages, one in which I was a member of a DL the message was addressed to and another where I was a regular addressee.  You should test it for awhile before depending on the results.
Sub BCCcheck(Item As Outlook.MailItem)
    Dim olkRecipient As Outlook.Recipient, olkAE As Outlook.AddressEntry, bolBCC As Boolean
    bolBCC = True
    For Each olkRecipient In Item.Recipients
        Select Case olkRecipient.AddressEntry.DisplayType
            Case olDistList, olPrivateDistList
                For Each olkAE In olkRecipient.AddressEntry.Members
                    If olkAE.Address = Session.CurrentUser.Address Then
                        bolBCC = False
                        Exit For
                    End If
                Next
                If Not bolBCC Then
                    Exit For
                End If
            Case Else
                If olkRecipient.Address = Session.CurrentUser.Address Then
                    bolBCC = False
                    Exit For
                End If
        End Select
    Next
    If bolBCC Then
        If Len(Item.Categories) = 0 Then
            Item.Categories = "BCC"
        Else
            Item.Categories = Item.Categories & ",BCC"
        End If
        Item.Save
    End If
    Set olkRecipient = Nothing
End Sub

Open in new window

0
 
LVL 6

Author Comment

by:zeotech
ID: 33570614
This worked!  A few notes as i am using Outlook 2010.

The developer tab is not enabled by default, here is how you enable it:
   1.      On the File tab, choose Options to open the Outlook Options dialog box.
   2.      Click Customize Ribbon on the left side of the dialog box.
   3.      Under Choose commands from on the left side of the dialog box, select Popular Commands.
   4.      Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.
   5.     Click OK.

When applying the Conditional Formatting rules, you cannot select the BCC category from the More Choices tab dropdown list.  Instead, go to the Advanced tab, select Field as Categories and the condition is "is (exactly)" then BCC.

This is going to be quite helpful for me!  Fantastic workaround BlueDevilFan! I wish MS would implement this as a MailTip in Office/Exchange 2010! I wish i could give you more than 500 points for this solution, i am really pleased.
0
 
LVL 6

Author Closing Comment

by:zeotech
ID: 33570629
Unbelievable. BlueDevilFan has gone above and beyond!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33571707
Thanks, zeotech!  Glad I could help out.
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34127429
I tried this but it is categorizing all the emails as BCC, even the ones in which my address is in To field. The conditional formatting does work with this method, but it is formatting each and every email as per the rule. Can you assist me to resolve this. Email to me categorized as BCC
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34128666
Hi, DevendraNaidu.

Are you sure the addresses match?  The code keys on the address matching the address returned by

    Session.CurrentUser.Address

Let's start by checking that.  Add the macro below to what you already have.  Run it and see if it returns the same address shown in the screenshot.
Sub TestMyAddress()
    Msgbox Session.CurrentUser.Address
End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34129975
Hi BlueDevilFan,

I tried out the same code on another POP3 mail account and it works perfectly as expected. My mailbox is on Exchange and I believe that's where the catch is. Do we need to modify the code to suite Exchange mailbox?
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34130392
Session.CurrentUser.Address
Hi BlueDevilFan,

Plese find attached the output of the Macro as suggested by you. Can you please guide me on the modification to be done in the code to get the desired result of categorization of mails in which I am BCC'd. Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 76

Expert Comment

by:David Lee
ID: 34131969
Change Session.CurrentUser.Address on lines 8 and 17 to the value you see in that dialog box.  Be sure to enclose the value in quotes.
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34134380
Tried it. Typed in the output as is but still no success. Maybe I may be missing out some space or adding an extra space. Also tried replacing with the email id, still no success. Require your assistance please. I have attached the revised code below.

 
Sub BCCcheck(Item As Outlook.MailItem)

    Dim olkRecipient As Outlook.Recipient, olkAE As Outlook.AddressEntry, bolBCC As Boolean

    bolBCC = True

    For Each olkRecipient In Item.Recipients

        Select Case olkRecipient.AddressEntry.DisplayType

            Case olDistList, olPrivateDistList

                For Each olkAE In olkRecipient.AddressEntry.Members

                    If olkAE.Address = "/o=Mantri Developers/ou=Exchange Administrative Group(FYDIBOHF23SPDLT)/cn=Recipients/cn=Devendra" Then

                        bolBCC = False

                        Exit For

                    End If

                Next

                If Not bolBCC Then

                    Exit For

                End If

            Case Else

                If olkRecipient.Address = "/o=Mantri Developers/ou=Exchange Administrative Group(FYDIBOHF23SPDLT)/cn=Recipients/cn=Devendra" Then

                    bolBCC = False

                    Exit For

                End If

        End Select

    Next

    If bolBCC Then

        If Len(Item.Categories) = 0 Then

            Item.Categories = "BCC"

        Else

            Item.Categories = Item.Categories & ",BCC"

        End If

        Item.Save

    End If

    Set olkRecipient = Nothing

End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 34134998
Try changing both of those lines to your SMTP email address.
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34136022
Tried that already. Does not work.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34136044
One of them has to work.  Did you match the case?
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 34146892
Trust me, I have tried all as per your advice. Still no luck. Used the SMTP Address, Used the Session.CurrentUser.Address, Session.CurrentUser.AddressEntry, Used the X.400 name..

Any more suggestions?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34153629
I believe you.  Can you save a test message to a .msg file and upload it here so I can test this?
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 35374041
Hi BlueDevil Fan,

I have attached 2 email messages. One which is marked to me as BCC and the other which is addressed to me. Can you check and let me know where I am going wrong. Thanks.
Test.msg
Mobile-Phone.msg
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35511415
The address isn't matching because the case is different.  In the message the address is in all uppercase while the IF statement is looking for mixed case.  

Change line #8 from

    If olkAE.Address = "/o=Mantri Developers/ou=Exchange Administrative Group(FYDIBOHF23SPDLT)/cn=Recipients/cn=Devendra" Then

to

    If LCase(olkAE.Address) = "/o=mantri developers/ou=exchange administrative group (fydibohf23spdlt)/cn=recipients/cn=devendra" Then

Change line #17 from

    If olkRecipient.Address = "/o=Mantri Developers/ou=Exchange Administrative Group(FYDIBOHF23SPDLT)/cn=Recipients/cn=Devendra" Then

to

    If LCase(olkRecipient.Address) = "/o=mantri developers/ou=exchange administrative group (fydibohf23spdlt)/cn=recipients/cn=devendra" Then

This will force both address checks to lowercase.
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 35710903
Thanks BlueDevilFan. Let me try this and revert.
0
 
LVL 2

Expert Comment

by:DevendraNaidu
ID: 36542176
Hi BlueDevilFan,

The suggested changes work like a breeze. The only issue is that the Rule does not work if the email is received in the Junk Folder. Works well when the message arrives in Inbox though. How could we resolve this.

Thanks in advance. U r gr8!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 36561322
Thanks, DevendraNaidu!

Junk email checking takes place before rules are run, so junk items are never processed by rules.  The only solution would be to add code that monitors the junk item folder for new items.  When one arrives it'd perform the BCC check on it.  Why would you want to check junk items though?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now