Using excel VBA to delete outlook emails

[sherlock9999]

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

 

[6StringJazzer]

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).

 

[sherlock9999]

Hi, sorry for the confusion, I was trying to show an example of the code as it is spread over multiple subs and forms. I was able to find a solution by using this format:

    For MeetingItem = MeetingCount To 1 Step -1
       Set olApt = PriFilteredItems.Item(MeetingItem)

Many thanks.