Outlook subject rule : populate from excel spreadsheet

[vadius]

Hi all,

I am trying to create a macro that filters the incoming mails and move them to a specific folder if the subject contains some words (one out of around 30)
The list of words is dynamic and can change every week.
Is that possible to create such a macro, which populate the rule with the words to consider in the subject from a list in an excel spreadsheet ?

Thanks

[JP2112]

In your event handler, open the workbook and read the range into an array. Then check the subject against the array to see if there's a match. Did you need specific help doing that?

[vadius]

Is that a joke ?

I think yiu have not understood the problem.

In the rules wizard I can only add the word one by one, and then it's fixed.
so if the words to look for change automatically, the rule won't be updated automatically. That is why I speak about populating the list from an excel list....(which is updated automatically)

[JP2112]

You said "I am trying to create a macro" so I assumed you were doing this in VBA. I skipped the part about populating the rule.

Your Outlook version?

[vadius]

ok no worries . I am running outlook 2007 . Below what I managed to arrange fron a code I found up to now.. But I don't know how to make the macro read into the closed excel file "CAC40.xls" and get the text values in the cells within the array "A1:A40"

Thanks

Sub CreateRule()


Dim colRules As Outlook.Rules
Dim oRule As Outlook.Rule
Dim colRuleActions As Outlook.RuleActions
Dim oMoveRuleAction As Outlook.MoveOrCopyRuleAction
Dim oFromCondition As Outlook.ToOrFromRuleCondition
Dim oExceptSubject As Outlook.TextRuleCondition
Dim oInbox As Outlook.Folder
Dim oMoveTarget As Outlook.Folder

'Specify target folder for rule move action
Set oInbox = Application.Session.GetDefaultFolder(olFolderInbox)
'Assume that target folder already exists
Set oMoveTarget = oInbox.Folders("Test")

'Get Rules from Session.DefaultStore object
Set colRules = Application.Session.DefaultStore.GetRules()

'Create the rule by adding a Receive Rule to Rules collection
Set oRule = colRules.Create("Test's rule", olRuleReceive)

'Specify the action in a MoveOrCopyRuleAction object
'Action is to move the message to the target folder
Set oMoveRuleAction = oRule.Actions.MoveToFolder
With oMoveRuleAction
.Enabled = True
.Folder = oMoveTarget
End With

'Specify the exception condition for the subject in a TextRuleCondition object
'Exception condition is if the subject contains "fun" or "chat"
Set oConditionSubject = oRule.Conditions.Subject
With oConditionSubject
.Enabled = True
.Text = Array(LOOK INTO CAC40.XLS)
End With

'Update the server and display progress dialog
colRules.Save
End Sub

[JP2112]

You need to instantiate Excel, open the workbook and read the range into an array. Something like:


Dim xl As Object
Dim xlwkbk As Object
Dim xlwksht As Object
Dim xlrange As Object
Set xl = CreateObject("Excel.Application")
Set xlwkbk = xl.Workbooks.Open("CAC40.xls")
Set xlwksht = xlwkbk.Sheets(1) ' I assume sheet 1
Set xlrange = xlwksht.Range("A1:A40")

With oConditionSubject
.Enabled = True
.Text = xlrange.Value ' insert values
End With

[vadius]

Hey

Thanks for your answer.
I have a problem with the end of the code . With "text" I should provide here an array of text value. I got the run-time error "Array lower bound must be zero." . I tried to specify Base Option 1 at the beginning but it's recognized.

Thanks