Multiple problems with macro to delete emails from Outlook Inbox

[DavidSCowan]

I am using Office 2016 and from Excel and am trying to write a procedure to delete specific emails from my Inbox. I have written it to just delete one sender with the aim of looping through a list of senders once this is working. The macro is supposed to move the designated email to the trash and once moved to be deleted. However, the code below throws up a number of problems.

Sub DeletingEmailsFromSpecificSenders()

Dim ol As Outlook.Application

Dim ns As Outlook.Namespace

Dim fol As Outlook.Folder

Dim i As Object

Dim mi As Outlook.MailItem

Dim n As Long



Set ol = New Outlook.Application

Set ns = ol.GetNamespace("MAPI")

Set fol = ns.GetDefaultFolder(olFolderInbox)



    For Each i In fol.Items

        If i.Class = olMail Then           

            Set mi = i  'doing this provides access to the intellisense

                If mi.SenderEmailAddress = "boxoffice@bridgetheatre.co.uk" Then

                               

                    Set mi = mi.Move(ns.GetDefaultFolder(olFolderDeletedItems)) 

                    mi.Delete

                                   

                    n = n + 1

                End If

        End If

    Next i

MsgBox "The number of this item to delete is " & n

End Sub

This works after a fashion – some of the emails are moved to Trash but there are a number of problems:

• None of the emails moved to trash are deleted i.e. deleted from Trash
• There were four emails from the example sender but only two were moved to Trash. The ones that had attachments were not moved (and this has been replicated with other senders).

Can anyone please explain how these two problems can be overcome.

• There is an additional problem too. Searching the Inbox for the sender that was supposed to be moved indicates that there are still the original four in the Inbox. However, ordering the emails “By From” and scrolling down shows, correctly, that there are only two still in the Inbox (the two that have attachments). How can I get the search facility to yield the correct answer? I understand that this is a 2016 problem which earlier versions don’t suffer from. Can anyone help with this too please.
  
  
  Thank you in advance.
  
  David

 

[Worf]

Please test this:

Sub PermDelete()
Dim myOlApp As Outlook.Application, ns As NameSpace, i As Object
Dim DelFolder As Folder, fol As Folder, items As Outlook.items, j%
Set myOlApp = Outlook.Application
Set ns = myOlApp.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderInbox)
For Each i In fol.items
    If i.Class = olMail Then
        If i.To Like "*ron*" Then
            i.Categories = "ForDeletion"
            i.Save
            i.Delete
        End If
    End If
Next
Set DelFolder = ns.GetDefaultFolder(olFolderDeletedItems)
For Each i In DelFolder.items
    If i.Class = olMail Then
        If i.To Like "*ron*" And i.Categories = "ForDeletion" Then i.Delete
    End If
Next
End Sub

 

[DavidSCowan]

Hi Worf

I had given up hope of getting any response so thank you very much for getting back to me and sorry for the delay.

I am afraid the procedure doesn't work. I ran it on several email senders and have checked that they are still in the Inbox and checked on Thunderbird just in case! Nothing in Trash either.

Thanks again for taking the trouble

David

 

[Worf]

My code is just an example; it looks for a string at the recipient field.

You have to adapt it to your specific needs.

I wrote it that way because at home I do not have Outlook configured, so it has a limited number of messages.

 

[DavidSCowan]

Yes, I understand that or maybe I don't!

When you wrote: If i.To Like "*ron*" Then

I inserted the SenderName of an example email I wanted to delete e.g. The Other Palace

So I wrote:

If i.To Like "*The Other Palace*" Then

That's what you meant wasn't it?

Although to be honest I don't understand the i.To

 

[Worf]

Therefore, you have to check the sender address for the desired string. See below information on the property I used in my example.

Post back if you still have doubts.

MailItem.To Property (Outlook)
Returns or sets a semicolon-delimited String list of display names for the To recipients for the Outlook item. Read/write.

Syntax
expression . To

expression A variable that represents a MailItem object.

Remarks
This property contains the display names only. The To property corresponds to the MAPI property PidTagDisplayTo . The Recipients collection should be used to modify this property.

 

[DavidSCowan]

Dear Worf

Thank you very much for sticking at this.

I tested out SenderEmailAddress's in your procedure. In one instance *orders@onslp.email* but I am afraid it didn't delete the items with this address. I tried it out on other addresses too.

I am new to using VBA in Outlook. I had checked out the VBA reference for MailItem.To that you quoted above but with my state of understanding it doesn't make much sense to me.

And if I am honest I don't really understand the whole section of code:

If i.To Like "*ron*" Then
i.Categories = "ForDeletion" (I presume this is somehow tagging the items)
i.Save (the item is saved with this tag?)
i.Delete (Now it is deleted?)

Once again thank you for putting in the effort.

David

 

[Worf]

Let’s focus on the first step which is moving the items to the trash folder. The test code below worked for me.

You should check for the sender address instead.

' Outlook module
Sub PermDelete()
Dim myOlApp As Outlook.Application, ns As NameSpace, i As Object
Dim DelFolder As Folder, fol As Folder, items As Outlook.items, j%
Set myOlApp = Outlook.Application
Set ns = myOlApp.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderInbox)
MsgBox fol.items.Count, , "Before"
For Each i In fol.items
    If i.Class = olMail Then
        If i.SentOnBehalfOfName Like "*sales*" Then
            i.Categories = "ForDeletion"            ' flag it
            i.Save
            i.Delete                                ' move to deleted folder
        End If
    End If
Next
DoEvents
MsgBox fol.items.Count, , "After"
End Sub

 

[DavidSCowan]

Hi Worf

Thank you very much for your last post but I am afraid the code doesn't work for me. It doesn't move emails in my Inbox to Trash. The original code I posted used the move method and did move emails to Trash but the problem was that once there MailItem.delete didn't delete.

If you have any suggestions of how to permentally delete once in Trash they would be gratefully received.

 

[Worf]

This worked for me:

' Outlook module
Sub PermDelete()
Dim myOlApp As Outlook.Application, ns As NameSpace, i As Object
Dim DelFolder As Folder, fol As Folder, items As Outlook.items
Set myOlApp = Outlook.Application
Set ns = myOlApp.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderDeletedItems)
MsgBox fol.items.Count, , "Before"
For Each i In fol.items
    If i.Class = olMail Then
        ' change this for the property you want to check
        If i.SentOnBehalfOfName Like "*sales*" Then i.Delete
    End If
Next
DoEvents
MsgBox fol.items.Count, , "After"
End Sub