Solved

How do I get folder statistics from Outlook?

Posted on 2009-05-17
4
832 Views
Last Modified: 2013-11-10
In Outlook, I have an inbox with multiple subfolders. Everything received to the inbox is manually sorted into the subfolders after reading the mail. E.g. everything regarding payments goes into the "Invoice" sub folder, everything spam-ish goes into the "Junk mail" folder, everything to be forwarded to specific departments goes into the "Forwarded to XX dept." folders, and so on.

It is not a fixed number of folders: If I one day notice I receive more and more "I have a new e-mail address" e-mails, I might make a new "Adress change - Email" folder.
 
What I'm looking for is statistics. Ideally, I would run a program, it would ask me for a date range, and return a tab separated list showing folder names and the number of e-mails in each folder within the date range.
 
Something like:
 
Invoice(tab)34
Forwarded to John(tab)23
Complaints(tab)19
Forwarded to Kevin(tab)15
Address change - Email(tab)11
Junk-mail(tab)6
 
etc. (Could easily be 20-30 different folders)
 
The purpose is to be able to easily see what kind of mails we receive, and paste this tab separated list into Excel  to make graphs so I can compare week to week, month to month, etc.
 
Some technical details:
This is Outlook 2003 running through a Citrix server, so I can not run external programs to do this. I don't know much about Visual Basic, but it sounds likely it could do the job. The inbox is not my default Outlook inbox, but an extra one I've added through the advanced menu of the Exchange server settings, so the program or script would have to be told it is only to be run on one particular mailbox.
0
Comment
Question by:m1kal
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 24409724
The following is a VBA code set to take all the folders under and including teh selected folder and list a mailcount for the folders.

To Create a macro:
------------------

Alt + F11 to open the macro editor
     Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, (launchpad & ProcessFolder)
Close the Visual Basic Editor.

Check Security as appropriate:
------------------------------

In the application select Tools | Macro | Security
Select Medium
Select OK

To run a macro:
---------------

Alt + F8
Select the macro
Select 'Run'

Chris
Sub launchpad()
 

Dim olApp As Outlook.Application

Dim objNS As Outlook.NameSpace

Dim myFolder As Outlook.MAPIFolder

Dim xlApp As Excel.Application

Dim xlbook As Excel.Workbook

Dim xlsheet As Excel.Worksheet

Dim firstDate As Variant

Dim lastDate As Variant

    

    On Error Resume Next

    

    Set xlApp = CreateObject("Excel.Application")

    Set xlbook = xlApp.Workbooks.Add

    Set xlsheet = xlbook.Worksheets(1)

    firstDate = InputBox("Enter the First date for the required filter:", vbOKCancel, "Date Filter")

    If firstDate = vbCancel Then Exit Sub

    firstDate = CDate(firstDate)

    lastDate = InputBox("Enter the Last date for the required filter:", vbOKCancel, "Date Filter")

    If lastDate = vbCancel Then Exit Sub

    lastDate = CDate(lastDate)

    xlsheet.Range("A1") = "Folder"

    xlsheet.Range("B1") = "First Date"

    xlsheet.Range("C1") = "Last Date"

    xlsheet.Range("D1") = "Mail Count"

    xlsheet.Range("a2").Activate

    

    Set olApp = Outlook.Application

    Set objNS = olApp.GetNamespace("MAPI")

    Set myFolder = objNS.PickFolder

    Call ProcessFolder(myFolder, xlsheet, CDate(firstDate), CDate(lastDate))

    xlsheet.Select

    xlsheet.Range("A1").Select

    xlApp.Visible = True

    xlsheet.Application.Range("a1").Activate

    

    Set objNS = Nothing
 

Set myFolder = Nothing

Set xlsheet = Nothing

Set xlbook = Nothing

Set xlApp = Nothing

End Sub
 
 

Sub ProcessFolder(startFolder As MAPIFolder, dataRecord As Excel.Worksheet, first As Date, last As Date)

Dim objFolder As Outlook.MAPIFolder

Dim colitems As Outlook.Items

Dim strFilter As String

    

    On Error Resume Next

    

    ' process all the items in this folder

    strFilter = "[ReceivedTime] >= '" & Format(first + TimeSerial(0, 0, 0), "ddddd h:nn AMPM") & "' and [ReceivedTime] <= '" & Format(last + TimeSerial(0, 0, 0), "ddddd h:nn AMPM") & "'"

    'strFilter = "[ReceivedTime] >= '" & Format(first + TimeSerial(0, 0, 0), "ddddd h:nn AMPM") & "'"

    Set colitems = startFolder.Items.Restrict(strFilter)

            dataRecord.Application.ActiveCell.Offset(0, 0) = startFolder.FolderPath

            dataRecord.Application.ActiveCell.Offset(0, 1) = first

            dataRecord.Application.ActiveCell.Offset(0, 2) = last

            dataRecord.Application.ActiveCell.Offset(0, 3) = colitems.Count

            dataRecord.Application.ActiveCell.Offset(1, 0).Activate

        

    ' process all the subfolders of this folder

    For Each objFolder In startFolder.Folders

        Call ProcessFolder(objFolder, dataRecord, first, last)

    Next
 

Set objFolder = Nothing

End Sub

Open in new window

0
 

Author Closing Comment

by:m1kal
ID: 31582496
Excellent, exactly what I was looking for. Thank you.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24409890
Glad to help.

Chris
0
 

Author Comment

by:m1kal
ID: 24744912
Since migrating to Outlook 2007 (12.0.6316.5000), the script has refused to work. When trying to run it, I get "Compile error: User-defined type not defined".  The VBA editor highlights the line "Sub ProcessFolder(startFolder As MAPIFolder, dataRecord As Excel.Worksheet, first As Date, last As Date)".

I disabled the macro security, so that should not be the problem.

Any suggestions are most welcome.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now