Solved

Outlook Conditional Formatting for BCC

Posted on 2010-08-25
24
1,965 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

763 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