[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

compile error in acess 2003

Hi,

i have a database in Access 2003 which has worked fine till i moved it to a new server (running Server 2008 R2 Foundation) with outlook 2007.  I used to run outlood 2003 on the old server.  now when i try to run the below simple email command i get a compile error: Reference Library feature not supported.

I have the Micorsoft Acess 11.0 library and the Microsoft Outlook 12.0 Library  ticked in the references.   What am i missing?

appreciate your help.
Rusty

Public Function SendEmailRunrate()

Dim oOApp As Outlook.Application
Dim oOMail As Outlook.MailItem

Set oOApp = CreateObject("Outlook.Application")
Set oOMail = oOApp.CreateItem(olMailItem)

With oOMail
 .To = "toowoomba@tela.com.au;Bice@team.com;accounts@australia.com.au;dalby@tel.com.au"
 .Subject = "QTD Runrate COB Yesterday Clifford Gardens and Dalby"
 .Body = "Please Find Runrate Attached. Check GRAPH Tab to see how we are going"
 '.Attachments.Add "\\server\drive\folder\filename", olByValue, 1
 .Attachments.Add ("c:\BC Files\reports\Quarterly.xls")
 .Send
End With

End Function
0
rustyroo
Asked:
rustyroo
  • 3
  • 3
  • 2
  • +1
1 Solution
 
hnasrCommented:
Remove both references and reference them agian.
try adding Office 12 object library.
0
 
rustyrooAuthor Commented:
ok tried that and now i get the error

runtime error 429 : active x cannot create object

in this line
Set oOApp = CreateObject("Outlook.Application")
0
 
Jeffrey CoachmanMIS LiasonCommented:
This is one of the reasons why I don't mix code references with different versions of Office.

Why not upgrade Access to 2007 as well and possibly avoid this issue altogether?

Code similar to what you posted works fine for me if I use the same Office version and the same VBA references...
Access/Outlook 2003
Access/Outlook 2007

JeffCoachman
0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
mbizupCommented:
Try using Late Binding in code instead of references (Early Binding).

This method *should* work regardless of which Office version you are using, so it should be portable from system to system without needing to deal with the references checkboxes.

The only difference is in how you define your Outlook Objects at the top of the function.  

Also, Error 429 indicates that you are attempting to create your Outlook Application when one already exists, or "Get" one when it doesn't exist.  You should have error handling in your code to manage this if it happens, and you should "unset" your objects at the end of your code to avoid this error on repetitive runs of your code.

Dim oOApp As Object   '<--- Use "Object" in your Dim statements.
Dim oOMail As Object

    On Error Resume Next
    Set oOApp = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Set oOApp = CreateObject("Outlook.application")
    End If

    On Error GoTo 0

    Set oOMail = oOApp.CreateItem(olMailItem)
 
   '*** the rest of your code remains the same
  With oOMail 
        .To = "abc@xyz.com" 
        .Subject = "QTD Runrate COB Yesterday Clifford Gardens and Dalby" 
        .Body = "Please Find Runrate Attached. Check GRAPH Tab to see how we are going" 
       '.Attachments.Add "\\server\drive\folder\filename", olByValue, 1 
        .Attachments.Add ("c:\BC Files\reports\Quarterly.xls") 
       .Send 
  End With

'******
' "Unset" your objects
      Set oOApp = Nothing
      Set oOMail = Nothing

End Function

Open in new window



0
 
hnasrCommented:
Ok!
This is your original code, minimal modification as shown. Working fine. Remmed the attachment.

Try it in a command click event.

Private Sub Command4_Click()
    Dim oOApp As Object      ' Outlook.Application =========> Object from access help
    Dim oOMail As Object      ' Outlook.MailItem =========> Object

    Set oOApp = CreateObject("Outlook.Application")
    Set oOMail = oOApp.CreateItem(olMailItem)

    With oOMail
        .To = "toowoomba@tela.com.au;Bice@team.com;accounts@australia.com.au;dalby@tel.com.au"
        .Subject = "QTD Runrate COB Yesterday Clifford Gardens and Dalby"
        .Body = "Please Find Runrate Attached. Check GRAPH Tab to see how we are going"
        '.Attachments.Add ("c:\BC Files\reports\Quarterly.xls")  '  =========>   remmed
        .Send
    End With
    Set oOApp = Nothing    ' =========> added, rule of thumb
    Set oOMail = Nothing    ' ' =========> added


End Sub
0
 
rustyrooAuthor Commented:
Still having the same problems with error 429 on most of these suggestions... except solution from mbizup: gives me an Error 91:object variable or with block variable not set.

I have access 2007 just didnt watn to mess with it cause better the devil you know...but when i open, i get the same errors in access 2007.

was wondering if it would make a difference installing outlook 2003 with this as it is not mission critical what program i use, just that it works simply.

0
 
mbizupCommented:
Here you go...

You also need to define olMailItem as a constant when using Late Binding (see the first line of code).

Also in the VBA Editor, ensure that Tools -> Options -> General -> Error Handling is set to "Break on unhandled errors".  It probably already is, but just double check it.


Public Function SendEmailRunrate()

Const olMailItem As Long = 0  '******* Forgot this line :)
Dim oOApp As Object   '<--- Use "Object" in your Dim statements.
Dim oOMail As Object

    On Error Resume Next
    Set oOApp = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Set oOApp = CreateObject("Outlook.application")
    End If

    On Error GoTo 0

    Set oOMail = oOApp.CreateItem(olMailItem)
 
   '*** the rest of your code remains the same
  With oOMail
        .to = "abc@xyz.com"
        .Subject = "QTD Runrate COB Yesterday Clifford Gardens and Dalby"
        .Body = "Please Find Runrate Attached. Check GRAPH Tab to see how we are going"
       '.Attachments.Add "\\server\drive\folder\filename", olByValue, 1
        .attachments.Add ("c:\BC Files\reports\Quarterly.xls")
       .Send
  End With

'******
' "Unset" your objects
      Set oOApp = Nothing
      Set oOMail = Nothing


End Function

Open in new window

0
 
mbizupCommented:
Again - that should *theoretically* work regardless of your Office versions.

As an aside, are those real email addresses that you are using?  I masked it in my code, but you may want to have them removed from this thread for privacy.
0
 
rustyrooAuthor Commented:
no they were not real email addys .. well not complete anyways.... i deleted the identifiying parts of them when i posted..
0

Featured Post

Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now