How can I reference Microsoft Outlook 15.0 Object Library?

[rkjudy]

I have a workbook that allows the user to send an email of one particular sheet and it works great. In the past, I only had users running MS Office 2007 and 2010. I have code (see below) that would check the version and then reference the the correct Microsoft Outlook 12.0 (or 14.0) Object Library (required, I guess for emailing within Excel) and everything worked. The code you see below was provided by another Forum user. I don't totally understand what's going on, but it works. My problem is that now I have MS Office 2013 users which uses Microsoft Outlook 15.0 Object Library. I need to expand my code to check and recognize Office 2013 (as well as 2007 and 2010) and then reference the appropriate Object Library. Does anyone know what I need to add so that the code will also reference the Microsoft Outlook 15.0 Object Library.

Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    Application.DisplayFormulaBar = False
    
    Dim wbRef As Variant, wbRefFound As Boolean
    wbRefFound = False
    
    For Each wbRef In ThisWorkbook.VBProject.References
        If wbRef.Description = "Microsoft Outlook 12.0 Object Library" Or _
           wbRef.Description = "Microsoft Outlook 14.0 Object Library" Then
            
            wbRefFound = True
            Exit For
            
        End If
    Next
    
    If wbRefFound = False Then
        Dim Reference As Object
        Dim OLB As String
        Dim Vmajor, Vminor
            
        'Load Microsoft Outlook 12.0 or 14.0 Object Library
        OLB14 = "{00062FFF-0000-0000-C000-000000000046}"
        Vmajor14 = 9
        Vminor14 = 4
           
        OLB12 = "{00062FFF-0000-0000-C000-000000000046}"
        Vmajor12 = 9
        Vminor12 = 3
             
        If Application.Version = 12 Then
            ThisWorkbook.VBProject.References.AddFromGuid OLB12, Vmajor12, Vminor12
        Else
            ThisWorkbook.VBProject.References.AddFromGuid OLB14, Vmajor14, Vminor14
        End If

    End If
    
End Sub

[shg]

Why not just use late binding and no reference?

[rkjudy]

I would love to make this as simple as possible. I'm not familiar with late binding, but if this will make it where anyone using any version will be able to email within Excel, I would appreciate help with an example. Thanks.

[shg]

Declare all of the Outlook variables as Object, and use CreateObject to instance the Outlook application. You will need to declare all enumerations that would otherwise come from the library.

I can't show you an example for Outlook (never done any Outlook automation), but here's an example of code that can be switched from early-bound for development (which gives you Intellisense) to late-bound for deployment:

#Const EarlyBound = False

Function WriteUTF8(sText As String, sFile As String) As Boolean
  ' Returns True if sText saved successfully as UTF-8 in sFile

  On Error GoTo Oops

  #If EarlyBound Then
    ' Requires a reference to Microsoft ActiveX Data Objects
    With New ADODB.Stream
  #Else
    ' No reference required
    Const adTypeText As Long = 2
    Const adSaveCreateOverWrite As Long = 2
    With CreateObject("ADODB.Stream")
  #End If
      .Type = adTypeText
      .Charset = "utf-8"
      .Open
      .WriteText sText
      .SaveToFile FileName:=sFile, Options:=adSaveCreateOverWrite
      WriteUTF8 = True
    End With
    Exit Function

Oops:
  MsgBox Err.Description
End Function

[romperstomper]

I have an example of early vs late binding for Outlook here: http://excelmatters.com/?p=69

[shg]

Rory, your site looks GREAT!

[romperstomper]

Thanks. Still needs a lot of work!

[milu]

Private Sub Workbook_Open() 'Reference - Microsoft Outlook 15.0 Object Library
Dim wbRef As Variant, wbRefFound As Boolean
wbRefFound = False

For Each wbRef In ThisWorkbook.VBProject.References
Debug.Print wbRef.Description
If wbRef.Description = "Microsoft Outlook 12.0 Object Library" Or _
wbRef.Description = "Microsoft Outlook 14.0 Object Library" Or _
wbRef.Description = "Microsoft Outlook 15.0 Object Library" Then
wbRefFound = True
Exit For
End If
Next

If wbRefFound = False Then
Dim Reference As Object
Dim OLB As String
Dim Vmajor, Vminor

'Load Microsoft Outlook 12.0 or 14.0 or 15.0 Object Library
OLB14 = "{00062FFF-0000-0000-C000-000000000046}"
Vmajor14 = 9: Vminor14 = 4

OLB12 = "{00062FFF-0000-0000-C000-000000000046}"
Vmajor12 = 9: Vminor12 = 3

If Application.Version = 12 Then
ThisWorkbook.VBProject.References.AddFromGuid OLB12, Vmajor12, Vminor12
Else
ThisWorkbook.VBProject.References.AddFromGuid OLB14, Vmajor14, Vminor14
End If
End If
End Sub

Hope is it work for you. Because it is working on my system. I am using 2013

[AliGW]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)