CODEWITHSUNDEEP
excelvbaemailoutlookworkflow
Ben Bennett
Ben Bennett

Reputation: 31

VBA script to add categories to Outlook emails if string from email matches a string in spreadsheet?

My department’s shared Outlook mailbox (0ffice 365) gets a TON of emails daily about different projects. The problem is that depending on the email sender the project gets referenced in different ways. The projects are university research projects and have at least 5 different names or alphanumeric numbers associated with them depending on the stage they are in.

My goal is to have VBA first cycle through the shared inbox and sent items and add a category tag to each email so that my team can find and follow up on all the different threads. Second, VBA should then add a category to new inbound and sent messages.

The key here is for VBA to search the TO:, FROM:, SUBJECT, and MESSAGE fields for string values that will identify which project the email refers to. The VBA should use as its search parameters data in our master projects spreadsheet. The master sheet can either be stored in a Sharepoint doc library or a OneDrive folder.

For instance, if an email comes in that includes the grant number in the subject line but doesn’t reference to the faculty member in charge of the project. VBA would find the grant number in the Excel worksheet and assign a tag with the faculty member’s name (also a field in the master project worksheet).

SUMMARY Search emails (from, to, cc, subject, message) for values using a master Excel spreadsheet (Principle Investigator, Project Number, Award Number, Grant Number, etc.) for search parameters and then assign a category (that may or may not already be created in Outlook) to each email in the inbox and sent folder of a 0365 shared mailbox.

Other details that usually get asked about: All users are on Windows 10 using Outlook 365 accounts (desktop apps). The master file is a .xlsx file that is exported from our Oracle OBIEE database. I have no access to connect to Oracle data directly.

Thank you to this awesome community in advance.

Upvotes: 0

Views: 896

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

It seems you are interested in using the AdvancedSearch method of the Application class. The key benefits of using the AdvancedSearch method in Outlook are:

  • The search is performed in another thread. You don’t need to run another thread manually since the AdvancedSearch method runs it automatically in the background.
  • Possibility to search for any item types: mail, appointment, calendar, notes etc. in any location, i.e. beyond the scope of a certain folder. The Restrict and Find/FindNext methods can be applied to a particular Items collection (see the Items property of the Folder class in Outlook).
  • Full support for DASL queries (custom properties can be used for searching too). You can read more about this in the Filtering article in MSDN. To improve the search performance, Instant Search keywords can be used if Instant Search is enabled for the store (see the IsInstantSearchEnabled property of the Store class).
  • You can stop the search process at any moment using the Stop method of the Search class.

Read more about this method in the Advanced search in Outlook programmatically: C#, VB.NET article.

Also you may find the Find/FindNext or Restrict methods of the Items class. The following articles describe them in depth:

Use the MailItem.Categories property which returns or sets a string representing the categories assigned to the Outlook item. Categories is a delimited string of category names that have been assigned to an Outlook item. This property uses the character specified in the value name, sList, under HKEY_CURRENT_USER\Control Panel\International in the Windows registry, as the delimiter for multiple categories. To convert the string of category names to an array of category names, use the Microsoft Visual Basic function Split.

The Application.NewMailEx event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem, MeetingItem, or SharingItem. The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance.

Upvotes: 0

Related Questions