Code to load reference / Object library automatically for office 2007 & 2010 versions

[ramanicwa]

Hi,


I'm working on an automation that will connect ms excel and outlook.


I want a code to work on both office 2007 & 2010 versions.


I'm getting a compile error "Can't find project or library" when i used a code taken from the below thread.


VBA References


I've given the code below which i used. Kindly help to overcome this issue as this an urgent assignment to be completed.

Kindly help me on this issue.

Thanks in advance.

Private Sub Workbook_Open()
Call OutlookLibrary("ADD")


Sub OutlookLibrary(action As String)
Dim olPath As String, ObjLib As String, olShell As String, olVersion As String
Dim WSH As Object
Set WSH = CreateObject("WScript.Shell")
olShell = "HKCR\CLSID\{00020D75-0000-0000-C000-000000000046}"
olPath = WSH.RegRead(olShell & "\Shell\Open\Command\")
olPath = Mid(olPath, 2, Len(olPath) - 13)
olVersion = WSH.RegRead("HKCR\Outlook.Application\")
Select Case olVersion
    Case "Microsoft Outlook 12.0 Object Library"
        ObjLib = "msoutl.olb"
    Case "Microsoft Outlook 14.0 Object Library"
        ObjLib = "msoutl.olb"
End Select
On Error Resume Next
If UCase(action) = "ADD" Then
    Application.VBE.ActiveVBProject.References.AddFromFile olPath & ObjLib
Else
    Application.VBE.ActiveVBProject.References.Remove Application.VBE.ActiveVBProject.References("Outlook")
End If
Set WSH = Nothing
End Sub