Deleting mails from shared mailbox, one at a time, is very slow

Question

I have a macro in Excel to delete mails older than 30 days from folders and subfolders in a shared mailbox. I have a sub for looping through each subfolder and a separate sub to delete mails from each subfolder

Private Sub deletemails(ByVal tgtfolder as outlook.mapifolder)
    set olitems = tgtfolder.items.restrict("[SentOn] <='" & (Date -30) & "'")
    filtercount = olitems.count

    If filtercount > 0 Then
        On Error Resume Next
        For i=filtercount To 1 Step -1
            olitems.item(i).Delete
        Next i
    End If
End Sub

For deleting 13000 mails (around 15 subfolders) it took 30 hrs and during this time Outlook is very slow (not able to read mails or any action). And getting pop up sometimes like 'microsoft excel is waiting for another ole action to complete'. Now I have 'Ignore other DDE action' in settings.

Need a better approach to delete mails from shared mailbox.

Answer 1

That's pretty much the best you can do using the Outlook Object Model - you do the right thing and avoid looping through all items in a folder, but OOM won't let you delete multiple items in a single call - Extended MAPI (C++ or Delphi) does allow that.

If you using Redemption (I am its author - any language) is an option, it exposes the RDOItems.RemoveMultiple method, that takes an array of entry ids.

Sub deletemails(tgtfolder)
    set App = CreateObject("Outlook.Application")
    set Session = CreateObject("Redemption.RDOSession")
    Session.MAPIOBJECT = App.Session.MAPIOBJECT
    set rFolder = Session.GetRDOObjectFromOutlookObject(tgtfolder)
    d = Date - 30
    'we need the standard SQL yyyy-mm-dd format 
    set rs = rFolder.items.MAPITable.ExecSQL("SELECT EntryID From Folder Where SentOn <='" & Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2) & "'")
    dim entryIds()
    redim entryIds(rs.RecordCount)
    i = 0
    while not rs.EOF
      entryIds(i) = rs.Fields(0).Value
      rs.MoveNext
      i = i + 1
    wend
    rFolder.Items.RemoveMultiple(entryIds)
End Sub

You might want to make App and Session global variables and initialize them only once.