none
Access & Outlook Appointment VBA question regarding appointments RRS feed

  • Question

  • Hello,

    I have the following code and it's working well for creating appointments. However I'd like it to either modify an existing appointment with the same Subject value or delete the previous appointment and recreate using the new data when a user updates the date value of Me.FollowUpDate I have not been able to figure out how to accomplish this and any assistance would be appreciated. Edit: Also if they make the value equal to null or "" I'd like to remove the appointment as well. I'm not sure what settings I'd need to add for it to do that and I've not had much luck googling the outlook vba.

    Thanks!


    Private Sub FollowUpDate_AfterUpdate()
    
    If IsNull(Me.FollowUpDate.Value) Then
    
    Else
    
    
      Dim olApp As Outlook.Application
                Dim olApt As AppointmentItem
                Set olApp = New Outlook.Application
                Set olApt = olApp.CreateItem(olAppointmentItem)
            
                With olApt
                    .Start = Me.FollowUpDate.Value
                    .AllDayEvent = True
                    .Subject = Me.EU_Num.Value
                    .Body = "AED Readiness Call"
                    .BusyStatus = olFree
                    .ReminderSet = False
                    .Save
                    
                End With
                       
                Set olApt = Nothing
                Set olApp = Nothing
                
    End If
    
    End Sub
    



    • Edited by Kerensky18 Wednesday, February 10, 2016 6:18 PM
    Wednesday, February 10, 2016 6:08 PM

Answers

  • Something like:

    Private Sub FollowUpDate_AfterUpdate()
        Dim olApp As Outlook.Application
        Dim olApt As AppointmentItem
        Dim oCalendar As Outlook.Folder
        
        Set olApp = New Outlook.Application
        Set oCalendar = olApp.session.GetDefaultFolder(olFolderCalendar)
        
        For Each olApt In oCalendar.Items
            If olApt.Subject = Me.EU_Num.Value Then
                olApt.Delete
                If IsNull(Me.FollowUpDate.Value) Then Exit Sub
                GoTo CreateAppt
            End If
        Next olApt
            
    CreateAppt:
        
        Set olApt = olApp.CreateItem(olAppointmentItem)
            
        With olApt
            .Start = Me.FollowUpDate.Value
            .AllDayEvent = True
            .Subject = Me.EU_Num.Value
            .Body = "AED Readiness Call"
            .BusyStatus = olFree
            .ReminderSet = False
            .Save
        End With
        
        Set olApt = Nothing
        Set olApp = Nothing
        
    End Sub

    • Marked as answer by Kerensky18 Wednesday, February 10, 2016 9:57 PM
    Wednesday, February 10, 2016 8:38 PM

All replies

  • Something like:

    Private Sub FollowUpDate_AfterUpdate()
        Dim olApp As Outlook.Application
        Dim olApt As AppointmentItem
        Dim oCalendar As Outlook.Folder
        
        Set olApp = New Outlook.Application
        Set oCalendar = olApp.session.GetDefaultFolder(olFolderCalendar)
        
        For Each olApt In oCalendar.Items
            If olApt.Subject = Me.EU_Num.Value Then
                olApt.Delete
                If IsNull(Me.FollowUpDate.Value) Then Exit Sub
                GoTo CreateAppt
            End If
        Next olApt
            
    CreateAppt:
        
        Set olApt = olApp.CreateItem(olAppointmentItem)
            
        With olApt
            .Start = Me.FollowUpDate.Value
            .AllDayEvent = True
            .Subject = Me.EU_Num.Value
            .Body = "AED Readiness Call"
            .BusyStatus = olFree
            .ReminderSet = False
            .Save
        End With
        
        Set olApt = Nothing
        Set olApp = Nothing
        
    End Sub

    • Marked as answer by Kerensky18 Wednesday, February 10, 2016 9:57 PM
    Wednesday, February 10, 2016 8:38 PM
  • Thanks :) That works great.

    Do you know a good resource I can use to find how to do things like this 

        Set oCalendar = olApp.session.GetDefaultFolder(olFolderCalendar)

    I see once I get that set in Access helps with the rest.

    Wednesday, February 10, 2016 9:58 PM
  • I like to search the MSDN knowledge base, so I add that to my search term, like

    outlook calendar delete appointment VBA MSDN

    The top returns are either from the knowledge base or the forums, which are also a good resource.

    Wednesday, February 10, 2016 10:07 PM