Removing Broken References and Adding VBA References

A user had a question about my Controlling Outlook From Excel post.

I have been reading your homepage and I have a problem with my macro. It works
fine when the Microsoft Outlook Object Library is checked as reference.
But my file is to be executed on 67 different PCs and they have not (many of them)
any reference marked to Outlook. And if the reference to outlook is not marked
in advance, then the macro doesn’t run – but displays a compile error.

I have tried this with both Early and Late Binding.

Do you know a method where the macro by itself find the outlook version and automatically set the references?

I had the same problem, as I take a workbook of mine home and there are different versions of Outlook. When I returned to work, there was always a broken reference to Outlook 11 (2003).

The following code runs fine on my machine but I do not have any broken references at the moment. Note that the Auto_Open routine along with the rest of the code needs to be in a standard module. Auto_Open runs before the Workbook_Open event. Thanks to a comment from Rob van Gelder for the major and minor version code.

Note that there needs to be a reference to “Microsoft Visual Basic for Applications Extensibility 5.3? for this code to work.

Sub Auto_Open()

Call RemoveOutlookReferences
Call LoadOutlookReferences

End Sub

Sub LoadOutlookReferences()

'load Outlook object library
On Error Resume Next
'When using AddFromGUID, you can use zero for the major
'and minor versions to pick the latest.

'adding VBE object library:
ActiveWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 0, 0
'Adding outlook object library:
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 0, 0
On Error GoTo 0

End Sub

Function ReferenceIsBroken(sRef As String) As Boolean

Dim vbProj As VBProject ' This refers to your VBA project.
Dim chkRef As Reference ' A reference.

' Refer to the VBA project.
Set vbProj = ThisWorkbook.VBProject

' Check through the selected references in the References dialog box.
For Each chkRef In vbProj.References

If sRef = chkRef.Name And chkRef.IsBroken Then

'if the ref matches the found ref and it is broken
ReferenceIsBroken = True
Exit Function

End If

Next

ReferenceIsBroken = False

End Function

Sub RemoveOutlookReferences()


Dim liCnt As Integer
Dim sRef As String
Dim count
Dim sThisVBEName As String
Const OUTLOOK_REF As String = "OUTLOO"

sThisVBEName = ThisWorkbook.VBProject.Name

'' Remove references to Outlook if they are there and are broken
For liCnt = Application.VBE.VBProjects.Item(sThisVBEName).References.count To 1 Step -1
sRef = UCase(Left(Application.VBE.VBProjects.Item(sThisVBEName).References.Item(liCnt).Name, 6))

Debug.Print sRef

If sRef = OUTLOOK_REF And ReferenceIsBroken(sRef) Then

Application.VBE.VBProjects.Item(sThisVBEName).References.Remove Application.VBE.VBProjects.Item(sThisVBEName).References.Item(liCnt)

End If
Next liCnt

End Sub