Appointment Delete/Change Recurrence

Status
Not open for further replies.

JoeG

New Member
Outlook version
Outlook 2016 64 bit
Email Account
POP3
Hello,

I have a client who likes to delete old recurring appointments after the month has passed. Because she handpicks the ones to delete, it would be too large a task (no pun intended) to write a macro to just delete all old appointments from previous months on her Outlook calendar.

Her current solution is to select a recurring appointment on the calendar and then open it and reset the start date to the new month (which effectively removes all the old appointments prior to that date.)

One requirement is that only the currently-selected (highlighted) appointment in her calendar is the one she wants to clear previous entries for.

I thought of several approaches to automating this task which included:
1) Finding all occurrences prior to the new start date she picks (the pattern-start-date) and deleting those.
2) Mimicking her process with code by changing the pattern start date to the first day of the next month and saving the changes.
3) Using send-keys to open the dialog box and mimic her keystrokes.
4) Copying the appointment to a new recurring appointment with a different start date and then deleting the old recurring appointment.

I settled on item #2. I got the code to work (some of the time) and that is where I'm stuck.

Let's say her recurring appointment is a daily appointment to go to the store to open the doors for the customers at 9:00 am. This recurring appointment started on 2/15/18 and shows up on her calendar each day at 9:00 am as 'Open Storefront Doors". Now that it's March, she will want to change the pattern start date to 3/1/18. So all the February and older dates will disappear.

When I test the code (listed below), I select one of the February appointments and then run the macro and it successfully changes the pattern start date to 3/1/18. No more items exist in February. When I run it a second time by selecting one of the dates in March, it moves the start date to April 1st. So far, so good. However, when I try to change April to May 1st, the program fails on the Save method. The error is something like, "you changed one of the recurrences and this item no longer exists".

Anyone have some thoughts on what I'm missing here? The weird thing is sometimes the Save method is needed and other times it isn't. (When the code is run, note that we are not opening the recurring appointment but merely highlighting it.)

Here's the basic code. I know there are a few things such as error handling, etc. missing but this is the core routine:

Option Explicit
Public Sub DeleteSelectedRecurringAppointments()

Dim olApp As Outlook.Application
Dim olItem As Object
Dim oAppointment As AppointmentItem
Dim oPattern As RecurrencePattern
Dim olSel As Outlook.Selection
Dim fFound As Boolean
Dim sAppointmentOldPatternStartDate
Dim sAppointmentNewPatternStartDate
Dim sAppointmentDate
Dim sAppointmentSubject
Dim sAppointmentLocation
Dim sAppointmentDetails
Dim x As Long
Set olApp = CreateObject("outlook.application")
Set olSel = olApp.ActiveExplorer.Selection
fFound = False
For Each olItem In olSel
If olItem.Class = olAppointment Then
fFound = True
Set oAppointment = olItem
Set oPattern = oAppointment.GetRecurrencePattern
If oAppointment.IsRecurring Then
fFound = True
With oAppointment
If .Subject <> "" Then
sAppointmentSubject = "Subject: " + .Subject + vbCr
End If
If Not IsNull(.Start) And .Start <> 0 Then
sAppointmentDate = "Appointment Date: " + CStr(Format(.Start, "ddddd h:nn AMPM")) + vbCr
End If
If Not IsNull(oPattern.PatternStartDate) And oPattern.PatternStartDate <> 0 Then
sAppointmentOldPatternStartDate = "Old Series Start Date: " + CStr(Format(oPattern.PatternStartDate, "ddddd h:nn AMPM")) + vbCr
End If
If Not IsNull(oPattern.PatternStartDate) And oPattern.PatternStartDate <> 0 Then
sAppointmentNewPatternStartDate = "New Series Start Date: " + CStr(Format(GetNextMonthFirstDay(oPattern.PatternStartDate), "ddddd h:nn AMPM")) + vbCr
End If
If .Location <> "" Then
sAppointmentLocation = "Location: " + .Location + vbCr
End If
If Trim(.Body) <> "" And Len(.Body) < 2 Then
sAppointmentDetails = "Details: " + Trim(.Body) + vbCr
End If
x = MsgBox("Move up this selected recurring appointment?" + vbCr + vbCr + sAppointmentSubject + sAppointmentDate + sAppointmentLocation + sAppointmentDetails + vbCr + sAppointmentOldPatternStartDate + sAppointmentNewPatternStartDate, vbYesNoCancel + vbQuestion + vbDefaultButton2)
Select Case x
Case vbYes
oPattern.PatternStartDate = Format(GetNextMonthFirstDay(oPattern.PatternStartDate), "ddddd h:nn AMPM")
.Save
Case vbCancel
End
End Select
End With
End If
End If
Next olItem
If fFound = False Then
MsgBox "No appointments were selected.", vbOKOnly + vbInformation
End If
Exit Sub
ErrHandler:
MsgBox Err.Description
End
Return
End Sub
Public Function GetNextMonthFirstDay(dtmDate As Date) As Date

