Solved

Outlook/Excel/VBScript error

Posted on 2009-07-02
4
472 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 92

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 92

Accepted Solution

by:
Patrick Matthews earned 125 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

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