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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top