Count the number of items in an Outlook folder and export the data to Excel

[GarrickB]

Hello,

I've been working on setting up a way to easily monitor the amount of emails processed by individuals using a shared inbox. I'd like to create a VBA script to output this information to an Excel sheet (either automated, or manually). The information I need to collect is, in order of priority, listed below:

-The amount of emails replied to by an individual per day (or over different time periods)
-The amount of emails processed in total per day (or over different time periods)
-The amount of emails in each category per day (or over different time periods) total

Ideally I'd also like to track the amount of emails sent and received by the inbox over a given time period.

The Exchange server is controlled by an outside group, and stats from them aren't easily obtainable.

People using the shared Inbox process emails by categorizing the email they are going to work on (each individual uses their own category), and then CC'ing any replies to the email back to the shared inbox address. The CC'd replies are then categorized and moved to the individual's folder, and the email that has been worked is moved (still categorized to the individual) to a 'worked emails' folder for record keeping.

For simplicity I'm thinking the easiest solution, given my very limited VBA ability, is to get a count on the amount of messages added to the individual's folders over a period of time, and export the information to an Excel file. This would at least give a rough idea of how many emails each individual is going through a day. This seems like it should be fairly easy to do, but I am finding I need a starting point. I'm hoping someone already has a solution built for this that can be setup for my situation.

Is there a simple way to take the count of the number of items in an Outlook folder and export that number to an Excel file? Also, is there a way to take a count of the number of items added to a category in a day and export it similarly?

Any suggestions or ideas would be greatly appreciated. Getting general metrics for this shared inbox is a high priority, and just getting the basic states for each person using it would be a great start.

Thank you for your time.

 

[kjv1611]

I honestly do not remember the details off-hand, and can no longer get to the final code I wrote, but I'll at least tell you this much: Be cautious of your final method. I found and tested with at least 2 if not 3 methods of getting a count of items, and most methods will bring Outlook to a crawl if there are many emails. However, if you use methods (I think it's what it is) that refer to collections within Outlook (I may not be using the correct term here - just off the cuff) rather than looping through and counting, you'll come out way better.

What I did was setup a form where the user could select a series of days they wanted to review, and it'd show how many "new" emails remained for each day within the date range. It was used for part of their production numbers until we found better methods than the user counting Outlook emails.

Also, I've not dug through the code at all, but this might be something to get you started:
extendoffice.com/.. outlook-count-emails-by-category

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[GarrickB]

@kjv1611

Thanks for the tip and resource! That link might lead to everything I need. The code to count by category is perfect if I can refine it to search sub-folders, which I should be able to find something on.

Thanks again!