If you use e-mails, you have certainly received loads of junk in the past and will continue to get more in the future. There are many ways you can deal with junk mail such as using a specialized tools (antispam tools). Alternatively you can use the Rules and Alerts function of Outlook to filter unwelcome e-mail.
Rules have many uses. You could simply delete a message for good or upon arriving you could move it to another folder. Or you could forward it to your smartphone or any other e-mail address you like.
Figure 1: Outlook 2007 Rules and Alerts dialog box
Up until Office 2007, you could create rules for Outlook using VBA. The best you could do was to create a class that after being initialized, it would process e-mails upon arrival and get them sorted accordingly. The problem here was that we could received different object such as a task (which is not an e-mail object), which could generate an error.
Another issue with classes was that we would need to certify the code in order to avoid the security message Office displays when there is code. Also, accessing information such as "sender" would trigger Outlook's security message. With Rules, we can bypass all these issues:
Figure 2 : Outlook 2007 security is trigger when accessing e-mail information
Obviously, this warning is not what we want. We really want something being done behind the scenes with minimal intrusion in our workflow. We could use tools such as Redemption, but it would still require a lot from us in terms of coding. By creating a Rule we bypass all these issues.
The project that follows will create an Outlook Rule from Excel (this will trigger a security message from Outlook, but only when the code is run for the first time).
The first thing you need to do is to add a reference to Outlook's Object Library. You do so by opening the VBE window (press Alt+F11 to do so). The click on Tools --> References ... as shown in the image below:
Figure 3 : Adding Outlook Object Library reference to an Excel project
When you click on References... the references dialog box will open as shown below:
Figure 4 : Choosing the object library
After you've added the reference, we're ready to start writing our code.Add a new standard module to the project where the following code will be inserted:
Sub createOutlookRule()
Dim appOutlook As Outlook.Application
Dim olRules As Outlook.Rules
Dim myRule As Outlook.Rule
Dim moveToAction As Outlook.MoveOrCopyRuleAction
Dim fromAction As Outlook.ToOrFromRuleCondition
Dim myInbox As Outlook.Folder
Dim moveToFolder As Outlook.Folder
Set appOutlook = New Outlook.Application
Set myInbox = appOutlook.Session.GetDefaultFolder(olFolderInbox)
Set moveToFolder = myInbox.Folders("MyWorkFlow")
Set olRules = appOutlook.Session.DefaultStore.GetRules()
Set myRule = olRules.Create("My Test Rule", olRuleReceive)
Set fromAction = myRule.Conditions.From
' With the "from" action
With fromAction
' Enable the condition
.Enabled = True
' Add the e-mail sender
' Here, I use the user-friendly name in my address book
.Recipients.Add ("Robert Martim")
' Resolve the recipients so that we know they're valid
.Recipients.ResolveAll
End With
' Determine the action type (moveToFolder, in this case)
Set moveToAction = myRule.Actions.moveToFolder
' With the moveToAction
With moveToAction
' Enable the action
.Enabled = True
' Determines the destination folder
.Folder = moveToFolder
End With
' Save the rule in Outlook's Rules list
olRules.Save
End Sub
As pointed out, when you run this code for the first time you will get Outlook's security message. You should allow the access so that the code can do its job. After the code has done its trick you can open Outlook's Rules and Alerts dialog box and it will show the new rule as per image below:
Figure 5 : New rule added to Outlook 2007
CONCLUSION
In this short article you have learned how to work with some of the new objects of Outlook 2007 using Excel 2007 so that Outlook rules can be created. This example is applicable to other Office tools.