Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

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.OlItemType.olAppointmentItem)
      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
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Murray Brown

ASKER

thanks very much
Glad to help, thank's for the prompt response.

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
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
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