?
Solved

I need a custom Outlook Rule, programmed with VBA

Posted on 2009-04-22
16
Medium Priority
?
589 Views
Last Modified: 2012-08-13
I have an interesting email problem.  

First, I'm really sorry for the length of this question!  But it's a totally riveting read I swear&

So I'm managing the email for an office of 3 people.  Previously each person just had their own email address, and things were very simple.  jon@bleh.com, judy@bleh.com, and james@bleh.com.

But now there's a new requirement whereby all emails must be captured by a 3rd party's server.  The typical way to do this is to have all emails in and out be to and from the new email address opened with that 3rd party,  newemail@thirdparty.com.

But doing this would be a major inconvenience.  Changing all our business cards, informing all our clients, and most importantly losing our personal identities -- I don't particularly want to merge all 3 emails into one.  

So I've been thinking of ways to keep our current emails, but still have them pass through and be captured by the third party's server.

For emailing out this is surprisingly easy.  I changed the SMTP server info of the 3 old emails to the third parties's SMTP server.  Now sending emails from those address sends them through that server, and so is captured by it, but still feels and looks just like it's coming from the same old address.  Brilliant!

But for emails coming in I'm having some difficulties.  I set up email forwarding on my server, so that all emails addressed to the 3 old addresses are now forwarded to the new one.  So all incoming emails are now captured.  Good.

But my problem is now that everyone is now receiving everyone's emails, of course.  Jon is receiving all of Judy and James' emails, as well as his own, and they are receiving his and each others'.

I was thinking I'd remedy this with Outlook's Rules.  The original recipient information IS retained even though it is passed through the third party email.  So an email to Judy@bleh.com, then forwarded to newemail@thirdparty.com, then downloaded into Outlook, STILL has Judy@bleh.com listed as the recipient.  That's good and I been taking advantage of that by creating rules in each user's Outlook that says -- in Jon's Outlook, for example -- if Judy or James is listed as the recipient then delete the downloaded message.

But there's TWO issues with this:

1.If an email is addressed to multiple users, both Jon and Judy, for example, that rule will still delete it even though JON should be entitled to see it.  So really what I need is a rule that says, if Jon is NOT included then delete.  But this option isn't included in the list of rules in Outlook!   So, any thoughts on how to get around this?  I was thinking I could program a new rule in VBA, but don't know how to do this.

2.It's not really ideal to be downloading emails for EVERYONE and THEN deleting them, because they're still retained in the Deleted Items folder, and people can still read other people's emails.  A superior solution would of course be to apply the rules as they are being downloaded, and only download those that pass the test.  Is there a way to do this with the VBA code?



So basically what I'm asking for is VBA code -- unless someone can think of another alternative.

I have Outlook 2000 by the way, but could upgrade if absolutely necssary...
0
Comment
Question by:Ajw43022
  • 7
  • 7
  • 2
16 Comments
 
LVL 28

Expert Comment

by:peakpeak
ID: 24212507
Script code examples to build from here:
http://www.outlookcode.com (download Sues code examples)
http://www.slovaktech.com/code_samples.htm
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24213315
Hi, Ajw43022.

I can help you write a rule using VBA.  Are you saying that all inbound messages go to a single mailbox?  If so, then depending on where the original address information is, a VBA based rule could solve the problem.  There are a few hurdles to making this work.  First, you'll have to install Outlook on a computer and dedicate it to the task of running this rule.  VBA based rules only work if Outlook is up and running.  Second, Outlook 2000 has security built into it that prevents sending messages from code.  That security cannot be turned off.  Fortunately, there are ways to work around it, but they aren't all free.  Here's a summary of the possibilities

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 you still want to pursue this, then I'll be glad to help with the code.
0
 
LVL 28

Expert Comment

by:peakpeak
ID: 24213405
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Ajw43022
ID: 24213454
Hi BlueDevilFan, thanks for your response.

I'm aware Outlook 2000 will have to be up and running.  That's fine.

Bypassing security shouldn't be necessary, because I don't think I should have to send any messages from VBA.

The VBA is just for creating rules that will filter/organize INCOMING messages (not OUTGOING).

Here's, in detail, what I need the VBA code to do:


If mail is from Account = "Account1"  then

If mail is not TO or CC'd to "jon@bleh.com" then

Delete mail (delete it permanently -- I don't want it just moved to the Deleted Items folder, because then it'd still be readable)

Else move mail to "Folder1"



thanks!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24213488
"Bypassing security shouldn't be necessary, because I don't think I should have to send any messages from VBA."
Hmmm.  Maybe I'm not understanding how you envision this working.  From your description of the situation I thought the flow would be something like this.

