Optimize Excel Code to Automatically Delete an Outlook Appointment

drj30026abanba

Board Regular
Joined
Oct 22, 2015
Messages
83
So I currently have code like this to delete an Outlook Appointment from Excel:

Code:
Sub deleteAppointment(ByVal i As Integer, ByVal j As Integer, ByVal oldVal As Date)


Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim subFolder As Outlook.MAPIFolder
Dim objAppointment As Outlook.AppointmentItem
Dim strSubject As String
Dim strBody As String
 
Set objOutlook = Outlook.Application
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
Set subFolder = objFolder.Folders("Second Calendar")
 
strSubject = Cells(i, 1)
strBody = Cells(1, j)


For Each objAppointment In subFolder.Items
  If objAppointment.Subject = strSubject And objAppointment.Body = strBody And _
     objAppointment.Start = oldVal + TimeValue("10:00:00") Then
       objAppointment.Delete
       Exit For
  End If
Next
 


End Sub

The Worksheet is a matrix of appointments. The first column of the Worksheet contains the Subject of the Appointment and the first row of the Worksheet contains the Body of the Appointment. Cell(i,j) is the start date of the Appointment and I made them all start at 10:00AM.

All of that is really irrelevant because I am just concerned about how to search the specific Outlook Calendar folder to see if this Appointment already exists. I know that the loop i created is very inefficient:

Code:
For Each objAppointment In subFolder.Items
  If objAppointment.Subject = strSubject And objAppointment.Body = strBody And _
     objAppointment.Start = oldVal + TimeValue("10:00:00") Then
       objAppointment.Delete
       Exit For
  End If
Next
I think it would be better to use a method like "objAppointment.items.find" but I'm not quite sure on how to use it. I was reading up on the three different search querying languages Outlook has: AQS, DASL, and Jet. I don't know which one is the best for what I'm doing and I don't know how to include multiple criteria on the searches. I want to search the subject AND the body AND the start time but I don't know how. Could someone help me out on this one or at least steer me in the right direction?

Thanks in advance!
 
Last edited:

Forum statistics

Threads
1,082,505
Messages
5,365,968
Members
400,864
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top