Murray Brown
asked on
Update Outlook Calendar from Access table
Hi
I am using the following code to update my Outlook calendar at the dates in my Access table.
I use the unique ID record in the subject. How would I update the code to firt delete any appointment with this ID
Sub LoadCalendarItems()
Dim OlkApp As Outlook.Application
Dim strSubject As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strDate As String
Dim strTime As String
Dim strEndDate As String
Dim strBookingID As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Bookings ")
Set OlkApp = New Outlook.Application
With rst
Do Until .EOF
strDate = .Fields("Arrival Date")
strTime = .Fields("Guest Arrival Time")
strEndDate = .Fields("Departure Date")
strSubject = .Fields("Guest Name")
strBookingID = .Fields("ID")
CreateCalendarItem OlkApp, strSubject, strDate, strTime, strEndDate, strBookingID
.MoveNext
Loop
.Close
End With
' Clean up.
Set OlkApp = Nothing
Set rst = Nothing
Set db = Nothing
End Sub
Sub CreateCalendarItem(OlkApp As Outlook.Application, _
strSubject As String, strDate As String, _
strTime As String, strEndDate As String, strBookingID As String)
Dim olkAppt As Outlook.AppointmentItem
Dim dte As Date
Dim EndDate As Date
' Must add Outlook to references for this to work
dte = CDate(strDate & " " & strTime)
EndDate = CDate(strEndDate & " " & "09:00:00 AM")
With OlkApp
Set olkAppt = .CreateItem(Outlook.OlItem Type.olApp ointmentIt em)
With olkAppt
.Start = dte
.End = EndDate
.Subject = strBookingID & " " & strSubject
.ReminderSet = False
olkAppt.Save
End With
End With
' Clean up.
Set olkAppt = Nothing
End Sub
I am using the following code to update my Outlook calendar at the dates in my Access table.
I use the unique ID record in the subject. How would I update the code to firt delete any appointment with this ID
Sub LoadCalendarItems()
Dim OlkApp As Outlook.Application
Dim strSubject As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strDate As String
Dim strTime As String
Dim strEndDate As String
Dim strBookingID As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Bookings
Set OlkApp = New Outlook.Application
With rst
Do Until .EOF
strDate = .Fields("Arrival Date")
strTime = .Fields("Guest Arrival Time")
strEndDate = .Fields("Departure Date")
strSubject = .Fields("Guest Name")
strBookingID = .Fields("ID")
CreateCalendarItem OlkApp, strSubject, strDate, strTime, strEndDate, strBookingID
.MoveNext
Loop
.Close
End With
' Clean up.
Set OlkApp = Nothing
Set rst = Nothing
Set db = Nothing
End Sub
Sub CreateCalendarItem(OlkApp As Outlook.Application, _
strSubject As String, strDate As String, _
strTime As String, strEndDate As String, strBookingID As String)
Dim olkAppt As Outlook.AppointmentItem
Dim dte As Date
Dim EndDate As Date
' Must add Outlook to references for this to work
dte = CDate(strDate & " " & strTime)
EndDate = CDate(strEndDate & " " & "09:00:00 AM")
With OlkApp
Set olkAppt = .CreateItem(Outlook.OlItem
With olkAppt
.Start = dte
.End = EndDate
.Subject = strBookingID & " " & strSubject
.ReminderSet = False
olkAppt.Save
End With
End With
' Clean up.
Set olkAppt = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help, thank's for the prompt response.
Chris
Chris
murbro,
Just went to post my answer to your question on current appt rather than delete ... it's gone, I guess you solved it for yourself but I have the solution as I did it anyway if you want it?
chris_bottomley
Just went to post my answer to your question on current appt rather than delete ... it's gone, I guess you solved it for yourself but I have the solution as I did it anyway if you want it?
chris_bottomley
ASKER
Hi Chris. My mistake. I shouldn't have deleted it. I have reposted it at the link below.
Thanks
Murray
https://www.experts-exchange.com/questions/24434216/Outlook-Edit-a-calendar-item-with-a-certain-header.html
Thanks
Murray
https://www.experts-exchange.com/questions/24434216/Outlook-Edit-a-calendar-item-with-a-certain-header.html
I've posted an answer there. It may not be perfect for what you want but I presume you will indicate there how you would like it to work.
Chris
Chris
ASKER