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

x
?
Solved

Outlook/Excel/VBScript error

Posted on 2009-07-02
4
Medium Priority
?
496 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
[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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

664 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