Exporting Email Info for Panic’s StatusBoard

 

A reader named Marc contacted me with an interesting request. Marc is a fan of the iPad app Status Board. As the name suggests, the app display a dashboard (status board) composed of panels. Each panel displays a different piece of information, such as a clock, the weather, a news feed, etc. One of the app’s features is the ability to create custom panels containing user-defined information. Custom panels read a file and display its contents. Marc saw my post on exporting data to Excel and asked if I could create a knock-off that would export mailbox details to an HTML file instead of to Excel.

Advertisement

Exporting Unflagged Message Counts to Excel

This post is for Chris Bull who asked for some modifications to the code in my post titled Exporting Outlook Message Counts to Excel. Chris needs to export message counts for messages in two public folders that meet specific conditions. Here’s how Chris explained it to me in his comment.

I need to Create an Excel spreadsheet which will count the total number of ‘unflagged’ messages in two named public folders as well as the number of ‘unflagged’ messages over 7 days old. It would be useful to tag this with the date the counts were generated. Ideally I would like this to be done automatically on a weekly basis.

Sorry, to clarify there are people who mark emails with a flag and others who mark them with a tick. So really I need ‘unflagged and unticked’ as the criteria!

In order to meet Chris’ requirements I modified the code from my original post by

• Adding the ability to pass two Outlook folder paths instead of one.
• Adding the necessary conditional statements to determine if each item in the folder is flagged or not.
• Changing the output to write the date and the four counts, two for each folder, to the spreadsheet.

Exporting Outlook Message Counts to Excel

I’m writing this post in response to a comment Shaima Alsaif left on my “Exporting Outlook Messages to Excel” entry from this last October. In the comment Shaima asked if it’s possible to alter the export to do the following:

• Get message counts instead of messages
• Add to the spreadsheet instead of overwriting it
• Have the process run automatically each week

The answer to all three is “yes”. Doing this requires taking a different approach from the one I used in the original solution. The biggest change is that this solution uses VBScript instead of VBA. Here’s why. Outlook does not have a built-in means of scheduling code to run. It’s possible to get around that (e.g. using a repeating task coupled with an event handler that traps the ReminderFire event), but it’s better to use a true scheduler like the one built into Windows (i.e. Task Scheduler). To the best of my knowledge Windows Task Scheduler cannot call a VBA routine, hence the need to use VBScript instead.