I need to encode the correct "link" to MS Outlook from Excel

Question

I want to run code in Excel that speaks with Outlook . On my machine, I can simply select the correct link from Tools->References in VBE.

But I want my code to run for other users on their machines, and they will all have different versions of Outlook and Excel,

Is there a way that I can make the code select the correct link to MS Outlook or tell me that Outlook is not installed, etc.

thanks

Answer 1

I use a function that should work in Outlook 2010. If you use a different version of Office, you may need to change the path / arguments or you have to deal with several versions of Office, then you will need additional logic for version control, but this its foundation.

This routine adds a link if it does not 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 if the link 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 

As an alternative

Develop the code on your computer using early linking (with a link), then before distributing, change all ads depending on the appearance (for example, As MailItem , As Outlook.Application , etc.) to a common As Object type. Your code will still be executed and will not require links.

With late binding, all that is required is that the corresponding libraries are on users' machines. This is usually not a problem, since you are not using any library of a user type or dll, but a standard library of Office components that will not be part of regular windows.

The only other difference that immediately comes to mind is that you cannot use the New keyword when assigning or declaring, for example:

 Dim olApp as New Outlook.Application 

Or:

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

Instead, you should use the CreateObject method:

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

Answer 2

Little append for the 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