Solved

How do I get folder statistics from Outlook?

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

19 Experts available now in Live!

Get 1:1 Help Now