Converting Code to Late Binding

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
139
Hi All,

I haven't had reason to convert code to late binding previously. This has just changed though with an ongoing deployment of Office 2013 across the company I work for and resultant problems with vba libraries.

I have some code which is part of a system for electronically signing forms, this uses outlooks directory to provide a name and job title from the windows login name / email alias. The code in question is in two parts, one which checks that outlook is open and one which does the work if it is.

The code to check that outlook is open is:
Code:
Private Sub TestOutlookIsOpen()

    Dim oOutlook As Object

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If oOutlook Is Nothing Then
        MsgBox "Outlook is not open, open Outlook and try again"
    Else
        Call GetOutlookProperties
    End If

End Sub

If I remove the Outlook and VBA Extensibility references the above works fine. Would it be correct therefore to assume that I dont need to change any of that or should 'GetObject(, "Outlook.Application")' change?

The code which does the work is:
Code:
Private Sub GetOutlookProperties()
MsgBox "You may see an Outlook security warning" & vbCrLf & _
    "displayed in the next step." & vbCrLf & _
    vbCrLf & _
    "If so please tick the box 'Allow access for'" & vbCrLf & _
    "and click the 'Allow' button"

Dim i As Integer
Dim ToAddr As String
Dim ErrMsg As String
Dim CRLF As String
Dim DistOption As Integer
Dim ClassID As Long
Dim ManagerVerified As Boolean
Dim ActivePersonVerified As Boolean
Dim ol As Outlook.Application
Dim DummyEmail As MailItem
Dim myInspector As Inspector
Dim ActivePersonRecipient As Recipient
Dim oAE As Outlook.AddressEntry
Dim oExUser As Outlook.ExchangeUser
Dim oPA As Outlook.PropertyAccessor

Dim RowsInRange As Integer

CRLF = Chr(13)
ErrMsg = ""
'Instantiate Outlook
Set ol = CreateObject("Outlook.Application")

'Create a dummy e-mail to add aliases to
Set DummyEmail = ol.CreateItem(olMailItem)
RowsInRange = 1

    ToAddr = Environ("UserName")

    'Use the alias to create a recipient object and add it to the dummy e-mail
    Set ActivePersonRecipient = DummyEmail.Recipients.Add(ToAddr)

    ActivePersonRecipient.Type = olTo
    'Resolve the recipient to ensure it is valid
    ActivePersonVerified = ActivePersonRecipient.Resolve
    'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
    If ActivePersonVerified Then
        Set oAE = ActivePersonRecipient.AddressEntry
        'Use the GetExchangeUser method of the AddressEntry object to retrieve the ExchangeUser object for the recipient

        Set oExUser = oAE.GetExchangeUser

        SignatorySurnameFirstName = oExUser.Name
        SignatoryJobTitle = oExUser.JobTitle
        SignatoryFirstNameSurname = oExUser.FirstName & " " & oExUser.LastName
        SignatoryEmailAddress = oExUser.PrimarySmtpAddress
        SignatoryPhone = oExUser.BusinessTelephoneNumber
        SignatoryCompany = oExUser.CompanyName

    Set oPA = Nothing
    Set oAE = Nothing
    Set oExUser = Nothing
Else
End If
On Error GoTo 0

    'Remove the recipient from the e-mail
    ActivePersonRecipient.Delete
    Set ActivePersonRecipient = Nothing
'Next I

ExitOutlookEmail:
    Set DummyEmail = Nothing
    Set ol = Nothing
    Set oPA = Nothing
    Set oAE = Nothing
    Set oExUser = Nothing
    Call FindApproverNameField
    Exit Sub

PropertyFail:
    Debug.Print Err.Number, Err.Description
    Debug.Print ExtendedProperty, PropertyIdentifier
    'Set the value of the ExtendedPropertyValue variable to the ExtendedProperty variable set just before the GetProperty method is called
    'This results in the phrase "Extended Property n" being written to the cell where the property value was supposed to be written
    ExtendedPropertyValue = ExtendedProperty
    Resume Next

End Sub

I was hoping someone who is more familiar than me with late binding (in other words more familiar than not at all!) might be able to advise what changes I should make to the above to convert to late binding and remove the requirement for the additional references libraries?

Best regards & many thanks in advance
Richard
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The first part is fine. Try this for the second part:
Code:
Private Sub GetOutlookProperties()
    Dim i                     As Integer
    Dim ToAddr                As String
    Dim ErrMsg                As String
    Dim CRLF                  As String
    Dim DistOption            As Integer
    Dim ClassID               As Long
    Dim ManagerVerified       As Boolean
    Dim ActivePersonVerified  As Boolean
    Dim ol                    As Object
    Dim DummyEmail            As Object
    Dim myInspector           As Object
    Dim ActivePersonRecipient As Object
    Dim oAE                   As Object
    Dim oExUser               As Object
    Dim oPA                   As Object
    Dim RowsInRange           As Integer

    Const olMailItem          As Long = 0
    Const olTo                As Long = 1

    MsgBox "You may see an Outlook security warning" & vbCrLf & _
           "displayed in the next step." & vbCrLf & _
           vbCrLf & _
           "If so please tick the box 'Allow access for'" & vbCrLf & _
           "and click the 'Allow' button"

    CRLF = Chr(13)
    ErrMsg = ""
    'Instantiate Outlook
    Set ol = CreateObject("Outlook.Application")

    'Create a dummy e-mail to add aliases to
    Set DummyEmail = ol.CreateItem(olMailItem)
    RowsInRange = 1

    ToAddr = Environ("UserName")

    'Use the alias to create a recipient object and add it to the dummy e-mail
    Set ActivePersonRecipient = DummyEmail.Recipients.Add(ToAddr)

    ActivePersonRecipient.Type = olTo
    'Resolve the recipient to ensure it is valid
    ActivePersonVerified = ActivePersonRecipient.Resolve
    'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
    If ActivePersonVerified Then
        Set oAE = ActivePersonRecipient.AddressEntry
        'Use the GetExchangeUser method of the AddressEntry object to retrieve the ExchangeUser object for the recipient

        Set oExUser = oAE.GetExchangeUser

        SignatorySurnameFirstName = oExUser.Name
        SignatoryJobTitle = oExUser.JobTitle
        SignatoryFirstNameSurname = oExUser.FirstName & " " & oExUser.LastName
        SignatoryEmailAddress = oExUser.PrimarySmtpAddress
        SignatoryPhone = oExUser.BusinessTelephoneNumber
        SignatoryCompany = oExUser.CompanyName

        Set oPA = Nothing
        Set oAE = Nothing
        Set oExUser = Nothing
    Else
    End If
    On Error GoTo 0

    'Remove the recipient from the e-mail
    ActivePersonRecipient.Delete
    Set ActivePersonRecipient = Nothing
    'Next I

ExitOutlookEmail:
    Set DummyEmail = Nothing
    Set ol = Nothing
    Set oPA = Nothing
    Set oAE = Nothing
    Set oExUser = Nothing
    Call FindApproverNameField
    Exit Sub

PropertyFail:
    Debug.Print Err.Number, Err.Description
    Debug.Print ExtendedProperty, PropertyIdentifier
    'Set the value of the ExtendedPropertyValue variable to the ExtendedProperty variable set just before the GetProperty method is called
    'This results in the phrase "Extended Property n" being written to the cell where the property value was supposed to be written
    ExtendedPropertyValue = ExtendedProperty
    Resume Next

End Sub
 

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
139
Thanks very much indeed for that Rory!

Will add that in and let you know how it goes.

Best regards
Richard
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,108,805
Messages
5,524,979
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top