CODEWITHSUNDEEP
excelvbaoutlooklate-binding
Adstanley84
Adstanley84

Reputation: 33

Late binding for non version specific Excel VBA code

I have an Excel spreadsheet that my VBA code pulls from to create appointments on a shared Outlook calendar. It will be manipulated by multiple project managers (not at the same time of course).

I found a couple of users have and older version of Office and as such I have learned I should be using "late binding" to make this compatible with older versions.

How do I convert what I have to late binding. All of the examples that I had to create this code was early binding.

Option Explicit
    
Sub SCHMTG() 'Schedule Meeting
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Set ws = wb.Sheets("Projects")
    
    ws.Unprotect ""
    
    Dim check As Boolean
    check = False
    
    Dim o As Outlook.Application
    Set o = New Outlook.Application
    
    Dim oNS As Outlook.Namespace
    Set oNS = o.GetNamespace("MAPI")
    
    Dim FOL As Outlook.MAPIFolder
    Set FOL = oNS.GetFolderFromID("00000000F4EFC638C1F878469E872F63F51D794A0100F96BCFC3DAF87B4F8C66193C3EA6F4F40000029DA2430000")
    
    Dim oAPT As Outlook.AppointmentItem
    Dim oAPT_DATE As Date
    Dim oAPT_TIME As Date
    Dim oOBJECT As Object
    Dim b As CheckBox
    Dim r As Integer
    Dim c As Integer
    
    Set b = ws.CheckBoxes(Application.Caller)
    With b.TopLeftCell
        r = .Row
        c = .Column
    End With
    
    For Each oAPT In FOL.Items 'Search for existing meeting
                
        oAPT_DATE = Format(oAPT.Start, "MM-DD-YYYY")
        oAPT_TIME = TimeValue(oAPT.Start)
        
        If oAPT_DATE = ws.Cells(r, c - 3).Value And oAPT.Subject = ws.Cells(r, 1).Value And oAPT_TIME = ws.Cells(r, c - 2).Value Then
            check = True
        Else
        End If
    
    Next oAPT
    
    If check = False Then 'If no meeting already exist Then create new meeting
        Set oAPT = FOL.Items.Add(olAppointmentItem)
        With oAPT
            .Start = ws.Cells(r, c - 3).Value + ws.Cells(r, c - 2).Value
            .Duration = ws.Cells(r, c - 1).Value * 60
            .Subject = ws.Cells(r, 1).Value & " " & ws.Cells(1, c).Value
            .Body = "Project: " & ws.Cells(r, 1).Value & vbCrLf & "Location: " & ws.Cells(r, 2) & vbCrLf & "OASIS#: " & ws.Cells(r, 3) & vbCrLf & "Project Manager: " & ws.Cells(r, 5) & vbCrLf & "Distributor: " & ws.Cells(r, 8) & vbCrLf & "Assigned Technitian: " & ws.Cells(r, c - 5) & vbCrLf & "Date: " & ws.Cells(r, c - 3) & vbCrLf & "Start Time: " & Format(ws.Cells(r, c - 2), "h:mm am/pm") & vbCrLf & "Duration: " & ws.Cells(r, c - 1) & " Hour(s)"
            .Location = ws.Cells(r, 2).Value
            .Recipients.Add Cells(r, c - 4).Value
            .MeetingStatus = olMeeting
            .ReminderMinutesBeforeStart = 1440
            .Save
            .Send
        End With
        ws.Cells(r, c - 1).Locked = True
        ws.Cells(r, c - 2).Locked = True
        ws.Cells(r, c - 3).Locked = True
        ws.Cells(r, c - 5).Locked = True
    Else
    End If
    
    ws.Cells(r, 1).Locked = True
    ws.Cells(r, 2).Locked = True
    ws.Cells(r, 3).Locked = True
    
    ws.Protect "", True, True
    
End Sub

Upvotes: 1

Views: 909

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Remove any VBA project references to the Outlook library.

Declare any Outlook objects As Object instead of using the Outlook library typename. Use CreateObject() in place of New

eg:

Dim oAPT As Outlook.AppointmentItem
Dim o As Outlook.Application
Set o = New Outlook.Application

would be 

Dim oAPT As Object
Dim o As Object
Set o = CreateObject("Outlook.Application")

Any Outlook-derived constants such as olAppointmentItem should either be declared as Constants in your code, or replaced by their numeric values (which you can find using the Object Browser)

Upvotes: 1

Related Questions