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

Status
Not open for further replies.

Duncan

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
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:

Code:
    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:

Code:
    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?
 

Duncan

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
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

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
Also worth noting - if I change the way the string is put together to the following:

Code:
    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

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
Sorry didn't know about 2 minute limit to edit and forgot to explain the following:

Dim SubjectCondition As Outlook.TextRuleCondition
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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
Code:
array("1","2")
is the same as this
Code:
split("1,2",",")
 

Duncan

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
Thanks Michael - I'll have a play around with it this morning :)
 

Duncan

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
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 :)

Code:
' 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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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:
Code:
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:
Code:
arr(i)=rs1![Keyword] & ""
 

Duncan

Member
Outlook version
Outlook 2013 32 bit
Email Account
POP3
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
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
A Apply Selected Emails to outlook rules and Run Rules Using Outlook 5
Wayne Outlook locks up when opening "Manage Rules & Alerts" Using Outlook 7
T "Words In Recipient's Address" Rule Causes Outlook To Stop Processing Rules Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
K Outlook Rules: Move a Copy Using Outlook 4
soadfan Outlook rules look up display name only Using Outlook 4
Justo Horrillo Issue with rules in Outlook 2010 Using Outlook 4
J Outlook Rules - Changing auto-submit address in multiple rules, according to rule name Outlook VBA and Custom Forms 0
EaglePI Outlook 2010 need help with rules Using Outlook 0
D Outlook Rules Using Outlook 1
Gunny NFOhiway Outlook PST file and Rules Using Outlook 1
M Outlook 2016 Rules Not Working Automatically Using Outlook 5
oliv- How to Run a Script IN AN ADDIN with Outlook's Rules and Alerts Outlook VBA and Custom Forms 2
Q Outlook 2016\365 export specific rules to import in another system Exchange Server Administration 1
A outlook 2007 how to chcek all rules ? Outlook VBA and Custom Forms 2
J Outlook Rules not show Master Category List Using Outlook.com accounts in Outlook 4
R Outlook rules Using Outlook 3
M Outlook 2007 IMAP Account Rules Issues Using Outlook 3
A Setting RULES with more than one condition in MS OUTLOOK Using Outlook 6
J Outlook rules and alternatives Using Outlook 2
Rupert Dragwater How to delete old rules and create new in Outlook 2013 Using Outlook 12
H Using Outlook Rules to search for NewLines in message body Using Outlook 1
R Outlook - alternatives to Rules / Junk? Using Outlook 5
R Outlook 2013 - IMAP & Rules Broken Using Outlook 1
D Trying to repair Outlook rules Using Outlook 5
J Outlook 2007 Rules & VBA: How to run a script on a report message (ReportItem) Using Outlook 14
V Importing Rules in Outlook 2013 Using Outlook 2
T Adding text to forwarding rules in Outlook 2010 Using Outlook 1
T Adding tex to forwarding rules in Outlook 2010 Exchange Server Administration 1
D Outlook 2010 Rules: fwd meeting invitation results in winmail.dat not .ics Using Outlook 4
M Outlook 2010 IMAP rules occasionaly stop working Using Outlook 4
K Smartphones, Outlook and Rules Using Outlook 0
W Fwd Outlook incomming messages to usb port after Rules and alerts applied Using Outlook 0
P Outlook 2013 rules -- not working reliably -- moving messages to wrong boxes Using Outlook 0
J Outlook 2013 - Rules and Alerts for POP3/IMAP Accounts Using Outlook 1
S Outlook 2010: Message Rules come back next time I restart Outlook Using Outlook 0
D Unable to transfer rules from one Outlook computer to another Using Outlook 5
R Outlook 2013 Rules not working Using Outlook 1
S Outlook 2013 - All rules simply delete the email Using Outlook 1
M Rules in Outlook 2013 Exchange Server Administration 9
K Outlook Cached Mode - can't create rules to move email to another mailbox Using Outlook 2
J Outlook rules help Using Outlook 1
D Outlook Rules Using Outlook 1
J Outlook - rules and Alerts Using Outlook 1
V Outlook 2010 non working rules Using Outlook 1
M Rules & Alerts not appearing in the File Tab (outlook 2010) Using Outlook 1
S Outlook 2010, need script for rules & Warnings Using Outlook 3
J Outlook - rules and Alerts - from group Using Outlook 3
D Rules Wizard Outlook 2010 limitations Using Outlook 1
N Outlook Rules - is this possible? Using Outlook 3

Similar threads

Top