Large Excel list of various email addresses that need to be deleted from Outlook

[tommyclearyuk]

Hi All,

I have a large list of email addresses in Excel, I need to delete (ideally to the Delete folder) or remove the emails present in the list from my Outlook Inbox.

I've searched the web to see if I can find a vba routine but have been unsuccessful.

I'd appreciate any assistance.

Many thanks,
Tom

FYI: Using Office 365

 

[Domenic]

The following code assumes...

1) The workbook running the macro contains your list of email addresses.

2) Sheet1 contains your list of email addresses.

3) Column A, starting at A1, contains your list of email addresses.

First, set the following reference (Visual Basic Editor >> Tools >> References)...

Microsoft Outlook xx.x. Object Library

Then, place the following code into a regular module (Insert >> Module)...

Option Explicit

Sub MoveEmailsToDeletedItemsFolder()

    On Error GoTo errorHandler

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
   
    Dim EmailsToDelete As Variant
    Dim lastRow As Long
    With sourceWorksheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        EmailsToDelete = .Range("A1:A" & lastRow).Value
    End With

    Dim olApp As outlook.Application
    Set olApp = New outlook.Application
   
    Dim olNS As outlook.Namespace
    Set olNS = outlook.GetNamespace("MAPI")
   
    Dim olInbox As outlook.Folder
    Set olInbox = olNS.GetDefaultFolder(olFolderInbox)
   
    Dim olDeletedItemsFolder As outlook.Folder
    Set olDeletedItemsFolder = olNS.GetDefaultFolder(olFolderDeletedItems)
   
    Dim olItem As Variant
    Dim olMailItem As outlook.MailItem
    Dim deletedEmailCount As Long
    Dim i As Long
    deletedEmailCount = 0
    With olInbox
        For i = .Items.Count To 1 Step -1
            If .Items(i).Class = olMail Then
                Set olMailItem = .Items(i)
                If Not IsError(Application.Match(olMailItem.SenderEmailAddress, EmailsToDelete, 0)) Then
                    olMailItem.Move olDeletedItemsFolder
                    deletedEmailCount = deletedEmailCount + 1
                End If
            End If
        Next i
    End With
   
    MsgBox "Emails moved to deleted items folder: " & deletedEmailCount, vbInformation
   
exitHandler:
    Set olApp = Nothing
    Set olNS = Nothing
    Set olInbox = Nothing
    Set olDeletedItemsFolder = Nothing
    Set olMailItem = Nothing
   
    Exit Sub
   
errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Resume exitHandler
   
End Sub

Hope this helps!

 

[tommyclearyuk]

Hi Domenic,

Many thanks for taking the time to resolve, however I'm encountering an issue when running the code.

Basically I :
1) Created a folder titled 'email addresses 3.'
2) Created one file within called email addresses.xlsx.
3) Pasted a list of 31058 rows of email addresses including duplicated emails, approx 1000 unique email addresses are present.
4) Navigated the Developer/Visual Basic/Tools/References path
5) Checked the Microsoft Office 16.0 Object Library
6) Selected OK
7) Navigated the Insert/Module path
8) Copied the supplied code above (from Option Explicit to End Sub)
9) Pressed f5 to execute code

This is where I encounter the issue with the very first sub routine, highlighted in yellow, there is a Dim variable highlighted in blue (DIM olApp As outlook.Application) with the following error message appearing, 'Compile Error: User defined type
not defined.' This error box has an OK or Help option.

I would appreciate any further assistance you could provide.
Many thanks,
Tom

 

[tommyclearyuk]

Just to add, email addresses were added to 'Sheet1' worksheet.

+ Included two attachments of Compiler and Excel worksheet.

 

[Domenic]

As I mentioned in my post, you'll need to set a reference to Outlook's object library. Sorry, I should have made it more clear. You'll need to first go to the Visual Basic Editor (Alt+F11), and then select References under Tools. Then, scroll all the way down until you see Microsoft Outlook xx.x. Object Library. Then, select it and click OK.

 

[tommyclearyuk]

Hi Domenic,

As per my previous note, in the steps 4 & 5, haven't I already done this albeit not via Alt+F11?

PFA, screen shot of Reference selection.

Let me know if I'm being stupid here, I may be missing a trick.

Many thanks,
Tom

 

[Domenic]

You've actually set a reference to the wrong library. You've set a reference to the Office library, instead of the Outlook library. So it should be...

Microsoft Outlook xx.x. Object Library

Sorry I didn't catch it earlier, but I was in a rush and quickly scanned your post, and then I saw that error, and proceeded from there.

Let me know if you have any other issues or questions.

Cheers!

 

[tommyclearyuk]

Hi Domenic,

Apologies, it's been a long few days and my attention span isn't at it's best.

Just ran again as per the above instruction, routines seems to work at first glance with pop up messages confirming how many emails have been moved however the sample of 20 emails addresses I have used in Sheet1 (including some duplicates) are not being moved and the subsequent pop up message states as, 'Emails moved to deleted items folder: 0.'

I've attached a screen print, any ideas whats preventing the move email to deleted routine?

Many thanks again,
Tom

 

[Domenic]

Your last image still shows that you've set a reference to the Office library, not the Outlook library. I'm assuming that you did set one to the Outlook library, otherwise you would have gotten that same error. Did you in fact set a reference to the Outlook library?

In the meantime, I'll look at this closely, in a short while.

 

[Domenic]

Can you post the exact code you're using?