3

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.?

Community
  • 1
  • 1
M. H
  • 199
  • 3
  • 5
  • 13
  • Read this article: it explains adding references dynamically using VBA: http://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically-vba-excel – Alexander Bell Jul 08 '14 at 11:21
  • 2
    Google late binding. Adding dynamically very unlikely to be worth the toruble. – brettdj Jul 08 '14 at 11:26

2 Answers2

7

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")
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • That's great - very comprehensive. I'm going to try late binding, I didn't realise that it meant I wouldn't need the reference. Thanks – M. H Jul 09 '14 at 13:35
  • Sure thing. Give it a shot, I added a few more bits of detail that might help with your conversion from early- to late-binding. – David Zemens Jul 09 '14 at 13:45
  • Where do you implement this function to ensure the reference gets added before it's needed? – schizoid04 May 03 '18 at 19:08
  • @schizoid04 if you have a question of your own, please ask a new & separate question rather than seeking answers in the comments on a 4-year old post :) – David Zemens May 03 '18 at 19:11
  • I've added it here as it's relevant to the answer you've posted and is a request for additional clarification on your answer. – schizoid04 May 03 '18 at 19:15
  • @schizoid04 yes, it's possibly relevant, but that's not how this site works, usually. Ask a new question of your own, include a link back to this one (that way the relevance/link remains between the two distinct questions & their respective answers). Cheers. – David Zemens May 03 '18 at 19:17
  • The other pertinent fact here is that I am simply not going to answer follow-ups here, and because it's such an old question, nobody else will have an opportunity to answer it, because most people don't just browse ancient topics to see if there are recent comments asking for additional detail.. So, if you actually want an answer, ask as a proper question of your own. Cheers. – David Zemens May 03 '18 at 19:19
0

Small append for first sub:

 Sub AddRefToOutlook()

 'This subroutine adds the reference if it doesn't already exist
Const outlookRef As String = "C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB"

If Not RefExists(outlookRef, "Microsoft Outlook " & CLng(Split(Application.Version, ".")(0)) & ".0 Object Library") Then
    Application.VBE.ActiveVBProject.References.AddFromFile ("MSOUTL.OLB")
End If
End Sub
Alexandr Panchenko
  • 111
  • 1
  • 8