none
Adding a reference to a project RRS feed

  • Question

  • Hi,

    I am working on an Excel project that need Outlook. I normally use the object variable to get outlook iso including the outlook reference in tools/references to avoid the error that the reference was not found because of the version the user has is not the same as the loaded reference.

    I found some code on the interner to open the address book:

    Private Sub cmdSetProjectMember1_Click()

        Dim olApp As Outlook.Application
        Dim oDialog As SelectNamesDialog
        Dim oGAL As AddressList
        Dim myAddrEntry As AddressEntry
        Dim exchUser As Outlook.ExchangeUser

        Dim AliasName As String
        Dim FirstName As String
        Dim LastName As String
        Dim EmailAddress As String

        Set olApp = GetObject(, "Outlook.Application")
        Set oDialog = olApp.Session.GetSelectNamesDialog
        Set oGAL = olApp.GetNamespace("MAPI").AddressLists("Global Address List")

        With oDialog
            .AllowMultipleSelection = False
            .InitialAddressList = oGAL
            .ShowOnlyInitialAddressList = True
            If .Display Then
                AliasName = oDialog.Recipients.Item(1).Name
                Set myAddrEntry = oGAL.AddressEntries(AliasName)
                Set exchUser = myAddrEntry.GetExchangeUser

                If Not exchUser Is Nothing Then
                    FirstName = exchUser.FirstName
                    LastName = exchUser.LastName
                    EmailAddress = exchUser.PrimarySmtpAddress
                    '...
                    MsgBox "You selected contact: " & vbNewLine & _
                        "FirstName: " & FirstName & vbNewLine & _
                        "LastName:" & LastName & vbNewLine & _
                        "EmailAddress: " & EmailAddress
                End If
            End If
        End With
    Set olApp = Nothing
    Set oDialog = Nothing
    Set oGAL = Nothing
    Set myAddrEntry = Nothing
    Set exchUser = Nothing
    End Sub

    but as you can see it need the outlook reference.

    Found also some other code to laod the reference in runtime mode:

    Sub test()
       Dim VBAEditor As VBIDE.VBE
        Dim vbProj As VBIDE.VBProject
        Dim chkRef As VBIDE.Reference
        Set VBAEditor = Application.VBE
        Set vbProj = ActiveWorkbook.VBProject
       Dim i As Integer
       For i = 1 To vbProj.References.Count
          Debug.Print vbProj.References(i).FullPath
       Next
       vbProj.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office15\MSOUTL.OLB"
       cmdSetProjectMember1_Click
    End Sub

    Fine but vbProj.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office15\MSOUTL.OLB" also includes the version.

    Following gives me the version:

    Sub test2()
       Dim obj As Object
       Set obj = GetObject(, "Outlook.Application")
       
    End Sub

    ? obj.version
    15.0.0.4937

    Final code looks working:

    Option Explicit

    Sub test(version)
       Dim VBAEditor As VBIDE.VBE
        Dim vbProj As VBIDE.VBProject
        Dim chkRef As VBIDE.Reference
        Set VBAEditor = Application.VBE
        Set vbProj = ActiveWorkbook.VBProject
       Dim i As Integer
       For i = 1 To vbProj.References.Count
          Debug.Print vbProj.References(i).FullPath
       Next
       vbProj.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office" & version & "\MSOUTL.OLB"
       cmdSetProjectMember1_Click
    End Sub

    Private Sub cmdSetProjectMember1_Click()

        Dim olApp As Outlook.Application
        Dim oDialog As SelectNamesDialog
        Dim oGAL As AddressList
        Dim myAddrEntry As AddressEntry
        Dim exchUser As Outlook.ExchangeUser

        Dim AliasName As String
        Dim FirstName As String
        Dim LastName As String
        Dim EmailAddress As String

        Set olApp = GetObject(, "Outlook.Application")
        Set oDialog = olApp.Session.GetSelectNamesDialog
        Set oGAL = olApp.GetNamespace("MAPI").AddressLists("Global Address List")

        With oDialog
            .AllowMultipleSelection = False
            .InitialAddressList = oGAL
            .ShowOnlyInitialAddressList = True
            If .Display Then
                AliasName = oDialog.Recipients.Item(1).Name
                Set myAddrEntry = oGAL.AddressEntries(AliasName)
                Set exchUser = myAddrEntry.GetExchangeUser

                If Not exchUser Is Nothing Then
                    FirstName = exchUser.FirstName
                    LastName = exchUser.LastName
                    EmailAddress = exchUser.PrimarySmtpAddress
                    '...
                    MsgBox "You selected contact: " & vbNewLine & _
                        "FirstName: " & FirstName & vbNewLine & _
                        "LastName:" & LastName & vbNewLine & _
                        "EmailAddress: " & EmailAddress
                End If
            End If
        End With
    Set olApp = Nothing
    Set oDialog = Nothing
    Set oGAL = Nothing
    Set myAddrEntry = Nothing
    Set exchUser = Nothing
    End Sub

    Sub test2()
       Dim obj As Object, strVersion As Variant
       Set obj = GetObject(, "Outlook.Application")
       strVersion = Split(obj.version, ".")(0)
       test strVersion
    End Sub

    The question is if this method is reliable or not. Or is there a better way to load a reference? Thanks for your time.

    Sunday, July 2, 2017 12:36 PM

Answers

  • Hello,

    I think the method to dynamically load reference is reliable.But the location would be different based on Office version/platform and OS. Please see: 

    Office 2013
    32-bit
    Windows 32-bit: C:\Program Files\Microsoft Office\Office15\
    Windows 64-bit: C:\Program Files (x86)\Microsoft Office\Office15\
    64-bit
    Windows 64-bit: C:\Program Files\Microsoft Office\Office15\

    Office 2016
    32-bit
    Windows 32-bit: C:\Program Files\Microsoft Office\root\Office16\
    Windows 64-bit: C:\Program Files (x86)\Microsoft Office\root\Office16\
    64-bit
    Windows 64-bit: C:\Program Files\Microsoft Office\root\Office16\

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JP Ronse Tuesday, July 4, 2017 4:46 PM
    Tuesday, July 4, 2017 10:14 AM
    Moderator

All replies

  • Hello,

    I think the method to dynamically load reference is reliable.But the location would be different based on Office version/platform and OS. Please see: 

    Office 2013
    32-bit
    Windows 32-bit: C:\Program Files\Microsoft Office\Office15\
    Windows 64-bit: C:\Program Files (x86)\Microsoft Office\Office15\
    64-bit
    Windows 64-bit: C:\Program Files\Microsoft Office\Office15\

    Office 2016
    32-bit
    Windows 32-bit: C:\Program Files\Microsoft Office\root\Office16\
    Windows 64-bit: C:\Program Files (x86)\Microsoft Office\root\Office16\
    64-bit
    Windows 64-bit: C:\Program Files\Microsoft Office\root\Office16\

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JP Ronse Tuesday, July 4, 2017 4:46 PM
    Tuesday, July 4, 2017 10:14 AM
    Moderator
  • Hi Celeste,

    Thanks for your feedback, very useful but it is also confirming that you have to know versions and path end what will be the 'next' path. Anyway, I can start experimenting further. Thanks again.

    Tuesday, July 4, 2017 4:46 PM