See related question/attached snippet. I got this script for Outlook folder statistics in May, and it was running fine in Outlook 2003. 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. There recently was changes to the mail server (All my mail was migrated from one Exchange server to another, I think), but I can't see why that should change anything.
Any suggestions are most welcome.
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"
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
Set myFolder = objNS.PickFolder
Call ProcessFolder(myFolder, xlsheet, CDate(firstDate), CDate(lastDate))
xlApp.Visible = True
Set objNS = Nothing
Set myFolder = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlApp = Nothing
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
' process all the subfolders of this folder
For Each objFolder In startFolder.Folders
Call ProcessFolder(objFolder, dataRecord, first, last)
Set objFolder = Nothing