Counting Word Occurrences


I put this solution together for a reader named Rakesh. Rakesh asked for a solution that counts the number of times certain words occur in Outlook messages received within a given date range. The list of words he wants to count are in column B of a spreadsheet, one word per row. Rakesh wants the counts to appear next to the words in column C. Here’s how Rakesh will use the solution.

  1. Select the Outlook folder containing the messages he wants to process
  2. Run the macro
  3. Enter the path and file name of the Excel workbook containing the words he wants to count for
  4. Enter a date range.

The macro scans every message in the selected folder that arrived within the date range he entered. The code scans each message for every word in the workbook and write the counts to the spreadsheet. Continue reading

Exporting Appointments from Outlook to Excel

I put this solution together for Allen, who had seen my post demonstrating how to export Outlook messages to Excel and asked if I could create a similar macro to export appointments. Allen went on to explain that his organization uses a shared calendar and that periodically he would like to export its contents to Excel. For each appointment, Allen wants to export the organizer (i.e. the person that added the appointment to the calendar), when the appointment was added, the appointment’s subject, when it starts, and who was invited. To make it a little easier for Allen, I broke “who was invited” down into two groups: required and optional attendees. The solution itself is nearly identical to the one I used to export messages.
Continue reading

Outlook Category Checker


I’ve found several interesting Outlook questions on Twitter recently, including this one from Carl Knecht.

In short, Carl wants a way to discover which Outlook categories he’s not using (i.e. no items assigned to the category) so he can delete them. Outlook isn’t able to do this natively, so once again we have to turn to scripting. To do this, the script first reads the categories from the master category list into an array that will keep track of how many times each category has been used. It then reads through every folder (mailbox and PST files alike) and sub-folder. For each item in each folder, the script checks to see if the item is assigned to one or more categories and updates the counts in the array accordingly. The result is a sorted list of category names and the number of items assigned to each. Continue reading