We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

compile error in acess 2003

rustyroo
rustyroo asked
on
Medium Priority
699 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Remove both references and reference them agian.
try adding Office 12 object library.

Author

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")
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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



Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
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

Author

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.

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
no they were not real email addys .. well not complete anyways.... i deleted the identifiying parts of them when i posted..
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.