Fixing MISSING references in VBA code

[Pat]

Is there a way to do this in VBA code?
I realise that if a reference is MISSING that VBA code will not execute.

Is there a way from another database to test for a MISSING reference in a target database and if thee are any to untick them and tick the appropriate ones.

[HansV]

It's virtually impossible in Access. You have to clear and set references from within the database itself, and as you have found, code refuses to run if there is a missing reference.

To get around this, you'd have to fully qualify ALL your code, i.e. use VBA.String instead of String, Access.DoCmd instead of DoCmd, etc., and you'd have to isolate the code to check references in a separate module. It's more work than it's worth.

[Pat]

Fair enough, i agree with you it looks like a lot of work just to check references.
The clients can do it manually, no big deal.
Thank you

[HansV]

Using late binding instead of early binding can help prevent problems with missing references.

[Pat]

The main reference i am concerned about is Outlook, i have Outlook 2007 and the client has 2003.

Can late binding solve that? Don't you still have to set the reference to Outlook?

[HansV]

You can use late binding for Outlook; if you do that, you can clear the reference to the Microsoft Outlook n.0 Library (where n=11 for Outlook 2003 and n=12 for Outlook 2007), so you won't have problems with a missing Outlook reference.

To switch to late binding, take the following steps:

1) Declare all Outlook variables as Object instead of as specific Outlook objects. E.g. change

Dim olkApp As Outlook.Application
Dim olkMsg As Outlook.MailItem

to

Dim olkApp As Object
Dim olkMsg As Object

2) Do not use the keyword New with Outlook objects, but use CreateObject instead. E.g. change

Set olkApp = New Outlook.Application

to

Set olkApp = CreateObject("Outlook.Application")

3) Replace all symbolic Outlook constants with their values. You can look up these values in the object browser in the Visual Basic Editor in Outlook, or in the Immediate window. E.g. replace olCC with its value 2, and replace olMailItem with its value 0.

4) Now clear the check box for the Outlook reference in Tools | References...

5) Select Debug | Compile <projectname> to check whether the code is still correct.

With these changes, the code should run for any version of Outlook.

[Pat]

Thanks Hans i did all that and it works fine here so hopefully it should work fine there too.

I also had to substitute 1 for olDiscard and olByValue

[HansV]

I also had to substitute 1 for olDiscard and olByValue

Yes - as I mentioned, you have to "Replace all symbolic Outlook constants with their values" (underline added).

[ChrisGreaves]

To switch to late binding, take the following steps:

Hans!
Thanks for this great little step-by-step tutorial.

[Van]

HI Hans,
very late to this post, but I'm encountering the same problems with the Access and the later outlook reference MS Outlook 16.0 Object Library. we've recently updated to a newer version of Outlook. Below is the code we're using to open Outlook then send a reminder txt message to patients. I've read above, but it's a bit over my head. Are you able to help?

Many Thanks,
Van :-)

Public Sub cmdEmail_Click()
'Declare variables
Dim email As String
Dim ref As String
Dim notes As String

'create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'gathers information from your form. this sets the string variable to my fields
email = Me![telephone 1] & "@SMS.SMScentral.com.au"
ref = "Reminder regarding your upcoming appointment at Peninsula Health"
notes = "Reminder of your overnight admission at Peninsula Health at 8 p.m. on " & Me![test date] & vbCr & "Please Phone 9788 1705 to confirm or cancel your attendance. If it has not been confirmed within 48 hours of appointment date it will be cancelled." & vbCr & "YOU CANNOT REPLY TO THIS MESSAGE."

'create an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'create and sends email
With objEmail
.To = email
.Subject = ref
.Body = notes
.Display 'displays the email in Outlook for editing. Change to SEND if you want to be able to
'send immediately
End With

End Sub

[HansV]

Here is the late binding version:

Code: Select all

Public Sub cmdEmail_Click()
    'Declare variables
    Dim email As String
    Dim ref As String
    Dim notes As String

    'create variables for Outlook
    Dim objOutlook As Object
    Dim objEmail As Object

    'gathers information from your form. this sets the string variable to my fields
    email = Me![telephone 1] & "@SMS.SMScentral.com.au"
    ref = "Reminder regarding your upcoming appointment at Peninsula Health"
    notes = "Reminder of your overnight admission at Peninsula Health at 8 p.m. on " & _
        Me![test date] & vbCr & _
        "Please Phone 9788 1705 to confirm or cancel your attendance. " & _
        "If it has not been confirmed within 48 hours of appointment date it will be cancelled." & _
        vbCr & "YOU CANNOT REPLY TO THIS MESSAGE."

    'create an instance of Outlook
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(0) ' olMailItem = 0

    'create and sends email
    With objEmail
        .To = email
        .Subject = ref
        .Body = notes
        .Display 'displays the email in Outlook for editing. Change to SEND if you want to be able to
        'send immediately
    End With
End Sub

[Van]

thanks Hans. very much appreciated!

Van

[Van]

sorry Hans,

we're getting this message now: MISSING msoutl.olb when trying to open the dB. This message isn't on all PC's however, only seems to happen on what our IT Dept call "thin client" PCs.

can you please advice?
thanks
Van

[HansV]

The code will only work if Outlook has been installed. If it isn't present, you'll get an error.

[Van]

outlook is installed as far as I can tell (it's opened on this thin client PC that is getting the error). maybe I will ask our IT Dept to load msoutl.olb on the server where MS Access and MS Outlook are located.

thanks,
V

[HansV]

They will also need to register the .olb file. They will know how to do that.