Excel to Outlook Question/Code

[zoog25]

Hello All,

Here is my questions / situation. I'm currently working on an excel spreadsheet that has a date of when a document is filed. Once the date is entered, i have a macro that inputs an appointment into my outlook calendar.

Now here is what i need to do. Based on information located on the sheet, i need to have it add the appointment to a shared calendar called "NOC Calendar". The second thing is, based on who is assigned to the document. Can a reminder just be set for that person? Not sure how it is done.

Here is the code i'm using:

Option Explicit


Sub AddToOutlook(rw)




Dim OL As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim NS As Outlook.Namespace
Dim oFolder As Outlook.Folder
Dim oRecipient As Outlook.Recipient
Dim sSubject As String, sBody As String, sLocation As String
Dim dStartTime As Date, dEndTime As Date
Dim bOLOpen As Boolean
Dim TPws As Worksheet


Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


'Checks to see if Outlook is open and either open and closes it or leaves it open
On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
bOLOpen = True




If OL Is Nothing Then
    Set OL = CreateObject("Outlook.Application")
        bOLOpen = False
End If


'Labor and Materials Appointment Set


sSubject
sLocation = Desk
    
dStartTime = TPws.Cells(rw, "AB").Value + #8:00:00 AM#
dEndTime = TPws.Cells(rw, "AB").Value + #8:30:00 AM#


Set olAppt = OL.CreateItem(olAppointmentItem)
    olAppt.Subject = sSubject
    olAppt.Location = sLocation
    olAppt.Start = dStartTime
    olAppt.End = dEndTime
    olAppt.ReminderSet = True
    olAppt.MeetingStatus = olMeeting
    olAppt.RequiredAttendees = "xxxx@xxxxx.com"
    olAppt.Send
    olAppt.Close olSave
     


If bOLOpen = False Then OL.Quit




End Sub

Now this code is based on an old project that i worked on that i have modified so i'm not sure if the code works or not but i wanted to see how one i assign the appointments to the "NOC Calendar" and then just set the reminder to one person.

The person's name is on corresponding column "B".

Thank you for any help.