Macro skips emails it is supposed to delete

[musicgold]

Hi,

I use the following code to clean my Outlook inbox. However, I have noticed that sometimes it doesn’t delete all the emails it should delete in the first sweep. It however deletes them in the second or third sweep. Why does it happen?

Dim oloutlook As Outlook.Application
Dim ns As Outlook.NameSpace
Dim itm As Object
Dim Myitem As MailItem
Dim counter As Integer
Dim olTrash As Object
 
Set oloutlook = CreateObject("Outlook.Application")
Set ns = oloutlook.GetNamespace("MAPI")
Set itm = ns.GetDefaultFolder(olFolderInbox)
Set olTrash = ns.GetDefaultFolder(olFolderDeletedItems)
 
 
For Each MyItem In itm.Items
 
  Select Case UCase(MyItem.SenderName)
 
  Case UCase("ABCD")
        MyItem.Move olTrash
        counter = counter + 1
 
  Case UCase("EFGH")
        MyItem.Move olTrash
        counter = counter + 1
 
  End Select
 
Next
…

Question cross posted at:
http://www.vbaexpress.com/forum/showthread.php?t=40759

 

[Macropod]

Hi musicgold,

You probably need to process the items via their index #, starting at the last item and working your way down. Try something like:

Dim oloutlook As Outlook.Application
Dim ns As Outlook.NameSpace
Dim itm As Object
Dim i As Long
Dim counter As Integer
Dim olTrash As Object
Set oloutlook = CreateObject("Outlook.Application")
Set ns = oloutlook.GetNamespace("MAPI")
Set itm = ns.GetDefaultFolder(olFolderInbox)
Set olTrash = ns.GetDefaultFolder(olFolderDeletedItems)
For i = itm.Items.Count To 1 Step -1
  With itm.Items(i)
    Select Case UCase(.SenderName)
      Case "ABCD", "EFGH"
          .Move olTrash
    End Select
  End With
Next

Note too how your Select Case statement can be simplified.

 

[musicgold]

Macropod,

Thank you. It works fine. A couple of questions though.

1. Was my code not going through each item?

2. Though I have not done extensive testing, it seems that your code is taking double the time my macro used to take. Is it possible? I have about 700 items in my inbox.

3. Case statement - does your case statement make processing faster than mine or is it just a simpler way of writing? I am also curious as to how your case statement is different from a nested if statement.

Thank you.

 

[Macropod]

Hi musicgold,

With a For Each loop, if you delete an item, the 'next' item becomes the current item and gets skipped over by the Next statement. So, if there are two consecutive items that should be deleted, the second one doesn't get processed.

Unfortunately, iterating through a collection with a counter takes longer than using a For Each loop (and going backwards is perhaps even slower), but that's the price one has to pay sometimes. Also, part of the reason the code I posted takes longer is that it is actually processing every record instead of skipping some ...

My version of the Select Case statement is simpler than yours, and may be faster. My understanding is that, when you have many items to test, Select Case is also more efficient than a plethora of ElseIf staements in a If .. ElseIf .. ElseIf .. End If construct, let alone a nested If (which serves an entirely different purpose.

 

[musicgold]

Thanks Macropod

 

[musicgold]

With a For Each loop, if you delete an item, the 'next' item becomes the current item and gets skipped over by the Next statement. So, if there are two consecutive items that should be deleted, the second one doesn't get processed.

This problem also occurs in Excel when one tries to delete multiple rows based on a condition. The workaround to that problem is to use the For Each loop only to add those rows to an union and then delete the whole union. Can something like that be done here?

Thanks.

 

[Macropod]

Hi musicgold,

AFAIK, the Outloook object model has nothing equivalent to Excel's Union. You might be able to gain some speed improvement by sorting the emails by sender, then having found the first email of interest, using a While .. Wend loop do keep deleting until they're all deleted. I don't have any experience with Outlook to say whether this would work, though.

 

[Domenic]

Try using the Restrict method to apply a filter to the Items collection...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] MyOutlook [color=darkblue]As[/color] Outlook.Application
    [color=darkblue]Dim[/color] MyNamespace [color=darkblue]As[/color] Outlook.Namespace
    [color=darkblue]Dim[/color] MyFolder [color=darkblue]As[/color] Outlook.MAPIFolder
    [color=darkblue]Dim[/color] MyItems [color=darkblue]As[/color] Outlook.Items
    [color=darkblue]Dim[/color] MyRestrictedItems [color=darkblue]As[/color] Outlook.Items
    [color=darkblue]Dim[/color] MyItem [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] MyTrash [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] MyOutlook = CreateObject("Outlook.Application")
    
    [color=darkblue]Set[/color] MyNamespace = MyOutlook.GetNamespace("MAPI")
    
    [color=darkblue]Set[/color] MyFolder = MyNamespace.GetDefaultFolder(olFolderInbox)
    
    [color=darkblue]Set[/color] MyTrash = MyNamespace.GetDefaultFolder(olFolderDeletedItems)
    
    [color=darkblue]Set[/color] MyItems = MyFolder.Items
    
    [color=darkblue]Set[/color] MyRestrictedItems = MyItems.Restrict("[From] = 'ABCD' Or [From] = 'EFGH'")
    
    [color=darkblue]For[/color] i = MyRestrictedItems.Count [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
        MyRestrictedItems(i).Move MyTrash
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[musicgold]

Thanks Macropod.

Domenic,
That works perfectly. Thanks.

 

[Domenic]

You're very welcome! Thanks for the feedback!