Solved

How do I get folder statistics from Outlook?

Posted on 2009-05-17
4
840 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

808 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