Solved

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

Posted on 2009-07-06
13
355 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,
0
Comment
Question by:gahute
  • 6
  • 6
13 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24795440
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.
0
 

Author Comment

by:gahute
ID: 24812612
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,
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24814790
Yes, it's for Outlook 2003 also.
0
 

Author Comment

by:gahute
ID: 24914708
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,
0
 

Author Comment

by:gahute
ID: 24919452
BlueDevilFan,

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

Thanks,
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24921182
This code should do it.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of
5.  Outlook's VB Editor window
6.  Edit the code as needed.  I included comment lines wherever something needs to or can change
7.  Click the diskette icon on the toolbar to save the changes
8.  Close the VB Editor
9.  Click Tools > Macro > Security
10. Set the Security Level to Medium
11. Close Outlook
12. Start Outlook
13. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.
14. Sign the code
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.Name = "My List Name" Then

                If olkRcp.Type <> olBCC Then

                    Cancel = True

                    Exit For

                End If

            End If

        Next

    End If

End Sub

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:gahute
ID: 24924238
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
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24928128
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.
0
 

Author Comment

by:gahute
ID: 24929801
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,
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24930766
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

0
 

Author Closing Comment

by:gahute
ID: 31600449
Thank you very much BlueDevilFan!

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

Thanks again!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24934155
You're welcome.  Glad I could help.
0
 

Expert Comment

by:sullisnyc44
ID: 34485210
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?
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Find out what you should include to make the best professional email signature for your organization.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

919 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

22 Experts available now in Live!

Get 1:1 Help Now