Using excel VBA to delete outlook emails

sherlock9999

New Member
Joined
Feb 3, 2025
Messages
1
Office Version
  1. 365
Hi,

I have a workbook that uses vba to capture 1 weeks worth of outlook calendar meetings. It does this in the usual way by opening the calendar via an "outlook.application" and using a filter on the week I require to create "PriFilteredItems" which is an "outlook.items" object.

I want to be able to delete meetings that match a certain subject. This all works fine if I delete the meetings in reverse order (Sunday to Monday) if the subject meets the criteria. However, this is very inefficient as I have to loop through all the meetings in the week 7 times.

If I try deleting meetings from Monday to Sunday instead, then it throws out the For Each loop because any deletions at the start of the week means it doesn't complete checks to the end of the week as there are less entries in PriFilteredItems to search through. For example, if PriFilteredItems has 7 meetings to be deleted, one on each day. When I run day1 to day7 then Monday to Thursday is deleted but then the For Each loop finishes checking.

Is there a more efficient way to delete outlook objects in reverse order?. Can PriFilteredItems be forced to keep checking the entire week after entries are deleted from the start of the PriFilteredItems object if I delete from Monday to Sunday ?


for loop1 = day7 to day1

For Each olObject In PriFilteredItems

Set olApt = olObject
MeetStartDate = olApt.Start
MeetSubject = olApt.Subject

If MeetSubject = "meeting no good" then
With olApt
.MeetingStatus = olMeetingCanceled
.Save
.Send
.Delete
End With
End If

Next olObject

next loop1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
However, this is very inefficient as I have to loop through all the meetings in the week 7 times.
I am unclear as to why you think you have to do this. Your sample code doesn't reference the variable loop1 in the loop so I don't see why you are doing the exact same thing seven times.

Also you are using For Each on a collection so the order that you delete things doesn't matter (unlike a situation where, for example, you are deleting rows in an Excel worksheet).

Can you please show your complete code? I highly recommend marking your code with code tags so it's readable (select the code in your post and click the "VBA" button in the edit controls).
 
Upvote 0

Forum statistics

Threads
1,226,463
Messages
6,191,181
Members
453,646
Latest member
BOUCHOUATA

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
Back
Top