Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA code to delete specific calendar events

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
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…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

610 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