?
Solved

VBA code to delete specific calendar events

Posted on 2013-05-21
1
Medium Priority
?
1,077 Views
Last Modified: 2013-05-24
I have the following lines of code that I am trying to use to remove specific calendar events prior to adding the updated events back using an sql server recordset.  When I run the code, it will delete some of the events, but I have to run it several times to delete all of the events. How can I change this code to delete ALL events in one code run that meet the subject criteria?

Dim myStart, myEnd As Date
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oResItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
  myStart = Date - 1
    myEnd = Date + 10
   

    Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
      strRestriction = "[Start] <= '" & myEnd & "' AND [End] >= '" & myStart & "'"
      Set oResItems = oItems.Restrict(strRestriction)
    For Each oAppt In oResItems
     ' delete PTO entries
       If InStr(1, oAppt.Subject, "PTO:", vbTextCompare) > 0 Then
            oAppt.Delete
        End If

    Next
0
Comment
Question by:BradleyCleveland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 39187036
Hi, BradleyCleveland.

There are two reasons the code would miss some appointments.  First, when deleting items you can't use a "FOR EACH" loop.  When you delete an item it changes the position of the items that appear after it.  This results in items being skipped.  Instead, you need to work backwards, starting at the end of the list and working toward its beginning.  To do this you use a "FOR x = COUNT OF ITEMS IN THE LIST TO 1 STEP -1".  Second, when dealing with appointments you have to consider recurring items.  To get the recurring items you need to sort the list by the start date and set IncludeRecurrences to True.  I've modified the code to do both.  Please give it a try and let me know if that does it for you.

Sub DeleteAppointments()
    Dim myStart, myEnd As Date
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oResItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    Dim intCnt As Integer
    myStart = Date - 1
    myEnd = Date + 10
    Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
    strRestriction = "[Start] <= '" & myEnd & "' AND [End] >= '" & myStart & "'"
    Set oResItems = oItems.Restrict(strRestriction)
    oResItems.Sort "[Start]"
    oResItems.IncludeRecurrences = True
    For intCnt = oResItems.Count To 1 Step -1
        Set oAppt = oResItems.Item(intCnt)
        ' delete PTO entries
        If InStr(1, oAppt.Subject, "PTO:", vbTextCompare) > 0 Then
            oAppt.Delete
        End If
    Next
End Sub

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

800 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