1.  Mail arrives at central mailbox.
2.  Rule fires running the script.
3.  The script checks to see who the message should go to and forwards the message to those people.

Are you saying that the rule will run on each computer and simply delete the messages that aren't addresses to them?  
0
 

Author Comment

by:Ajw43022
ID: 24213542
>Are you saying that the rule will run on each computer and simply delete the messages that aren't >addresses to them?  

That's right!  It's as simple as that!  thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24213574
Ok.  That is simple.  Still not sure that this won't trigger Outlook security though.

Where do the original addresses appear in the messages?  
0
 

Author Comment

by:Ajw43022
ID: 24213630
The original addresses actually just appear in the TO/CC fields.  They are retained there despite the interim forwarding step.
0
 

Author Comment

by:Ajw43022
ID: 24222424
Hi, not to pester, but just wondering if you're working on this BlueDevilFan?  thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24223062
Yes, hadn't forgotten.  Just slow sometimes.  

Here's the code for doing this.  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.

Here's how this works.

1.  The code begins monitoring the Inbox watching for new items arriving.
2.  When an item arrives the code checks to see if the currently logged on user's name is in the To/Cc line.  If it isn't, then the code permanently deletes the item.

A couple of notes.

1.  I no longer have Outlook 2000, so I could not test this in the target environment.  
2.  I'm not clear on how the names appear in the To/Cc line, so this may not work.  The code is using the name of the person which is typically what appears in the address line.  If it's their address that appears, then I'll need to modify the code.
3.  Because there is no means of checking the BCC address line this code is going to delete all messages BCCd to the people.
Dim WithEvents olkInbox As Outlook.Items
 
Private Sub Application_Quit()
    Set olkInbox = Nothing
End Sub
 
Private Sub Application_Startup()
    Set olkInbox = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
End Sub
 
Private Sub olkInbox_ItemAdd(ByVal Item As Object)
    Dim strName As String, olkDeleted As Object
    strName = Outlook.Session.CurrentUser
    If (Not InStr(1, Item.To, strName)) And (Not InStr(1, Item.CC, strName)) Then
        Set olkDeleted = Item.Move(Outlook.Session.GetDefaultFolder(olFolderDeletedItems))
        Set olkDeleted = Outlook.Session.GetItemFromID(olkDeleted.EntryID)
        olkDeleted.Delete
    End If
    Set olkDeleted = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Ajw43022
ID: 24223330
Not slow at all!  Thanks, I'll test this out tomorrow.  Looks like it should work, but I'll get back to you.
thanks again.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24223377
You're welcome.
0
 

Author Comment

by:Ajw43022
ID: 24239594
OK, I've now had a chance to test this out and it works well.  Thanks again.

I'm not sure how to deal with the fact that Item.To is really just a label, not the actual email address, however.  Because this means that for a given user it could equal a whole variety of values, i.e.  billsmith@gmail.com, Bill Smith, William Smith, W Smith, etc.  I could try to create a case statement that covers all possibilities I can think of....

But it'd be much nicer if there was a way to just access the actual email address.  Is there a way to do that?   Or even better yet, a way to access the entire detailed Header info of the email?  I've tried Item.Header but that doesn't seem to work, not surprisingly.  I've looked, but I'm having trouble finding a guide to the syntax of "Item".
0
 
LVL 76

Accepted Solution

by:
David Lee earned 600 total points
ID: 24240033
"Is there a way to do that?"
Yes.  Doing it this way is going to trigger Outlook's built-in security.  That's because the test requires accessing a address field.  I've modified the code to compare addresses instead of names.
Dim WithEvents olkInbox As Outlook.Items
 
Private Sub Application_Quit()
    Set olkInbox = Nothing
End Sub
 
Private Sub Application_Startup()
    Set olkInbox = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
End Sub
 
Private Sub olkInbox_ItemAdd(ByVal Item As Object)
    Dim strName As String, olkDeleted As Object, olkRecipient As Outlook.Recipient, bolToMe As Boolean
    strName = Outlook.Session.CurrentUser.Address
    For Each olkRecipient In Item.Recipients
        If olkRecipient.Address = strName Then
            bolToMe = True
            Exit For
        End If
    Next
    If Not bolToMe Then
        Set olkDeleted = Item.Move(Outlook.Session.GetDefaultFolder(olFolderDeletedItems))
        Set olkDeleted = Outlook.Session.GetItemFromID(olkDeleted.EntryID)
        olkDeleted.Delete
    End If
    Set olkDeleted = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Ajw43022
ID: 24249050
Awesome!  You're the man!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24249115
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month17 days, 13 hours left to enroll

829 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