Need help converting early binding to late binding with excel to outlook vba

[adstanley84]

So over the past two weeks, I have taught myself a decent amount of VBA in order to create a spreadsheet for my department. The basic idea of the excel is to take user entered information then with the check of a box have excel, through VBA, create or cancel a meeting on a shared outlook calendar. I have gotten this to work perfectly at my workstation and at other workstations with the same version of excel. However, the issue I now have is we have a few users with older versions of Excel and they are unable to use the spreadsheet due to the missing outlook reference library. I have read that I need to use "Late Binding" to remove its dependency on the Outlook Reference, but I cannot find enough information in a format that is easy enough for me to understand that would allow me to stumble my way though this. So now I am asking for some community help to push me across the finish line!

I have a module that creates my check boxes and assigns them on action to call a module TASKSCHEDULER that determines which box was activated and if the box is checked or unchecked then calls a module that creates or cancel a meeting.

This is the module that creates the meeting. If I can get it working I can resolve the others on my own.

Option Explicit
Public Sub SCHMTG() 'Schedule Meeting


Dim wb As Workbook
Set wb = ThisWorkbook


Dim ws As Worksheet
Set ws = wb.Sheets("Projects")


ws.Unprotect vbNullString


Dim check As Boolean
check = False


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


Dim oNS As Object
Set oNS = o.GetNamespace("MAPI")


Dim FOL As Object
Set FOL = oNS.GetFolderFromID("00000000F4EFC638C1F878469E872F63F51D794A0100F96BCFC3DAF87B4F8C66193C3EA6F4F40000029DA2430000")


Dim oAPT As Object
Dim oAPT_DATE As Date
Dim oAPT_TIME As Date
Dim b As CheckBox
Dim r As Long
Dim c As Long


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")    'When I remove the Outlook reference Format flags an error
    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)    'Not sure how to late bind this line
        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 (ws.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 vbNullString, True, True


End Sub

 

[adstanley84]

sorry, I managed to figure out my hang up. Here is my revised code in case it helps anyone

Option Explicit
Public Sub TASKSCHEDULER()


Dim wb As Workbook
Set wb = ThisWorkbook


Dim ws As Worksheet
Set ws = wb.Sheets("Projects")


Dim b As CheckBox
Dim r As Long
Dim c As Long
Set b = ws.CheckBoxes(Application.Caller)
    With b.TopLeftCell
        r = .Row
        c = .Column
    End With


Dim pcmt As String
Dim pcmd As String
Dim pcmst As String
Dim pcmdur As String


If IsEmpty(ws.Cells(r, c - 5)) Then 'VerIfy data has been entered into all fields needed - works
    pcmt = "Technician" & vbCrLf
Else
    pcmt = vbNullString
End If
If IsEmpty(ws.Cells(r, c - 3)) Then
    pcmd = "Date" & vbCrLf
Else
    pcmd = vbNullString
End If
If IsEmpty(ws.Cells(r, c - 2)) Then
    pcmst = "Start Time" & vbCrLf
Else
    pcmst = vbNullString
End If
If IsEmpty(ws.Cells(r, c - 1)) Then
    pcmdur = "Duration" & vbCrLf
Else
    pcmdur = vbNullString
End If 'End field verIfy


If b.Value = 1 Then
    If IsEmpty(ws.Cells(r, c - 5)) Or IsEmpty(ws.Cells(r, c - 3)) Or IsEmpty(ws.Cells(r, c - 2)) Or IsEmpty(ws.Cells(r, c - 1)) Then 'Call out missing fields If present
        MsgBox "Missing Fields: " & vbCrLf & vbCrLf & pcmt & pcmd & pcmst & pcmdur
        b.Value = 0
    Else
        SCHMTG     'Schedule Meeting
    End If
Else
    CNCLMTG   'Cancel Meeting
End If


End Sub

 

[adstanley84]

Figured out what would work:

Option Explicit
Public Sub SCHMTG() 'Schedule Meeting


Dim wb As Workbook
Set wb = ThisWorkbook


Dim ws As Worksheet
Set ws = wb.Sheets("Projects")


ws.Unprotect vbNullString


Dim check As Boolean
check = False


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


Dim oNS As Object
Set oNS = o.GetNamespace("MAPI")


Dim FOL As Object
Set FOL = oNS.GetFolderFromID("00000000F4EFC638C1F878469E872F63F51D794A0100F96BCFC3DAF87B4F8C66193C3EA6F4F40000029DA2430000")


Dim oAPT As Object
Dim oAPT_DATE As Date
Dim oAPT_TIME As Date
Dim b As CheckBox
Dim r As Long
Dim c As Long


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


Const olAppointmentItem = 1
Const olMeeting = 5


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 (ws.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 vbNullString, True, True


End Sub