Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    VBA Error (2002)

    Loungers,

    With the help of the lounge I setup a VBA code that works well for me using Office 2003 and generates an email as required, however when another person opening the spreadsheet in 2002 a compile error occurs (see attached).

    Any thoughts on what may be causing this error and any suggestions to fix the problem

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Error (2002)

    I'm taking a guess, but it seems you need to set a reference to the Outlook application in the VBA object library!
    Open Excel and press ALT + F11
    Choose the TOOLS menu and select References.
    Scroll through the list and activate the checkbox next to Microsoft Outlook 10.0 Object Library!

    This sould set a reference to Outlook on that PC and continue the macro!
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: VBA Error (2002)

    Rudi,

    Thanks for the guess - I'll give it a go.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Error (2002)

    References usually work OK if you move to a later version, but not if you move to an earlier version. If you want to avoid such problems, you can use late binding:
    - Replace all symbolic Outlook constants with their value (you can look them up in the Object Browser), e.g. replace olMailItem with 0.
    - Declare all Outlook objects As Object instead of As Outlook.Something.
    - Clear the reference to the Microsoft Outlook n.0 Object Library.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: VBA Error (2002)

    Hans,

    Thanks for the reply, however I'm not sure how to do what you suggest. Do these alteration occur in the code? Is so would you mind showing where theses apply or in the object browser or both.

    Thanks for your all your help



    Sub SendMailsOneMonth()
    Dim olApp As Outlook.Application
    Dim olItem As Outlook.MailItem
    Dim oSheet As Worksheet
    Dim lRow As Long
    Dim lMaxRow As Long
    Dim strBody As String

    On Error Resume Next

    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    If olApp Is Nothing Then
    Exit Sub
    End If
    End If

    On Error GoTo ExitHandler

    Set olItem = olApp.CreateItem(olMailItem)
    olItem.Recipients.Add "deanj@crushing.com.au; rachelw@crushing.com.au"
    olItem.Subject = "Reminders for " & Format(Date, "mmm d, yyyy")
    strBody = "Please note the following:" & vbCrLf

    ' Reference to worksheet
    Set oSheet = Worksheets("details")
    ' Determine last filled cell in column C
    lMaxRow = oSheet.Range("f65536").End(xlUp).Row
    ' Loop through cells in column C
    For lRow = 4 To lMaxRow

    ' Check if
    ' - column C contains something (mail address)
    ' - Column F between today and one month from today
    ' - Column H is blank (since this is tested in formula too)
    ' - column I is blank (set if mail has been sent already)
    If oSheet.Cells(lRow, 6) <> "" And _
    oSheet.Cells(lRow, 9) > Date And _
    oSheet.Cells(lRow, 9) <= DateAdd("m", 1, Date) And _
    oSheet.Cells(lRow, 11) = "" And _
    oSheet.Cells(lRow, 12) = "" Then
    ' Create e-mail
    strBody = strBody & vbCrLf & oSheet.Cells(lRow, 6) & _
    "'s performance assessment is due on " & Format(oSheet.Cells(lRow, 9), "mmmm d, yyyy") & " - from " & oSheet.Cells(lRow, 4)
    ' Put something in column I to prevent mail next time
    oSheet.Cells(lRow, 12) = "Sent"
    End If
    Next lRow

    olItem.Body = strBody & vbCrLf & vbCrLf & " From your friendly automated Performance Reviews spreadsheet"
    ' For testing, use the following line:
    olItem.Display
    ' For real, use the following line instead:
    ' olItem.Send

    ExitHandler:
    Set oSheet = Nothing
    Set olItem = Nothing
    Set olApp = Nothing
    Application.OnTime Now + 1, "SendMailsOneMonth"
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Error (2002)

    You need the following changes:

    - Declare Outlook items as Object
    Dim olApp As Object ' instead of Outlook.Application
    Dim olItem As Object ' instead of Outlook.MailItem

    - Replace Outlook constants with their value
    Set olItem = olApp.CreateItem(0) 'instead of olMailItem

    - Select Tools | References... and clear the check box for the Microsoft Outlook n.0 Object Library

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: VBA Error (2002)

    Hans,

    Sorry about the delay in getting back to you.

    Thanks once again for your help - that works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •