Remove missing reference, update outlook reference

Hello,

I have an excel xlsm file which includes Microsoft outlook reference.
The file is opened in excel 2007 and excel 2010.

When the file is opened in Excel 2007 after it is opened in Excel 2010
I get a reference error message and a missing reference.

I wish to dynamic update the reference - remove the missing outlook reference and add the correct one.

I use the following code. If the reference is missing it adds it correctly.
My problem: when there is a missing reference the code can not remove it and I get an error.

Can you help me ?

Thank you for your help

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

Sub References_RemoveMissing()
     'Macro purpose:  To remove missing references from the VBE
     
    Dim theRef As Variant, i As Long
     
    On Error Resume Next
     
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.IsBroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i
     
    If Err <> 0 Then
        MsgBox "A missing reference has been encountered!" _
        & "You will need to remove the reference manually.", _
        vbCritical, "Unable To Remove Missing Reference"
    End If
     
    On Error GoTo 0
End Sub

Hello Snoopy2003,

You can easily resolve this problem by using late binding in your code. The downside is you can not use named constants from the object library and you do not have the Intellisense to show you the various properties and methods of the object. The upside is you do not have to fiddle about with checking for and loading the correct library and removing missing references.

Late Binding Example for Outlook

  Dim olApp As Object

    Set olApp = CreateObject("Outlook.Application")

Hello Snoopy2003,

Named constants are usually prefixed with ol. Here is an example of the predefined named constants for the Default Folders. To find the constants for Outlook use the Object Browser in the Visual Basic Editor by pressing F2.

Outlook Default Folder Constants and Values

olFolderCalendar = 9
olFolderConfilicts = 19
olFolderContacts = 10
olFolderDeletedItems = 3
olFolderDrafts = 16
olFolderInbox = 6
olFolderJournal = 11
olFolderJunk = 23
olFolderLocalFailues = 21
olFolderNotes = 12
olFolderOutBox = 4
olFolderSentMail = 5
olFolderServerFailures = 22
olFolderSyncIssues = 20 
olFolderFolderTasks = 13
olPublicFoldersAllPublicFolders = 18