Dim iOldMonth As Integer
Dim iNewMonth As Integer
Dim iOldYear As Integer
Dim iNewYear As Integer

iOldMonth = Month(dtmDate)
iOldYear = Year(dtmDate)

If iOldMonth = 12 Then
iNewMonth = 1
iNewYear = iOldYear + 1
Else
iNewMonth = iOldMonth + 1
iNewYear = iOldYear
End If

GetNextMonthFirstDay = CDate(CStr(iNewMonth) + "/01/" + CStr(iNewYear))

End Function
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
G How can I delete a private appointment in shared calendar in public folders? Using Outlook 1
F Outlook 2003: Set up of new appointment. The Location: drop down menu holds old location we want to delete. Using Outlook 4
K Re: ItemRemove event not fire when delete recurring appointment Outlook VBA and Custom Forms 8
D Prevent popup of "Do you want to save changes?" when closing after opening an appointment to view Outlook VBA and Custom Forms 2
O Calendar - appointment templates and categories Using Outlook 1
W Appointment userproperties disappear Outlook VBA and Custom Forms 4
Nessa Can't create new appointment Using Outlook 1
F Appointment Show All Fields Using Outlook 1
C Trying to populate an appointment ComboBox from Excel Outlook VBA and Custom Forms 2
A Possible to hide ribbon with custom appointment form? Outlook VBA and Custom Forms 3
W Appointment occurrences change the location property Using Outlook 0
W Space in an Outlook appointment body Using Outlook 0
Dave A Run macro on existing appointment when it changes Outlook VBA and Custom Forms 1
S Display PF contact folder items to select contact to link to appointment Outlook VBA and Custom Forms 1
M Forward Appointment as BCC with VBScript Outlook VBA and Custom Forms 7
K Update Appointment category when changed in Excel Using Outlook 3
S View Appointment in Text Wrap in Outlook 2007 Month Calendar View Using Outlook 0
A Day view - print appointment details Calendar Printing Assistant 1
R Recover Deleted Appointment in Calendar Using Outlook 0
N Select Appointment subject line from combobox or list Outlook VBA and Custom Forms 1
S Appointment-Cannot set Categories because ConversationID is not set Outlook VBA and Custom Forms 1
D Record Appointment to Calendar on "Public Folder" Outlook VBA and Custom Forms 13
G Copy Contact field to Appointment Custom Form Field Outlook VBA and Custom Forms 2
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
S Appointment colour categories disappear Using Outlook 4
G Using Data From Combo Box in Appointment Body Outlook VBA and Custom Forms 6
C Set reminder / appointment by right clicking email Using Outlook 1
A Add attachments to appointment based on field values Outlook VBA and Custom Forms 0
S how to set user properties to a newly created appointment Outlook VBA and Custom Forms 12
Y Creating custom appointment request form with multiple mail recipients Outlook VBA and Custom Forms 5
S my vbscript button1_click code works on appointment created but not on opening an existing apntmn Outlook VBA and Custom Forms 16
G Create an Appointment at the Contact's Address From Email Outlook VBA and Custom Forms 0
Diane Poremsky Create Task or Appointment and Insert Selected Text New Slipstick.com Articles 0
Pierce007 Maps/location in making an appointment Using Outlook 2
A Get shared calendar name or id in custom appointment form Using Outlook 0
Diane Poremsky Create Appointment From Email Automatically New Slipstick.com Articles 0
C Reminder for single-click appointment Using Outlook 2
P Appointment times are off by one minute Using Outlook 1
M receive mail when appointment category changes and create task from appointment Outlook VBA and Custom Forms 0
B VBA Code to create appointment from email Outlook VBA and Custom Forms 1
Diane Poremsky Create an Outlook appointment from an email message New Slipstick.com Articles 4
Diane Poremsky Create an Appointment Diary New Slipstick.com Articles 0
N Adding Appointment Item in Outlook to Shared Calendar Folder Outlook VBA and Custom Forms 7
Diane Poremsky Send an Email When You Add an Appointment to Your Calendar New Slipstick.com Articles 0
J Outlook calendar...How to add an event or appointment without a border around it? Using Outlook 9
F Outlook 2010 Search Appointment Notes Using Outlook 0
D Calendar appointment, type 2 Using Outlook 2
Diane Poremsky Use VBA to get an Appointment's Time Zone New Slipstick.com Articles 0
C Autofill subject line in appointment from options selected in combobox Using Outlook 6
J Create an appointment from Contact list with the Company name in the Subject line Using Outlook 1
Similar threads


















































Top