Update Outlook Calendar from Access table

Posted on 2009-05-23
Medium Priority
Last Modified: 2012-05-07

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
   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
      End With
   End With
   ' Clean up.
   Set olkAppt = Nothing
End Sub
Question by:Murray Brown
  • 4
  • 2
LVL 59

Accepted Solution

Chris Bottomley earned 2000 total points
ID: 24460773
Hello murbro,

You could add the following sub and call it with the strBookingID .

Sub calDelete(strSubjectStarter As String)
Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
Dim olkApp As object
Dim olkNS As object
Dim myfolder As object
Dim olkCalitems As object
Dim itemCount As Integer
Dim appt As object
Dim strFilter As String
    On Error Resume Next
    Set olkApp = createobject("Outlook.Application")
    Set olkNS = olkApp.GetNamespace("MAPI")
    Set myfolder = olkNS.Session.GetDefaultFolder(9)
    strFilter = "@SQL=" & Chr(34) & PropTag _
        & "0x0037001E" & Chr(34) & " like " & Chr(39) & strSubjectStarter & "%" & Chr(39)
    Set olkCalitems = myfolder.items.Restrict(strFilter)
    itemCount = olkCalitems.count
    For Each appt In olkCalitems
Set olkCalitems = Nothing
Set olkNS = Nothing
Set olkApp = Nothing
Set myfolder = Nothing
End Sub

Open in new window


Author Closing Comment

by:Murray Brown
ID: 31584787
thanks very much
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24460937
Glad to help, thank's for the prompt response.

Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

LVL 59

Expert Comment

by:Chris Bottomley
ID: 24460964

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?


Author Comment

by:Murray Brown
ID: 24461344
Hi Chris. My mistake. I shouldn't have deleted it. I have reposted it at the link below.

LVL 59

Expert Comment

by:Chris Bottomley
ID: 24461490
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.


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Office 365 has multiple features and services which are specially designed to help businesses to reduce their dependence on on-premises IT resources. It also offers great flexibility and enhanced security. But like any other data, Office 365 mailbo…
If there is anything erroneous with Exchange Database, it causes a significant effect on email communication till the user remounts the database. Further, database crash directly affects Outlook users due to which they are unable to access their ema…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question