Outlook Rules - How to use a variable in the subject condition

[Duncan]

Hi there, first post

I am trying to set up Outlook mail rules to move incoming mails into a folder based on certain keywords in the subject line.

The rules are being created in Access and passed across to Outlook - all works fine but I would like to be able to control the words to be searched for using a variable called "Keywords" rather than hard coding it.

For example, if the keywords being searched for are "Dog", "Cat", "Horse" then the following works (this is just part of the code so haven't posted the definitions of RuleIn, olRuleReceive, SubjectCondition etc):

This works:

    Set RuleIn = colRules.Create("Inbox", olRuleReceive)
    Set SubjectCondition = RuleIn.Conditions.BodyOrSubject
    With SubjectCondition
        .Enabled = True
        .Text = Array("Dog", "Cat", "Horse")
    End With

However - I would like the .Text = Array element of the SubjectCondition to be driven by a string variable called Keywords, which can be compiled into any format and is currently equal to "Dog", "Cat", "Horse"

This does not work:

    Set RuleIn = colRules.Create("Inbox", olRuleReceive)
    Set SubjectCondition = RuleIn.Conditions.BodyOrSubject
    With SubjectCondition
        .Enabled = True
        .Text = Array(Keywords)
    End With

In Outlook I get a rule which looks for ""Dog", "Cat", "Horse"" (note double quotes at start and end - i.e. the rule looks for the whole string and not the individual values in the array.

Where am I going wrong?

 

[Michael Bauer]

Is 'Keywords' a string or an array? If it's a string, you can split it into an array by the Split function.

 

[Duncan]

Hi Michael

Keywords is a concatenation of text values from a table, I have tried defining it as a Variant and as a String and get exactly the same error referred to above.

In both cases (Variant and String) its value is precisely "Dog", "Cat", "Horse" (including the quotation marks).

I think my issue is something to do with the formatting of the Keywords variable in .Text = Array(Keywords) and how this is being passed into the rule.

With the .Text = Array("Dog", "Cat", "Horse") alternative Outlook creates the rule as follows:

Apply this message after the rule arrives
with 'Dog' or 'Cat' or 'Horse' in the subject or body
move a copy to the Inbox folder

With the .Text = Array(Keywords) alternative Outlook creates the rule as follows:

Apply this message after the rule arrives
with "Dog", "Cat", "Horse" in the subject or body
move a copy to the Inbox folder

i.e. the rule is looking for a concatenation exactly equal to "Dog", "Cat", "Horse" without the 'or' condition

I hope this makes sense (!)

Many thanks

Duncan

 

[Duncan]

Also worth noting - if I change the way the string is put together to the following:

    Keywords = ""
    rs1.MoveFirst

    Do While Not rs1.EOF
    Keywords = Keywords & Chr(39) & rs1![Keyword] & Chr(39) & " or "
    rs1.MoveNext
    Loop

    Keywords = Left(Keywords, Len(Keywords) - 4) ' to remove the last " or "

Then this still does not work. I get a rule that looks as if it should work:

Apply this message after the rule arrives
with 'Dog' or 'Cat' or 'Horse' in the subject or body
move a copy to the Inbox folder

However, when I click on the hyperlink for 'Dog' or 'Cat' or 'Horse' then the Search Text definition contains the following:

"'Dog' or 'Cat' or 'Horse'" (enclosed in double quotes) i.e 1 condition

The correct requirement for the Search Text would be:

"Dog" or
"Cat" or
"Horse"

In essence, is there a way to create a rule using

With SubjectCondition
.Enabled = True
.Text = myvariablehere 'in some form or another
End With

Hope this helps!

 

[Duncan]

Sorry didn't know about 2 minute limit to edit and forgot to explain the following:

Dim SubjectCondition As Outlook.TextRuleCondition

 

[Michael Bauer]

Ok, so currently Keywords is a string. Remove the "or" from it, and use the Split function as mentioned to turn it into an array. This

array("1","2")

is the same as this

split("1,2",",")

 

[Duncan]

Thanks Michael - I'll have a play around with it this morning

 

[Duncan]

Michael - thanks so much - got there in the end as follows (probably a bit of unnecessary code in here but it worked...) :

In essence, with my database split, I can update my table 'Email Keywords' and trigger an update to the Outlook Rule from Access without having to go into the code and edit the list manually.

The database is multiuser - and the point of having the rules is to copy the emails into a folder from which they are imported into Access into an emails table, so the whole email history for all DB users can be filtered into forms relevant to particular records.

Thanks for all your help - I have only been programming in Access for a couple of months and have no training, so suspect I may fallen down on something obvious here...! Never mind, it works

' defined the String to return and the array to pass the Split into

    Dim KeywordString As String
    Dim KeywordArray() As String

'some of the bits in Access

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("SELECT * FROM [Email Keywords]") 'the name of the table from which the
                                                                 'Subject/Body conditions are taken
    KeywordString = ""

    rs1.MoveFirst
    Do While Not rs1.EOF
    KeywordString = KeywordString & rs1![Keyword] & ","
    rs1.MoveNext
    Loop

    KeywordString = Left(KeywordString, Len(KeywordString) - 1) ' remove the last ","
    KeywordArray = Split(KeywordString, ",") ' pass the result into the array
      
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing

' and later on in code passed to the rule condition as;

    Set RuleIn = colRules.Create("InboxDBFolder", olRuleReceive)
    Set SubjectCondition = RuleIn.Conditions.BodyOrSubject
    With SubjectCondition
        .Enabled = True
        .Text = KeywordArray     'now the array is driven by a variable!!
    End With

 

[Michael Bauer]

Looks good. Not important at all, just for the case you're interested: You could read the values from the recordset directly into the array:

Dim i&, arr() as string
...
if rs1.recordcount then
  redim arr(rs1.recordcount-1)
  do...
    arr(i)=rs1![Keyword]
    i=i+1
  loop
endif

And for the case rs1![Keyword] could have a NULL value, this would prevent that an error occurs:

arr(i)=rs1![Keyword] & ""

 

[Duncan]

Wow, much neater, thank you. Have subscribed to alerts for the vba element of this site, hope to be able to help someone else out one day. This was the first "brick wall" I've hit, so thanks again.

Currently having fun with read receipts not being mail items, but seem to be working through that ok.

All the best

Duncan