We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Stop email sending if a specified distribution list is not in the BCC field.

gahute
gahute asked
on
Medium Priority
384 Views
Last Modified: 2012-05-07
Hi experts,

We use Outlook 2003 and Win XP Pro.

We use to send an email to our buyers list to inform them when we have new assets for sale. We don't want them to see each other in this list (modified weekly) and that's why we put the distribution list within BCC field. Usually, this sending is done by our secretary.

Unfortunately, it happened that she put the distribution list in the CC field rather than in the BCC field. For her, I created a draft where I put the distribution list in the BCC field. I asked her to use this draft as a template by copying it before she send the new copy correctly. It's not mistake-proof as she made a mistake again today (don't ask me how please).

How can I force the email to be sent using BBC when this distribution list is the recipient in any field?

I know Excel VBA and use it oftenly, but I'm not good with Outlook VBA (never used it up to now) so your explanations will need to be very clear.

Thanks,
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi, gahute.

I can make the check with a bit of VBA, but that creates another problem.  Outlook security prohibits VBA from accessing any field that contains an address.  When a script tries to access a field containing an address Outlook pop up a dialog-box warning the user that an application is accessing their mailbox and asking for their permission to allow it to continue.  Outlook security cannto be turned off, but there are ways to work around it.

1.  Sign the code.  Here's a link to instructions on doing that: http://msdn.microsoft.com/en-us/library/aa155754(office.10).aspx
2.  Use ClickYes (http://www.contextmagic.com/express-clickyes/), a small utility that'll click the Yes button for you.  It creates a security hole though, since a virus could start sending messages and ClickYes would click the Yes button for it too.  
3.  Use Redemption (http://www.dimastr.com), a COM library that enables code to safely bypass Outlook security.

If this isn't a problem, then I can provide the code and instructions on how to use it.

Author

Commented:
Hi BlueDevilFan,

Your first proposition looks the best one but I have a question before I check it in details. On your link, it says "Outlook 2000", is it also ok for "Outlook 2003"? If yes, I'll check that in details and get back to you.

Thanks,
CERTIFIED EXPERT
Top Expert 2010

Commented:
Yes, it's for Outlook 2003 also.

Author

Commented:
Hi BlueDevilFan,

Sorry for the delay, I was on holidays. But I will check it in the next days. I'll try to sign the code and get back to you.

Thanks,

Author

Commented:
BlueDevilFan,

Your first point looks correct. I signed my project. Now what are the next steps?

Thanks,
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hello BlueDevilFan,

Since this program will be installed on our secretary's computer, I'd prefer to set security level (step #10) to "High" because I don't want her to choose if macros should be executed or not on its computer...

Is it correct to do so?

√Čric
CERTIFIED EXPERT
Top Expert 2010

Commented:
I'm not sure that the code will run.  I think High disables all code.  No harm in trying it out and seeing what happens.

Author

Commented:
BlueDevilFan,

It seems to work fine with security level set to high.

Would it be possible to change the code in order to verify that the email is sent to any distribution list rather than to a specific named distribution list?
I mean change "if sent to a specific list" ==> "if sent to any distribution list"...

Thanks a lot,
CERTIFIED EXPERT
Top Expert 2010

Commented:
I've modified the code (below) to check for any distribution list.  Replace the code above with the version below.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim olkRcp As Outlook.Recipient
    If Item.Class = olMail Then
        For Each olkRcp In Item.Recipients
            'Edit the list name on the next line'
            If olkRcp.AddressEntry.AddressEntryUserType = olExchangeDistributionListAddressEntry Or olOutlookDistributionListAddressEntry Then
                Cancel = True
                Exit For
            End If
        Next
    End If
End Sub

Open in new window

Author

Commented:
Thank you very much BlueDevilFan!

Your solution is exactly what I was looking for. It was clear and easy to understand.

Thanks again!
CERTIFIED EXPERT
Top Expert 2010

Commented:
You're welcome.  Glad I could help.
I would actually like to implement this in Outlook 2007 for all email sent FROM a particular account. Can I do that? Or at least prompt the user?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.