Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Outlook/Excel/VBScript error

Posted on 2009-07-02
4
Medium Priority
?
501 Views
Last Modified: 2013-11-10
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.

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
Comment
Question by:m1kal
  • 2
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24767612
Hello m1kal,

I suspect the culprit is a broken reference.  If you go to tools?references in the VB Editor's menu, are
any references listed as missing?

Is this running from Excel, Outlook, or from somewhere else?

Regards,

Patrick
0
 

Author Comment

by:m1kal
ID: 24783722
There are about a million items under "Available References", but only four, in the top of the list, are checked:
- Visual Basic for Applications
- Microsoft Outlook 12.0 Object Library
- OLE Automation
- Microsoft Office 12.0 Object Library

I'm not sure how it would look if a reference is broken, but there's no text about an error in top nor the bottom of the list, and none of the references are marked in a different color. Still, if they're not one of the list of checked ones on top of the list, that shouldn't be the problem, right?

Maybe MAPI or Excel requires one of the many references? The list is alphabetical, but none of the references start with "MAPI" or "Excel".
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24785613
m1kal said:
>>- Visual Basic for Applications
>>- Microsoft Outlook 12.0 Object Library
>>- OLE Automation
>>- Microsoft Office 12.0 Object Library

Well, an appropriate Excel library is conspicuously absent :)

If you omit the Excel library, then you have to convert your code to late binding, e.g.:



Regards,

Patrick
Sub launchpad()
 
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim myFolder As Outlook.MAPIFolder
Dim xlApp As Object 'Excel.Application
Dim xlbook As Object 'Excel.Workbook
Dim xlsheet As Object '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 Object, 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 Comment

by:m1kal
ID: 24801102
Thank you very much. Your solution worked perfectly. (And after testing it, I found a "Microsoft Excel Object Library" that also made the previous script work ;)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
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…
Progress

963 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