Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I get folder statistics from Outlook?

Posted on 2009-05-17
4
Medium Priority
?
855 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

670 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