Outlook 2010 VBA script, how to setup the incoming new emails auto categories(Color) with 5000 body keywords ?

how should i setup or design the VBA code wit the condition as below ?

I would need all new incoming emails (doesn't bother who is the sender) , if detected keyword (i got 5000 server name) in email body, auto categories as x(color) or y (color).
Should the code need to pointing to Excel/txt file or all 5000 keyword should import into the code ?

\\FYI, the 5000 keywords is unique.
Sackz LimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Unless the 5000 words change a lot, I would load them in when Outlook is started, into a global dictionary object.  Then as each email comes in you can easily check against that list.  That being said, you still have to iterate 5000 times checking for each in the email body.  That could be slow, so I have to ask the question, can the server name be "found" in the body and then a single check to the dictionary be done?  That would require there to be some consistent trigger phrase or word(s) around the server name all the time.  Like

SERVER: xxxxxx

Then you could search for "SERVER:", and extract whatever the server name is following it, then check that against the dictionary.

It you can't do it this way, and have to loop over 5000 items, then a simple array would probably be better than a dictionary.  Less overhead.

What additional help do you need on this one, are you familiar with VBA code, and how to set the categories, and how to create a VBA procedure to be invoked by a Rule, or need help there as well.  If a dictionary can work (really just a keyed "table" of key=data pairs) do you need help / examples for that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sackz LimAuthor Commented:
Thank you so much, Bill. You are wider my thought in dictionary methods.
It`s look alike , global dictionary is more suitable.
Yes,the question you ask,there`s fix character like Server ="xxx" will show in the email.
Now, how i gonna input 5000 keyword as directory.?
Bill PrewIT / Software Engineering ConsultantCommented:
Here is some example code, it will need adjusting to fit your needs.

LoadKeywords() is a procedure that will load keywords into a global dictionary from a text file.  Notice that the full path to the keyword text file needs to be passed to this procedure as a parm.

FindKeyword() is a function that will look for a keyword (passed in as a parm) and return TRUE or FALSE indicating if the keyword was found.

Test() is a small procedure I used here for testing that loads the dictionary from a text file (for my test the text file contained lines of "one", "two", and "three").  It then looks for a keyword that was in the file, and one that wasn't and displays the result in a message box.  I tested here and it worked as expected.

You may want to hook into the Outlook application startup to load the dictionary one time when Outlook starts.

Option Explicit

Global dicKeywords As Object

Sub Test()
    ' Load text file into dictionary
    LoadKeywords "B:\EE\EE29078688\keywords.txt"
    ' Look for a word in the dictionary
    MsgBox "Keyword 'two' was found? " & FindKeyword("two")
    ' Look for a word not in the dictionary
    MsgBox "Keyword 'four' was found? " & FindKeyword("four")
End Sub

Sub LoadKeywords(strFile As String)
   Dim strKeyword As String
   Dim intFreeFile As String
   ' Create a dictionary for Keyword data (case insensitive key matching)
   Set dicKeywords = CreateObject("Scripting.Dictionary")
   dicKeywords.CompareMode = vbTextCompare
   ' Open Keyword data file for reading
   intFreeFile = FreeFile
   Open strFile For Input As #intFreeFile

   ' Load Keyword data, add each record to the dictionary
   Do Until EOF(intFreeFile)
      Line Input #intFreeFile, strKeyword
      dicKeywords.Add strKeyword, 0

   ' Close Keyword data file
   Close #intFreeFile
End Sub

Function FindKeyword(strWord As String) As Boolean
    ' If word in dictionary return True, otherwise False
    If dicKeywords.Exists(strWord) Then
        FindKeyword = True
        FindKeyword = False
    End If
End Function

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.