How to select 'Reference' to user's version of MS Outlook from Excel?

Question

I want to run code in Excel, that talks to Outlook.

I can select the right reference from Tools->References in the VBE.

I want my code to run for other users. They will have different versions of Outlook and Excel.

Is there a way I can make the code select the right reference to MS Outlook, or tell me if Outlook isn't installed, etc.?

Read this article: it explains adding references dynamically using VBA: stackoverflow.com/questions/9879825/… — Alexander Bell
Google late binding. Adding dynamically very unlikely to be worth the toruble. — brettdj

David Zemens David Zemens · Accepted Answer · 2014-07-08T14:14:37

I use a function like this which should work for Outlook 2010. If you're using a different version of Office you may need to change the path/arguments, or if you have to deal with multiple versions of Office then you will need some additional logic to handle the versioning, but this is the basics of it.

This subroutine adds the reference if it doesn't already exist

Sub AddRefToOutlook()
    Const outlookRef as String = "C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB"

    If Not RefExists(outlookRef, "Microsoft Outlook 14.0 Object Library") Then
        Application.VBE.ActiveVBProject.References.AddFromFile _
            outlookRef
    End If
End Sub

This function checks to see if the reference exists (or not)

Function RefExists(refPath As String, refDescrip As String) As Boolean
'Returns true/false if a specified reference exists, based on LIKE comparison
' to reference.description.

Dim ref As Variant
Dim bExists As Boolean

'Assume the reference doesn't exist
bExists = False

For Each ref In Application.VBE.ActiveVBProject.References
    If ref.Description Like refDescrip Then
        RefExists = True
        Exit Function
    End If
Next
RefExists = bExists
End Function

Alternatively

Develop the code on your machine using early binding (with the reference), then before you distribute, change all of the outlook-specific declarations (e.g., As MailItem, As Outlook.Application, etc.) to generic As Object type. Your code will still execute and will not require the references.

With late-binding all that is required is that the appropriate libraries are on the users' machines. This is usually not a problem since you're not using any sort of custom type library or dll, but a standard Office component library that would not be part of the normal windows install.

The only other difference that immediately comes to mind is that you can't use the New keyword on assignment or declaration, e.g.,:

Dim olApp as New Outlook.Application

Or:

Dim olApp as Outlook.Application
Set olApp = New Outlook.Application

Instead, you have to use the CreateObject method:

Dim olApp as Object 'Outlook.Application object
Set olApp = CreateObject("Outlook.Application")

How to select 'Reference' to user's version of MS Outlook from Excel?

2 Answers