[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA code to delete specific calendar events

Posted on 2013-05-21
1
Medium Priority
?
1,203 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

830 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