How to remove a list of specific contacts from Outlook

Status
Not open for further replies.

mr_malkovich

Senior Member
Outlook version
Email Account
Exchange Server
Hey all. So I just sent my database of 25k contacts through an email verifier which spat back a list of 500 emails that are invalid. I want to get the corresponding Outlook contact files out of there obviously. Is there a way to do this in any other way but manual? Bonus points if you can also suggest a way for me to get these emails out of any contact groups they may appear in without having to open and inspect them one by one. Thanks as always..
 
I have some macros - Categorize Contacts with bad addresses this one is published and works with NDRs to mark bounced contacts with a category. It shows how to do a lookup using an address - you just need to change the source.

I have another that is not yet published - a client and I worked on it to clear (or update) contacts from a list in a spreadsheet. (Will try to get it published later today.) That might work in your situation. I forget how it handles groups - if he stopped using groups (because he used separate folders for each group, he didn't also need to use groups), but i have macros that work with contacts in groups.

this one - Add a Category to Contacts in a Contact Group (DL) - sets a category on contacts (category name = group name) so you know which groups they are in - that will help if a macro can't be tweaked to remove the names. Run the macro to add the group names as categories then add a 'NDR' category from the list - then manually remove names from groups. Still a little effort, but less than checking every group to see if a contact is on it.
 
The attached Excel macro will find contacts and delete them (or update if the spreadsheet has the address) - this will get you started -
 

Attachments

  • find-NDR-updated.txt
    1.9 KB · Views: 564
Last edited:
I have some macros - Categorize Contacts with bad addresses this one is published and works with NDRs to mark bounced contacts with a category. It shows how to do a lookup using an address - you just need to change the source.

I have another that is not yet published - a client and I worked on it to clear (or update) contacts from a list in a spreadsheet. (Will try to get it published later today.) That might work in your situation. I forget how it handles groups - if he stopped using groups (because he used separate folders for each group, he didn't also need to use groups), but i have macros that work with contacts in groups.

this one - Add a Category to Contacts in a Contact Group (DL) - sets a category on contacts (category name = group name) so you know which groups they are in - that will help if a macro can't be tweaked to remove the names. Run the macro to add the group names as categories then add a 'NDR' category from the list - then manually remove names from groups. Still a little effort, but less than checking every group to see if a contact is on it.


Thanks alot for the macros.

Right now I have a .csv file which contains one column of invalid email addresses. How should I change the macro so that Outlook can read the file source of that .csv file and compare to a contact folder in my Outlook?

From that, I should be able to categorize which email is invalid in one of my Outlook contact folder.
 
assuming you mean the macro in the text file (although it should the same as with others), you should only need to change the sheet name to make it work with a csv. At most, you'll need to save the csv as an excel workbook.
This is what you need to change:
change the sheet name to match your sheet name: Sheets("Sheet1").Select
change the data file to the name that shows in the navigation pane (in 2007 it might be the name, not email address): FolderPath = "alias@domain.com\Contacts"

delete this line:
Set groupFolder = cFolder.Folders(strGroup)

and change the next line to this:
Set myContacts = cFolder.items

during testing, add a ' to the error check so its easier to find errors:
' On Error Resume Next



This will need to be changed too as the original sheet had a value in columns 2 and 5 letting the user know if the address was bad or changed and which folder it was in:
Do Until Trim(Cells(i, 1).Value) = ""
strGroup = Cells(i, 5).Value
Debug.Print strGroup
strGroup = Replace(strGroup, " Grp", "")
Debug.Print strGroup

Assuming column 1 (A) has the address, i think this will work to replace the end of the macro - replace from do until... to the end with this:
Do Until Trim(Cells(i, 1).Value) = ""
strAddress = Cells(i, 1)
Set myItem = myContacts.Find("[Email1Address]=" & strAddress)

If TypeName(myItem) = "ContactItem" Then
If Not TypeName(myItem) = "Nothing" Then
myItem.categories = "Delete"
myItem.Save
End If
End If

i = i + 1
Loop

Set olApp = Nothing

End Sub

make all of the editing in a text file - then open the vba editor in excel (alt+f11) and paste it into the thisworkbook module and run it. CSV files can't store macros so you'll need to paste it in each time you need to do this - if this was just a one-off deal, there is no point in saving it in a workbook - but save the text file in case you need it again.

if you haven't used vba before, you'll need to change macro security to low to use it - excel's file, option, trust center, macro security.
 
assuming you mean the macro in the text file (although it should the same as with others), you should only need to change the sheet name to make it work with a csv. At most, you'll need to save the csv as an excel workbook.
This is what you need to change:
change the sheet name to match your sheet name: Sheets("Sheet1").Select
change the data file to the name that shows in the navigation pane (in 2007 it might be the name, not email address): FolderPath = "alias@domain.com\Contacts"

delete this line:
Set groupFolder = cFolder.Folders(strGroup)

and change the next line to this:
Set myContacts = cFolder.items

during testing, add a ' to the error check so its easier to find errors:
' On Error Resume Next



This will need to be changed too as the original sheet had a value in columns 2 and 5 letting the user know if the address was bad or changed and which folder it was in:
Do Until Trim(Cells(i, 1).Value) = ""
strGroup = Cells(i, 5).Value
Debug.Print strGroup
strGroup = Replace(strGroup, " Grp", "")
Debug.Print strGroup

Assuming column 1 (A) has the address, i think this will work to replace the end of the macro - replace from do until... to the end with this:
Do Until Trim(Cells(i, 1).Value) = ""
strAddress = Cells(i, 1)
Set myItem = myContacts.Find("[Email1Address]=" & strAddress)

If TypeName(myItem) = "ContactItem" Then
If Not TypeName(myItem) = "Nothing" Then
myItem.categories = "Delete"
myItem.Save
End If
End If

i = i + 1
Loop

Set olApp = Nothing

End Sub

make all of the editing in a text file - then open the vba editor in excel (alt+f11) and paste it into the thisworkbook module and run it. CSV files can't store macros so you'll need to paste it in each time you need to do this - if this was just a one-off deal, there is no point in saving it in a workbook - but save the text file in case you need it again.

if you haven't used vba before, you'll need to change macro security to low to use it - excel's file, option, trust center, macro security.


I've tried changing all the required changes. Still, I think my Excel hasn't access my Outlook contact folders. What would be the correct way to input the FolderPath as an Outlook contact folder?

I'm currently using Outlook 2013 and Excel 2013.
 
Are the contacts in your default contacts folder?
 
Are they in your default date file? Switch to the folder list pane (Ctrl+6) and see where the folder is.
This *should* work for any data file, default or not (but there is another way to reference the data data file too).

This is the line that needs changed:
FolderPath = "alias@domain.com\Contacts"

if it's a subfolder of your contacts, use
FolderPath = "alias@domain.com\Contacts\subfoldername"

if its at the same level as contacts, inbox, deleted items etc, use
FolderPath = "alias@domain.com\foldername"
 
For example, i would use
FolderPath = "blank@blank.com\Contacts\No Sync"
to access the subfolder of my contacts folder:
folder-names.png
 
Thanks for that. But it's still not working.
I guess the arrangement of this line is the problem: Set myContacts = cFolder.items

Because when you asked me to replace the Do Until Trim until the end, the line above will be deleted as well. May I know where should this line be?
 
Ah, sorry about that - yeah it looks like i did that wrong (a hazard of telling and not testing the macro myself :( ) - that line should be right before the do until.
 
No problem. Now the macro works fine. But there's one more thing, my .csv file has 530 addresses but I only got 469 addresses in the Delete category.

I've tried changing this part Set myItem = myContacts.Find("[Email1Address]=" & strAddress)

The [Email1Address] to [Email2Address] and [Email3Address]

Also, I've checked out Outlook ContactItem Object properties (ContactItem.Email3Address Property (Outlook)) and it only has these 3 properties for email.

I'm not sure why is that it can't find some of the addresses.
 
Ah, now I got it. It can't find certain addresses that has "." before the "@" in the email address.

For instance, emails like ace.falcon@gmail.com cannot be detected by the macro.

Please advice me.
 
is there more than one address on the contacts that weren't found? This: Email1Address checks the Email1 field. Yoou'll need to reun it using email2address and possibly email3address.
 
is there more than one address on the contacts that weren't found? This: Email1Address checks the Email1 field. Yoou'll need to reun it using email2address and possibly email3address.

Yes, I've tried changing to Email2 and Email3. There are around 60 emails that weren't found. Check out my last reply.

Ah, now I got it. It can't find certain addresses that has "." before the "@" in the email address.

For instance, emails like ace.falcon@gmail.com cannot be detected by the macro.

Please advice me.
 
So all addresses with a dot before @ can't be found?
 
This worked here - "[Email1Address]='" & strAddress & "'" - that is a single ' around the address. The resulting search string was:
[Email1Address]='accounts@slipstick.com'
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
W Remove specific contacts from contact list Outlook VBA and Custom Forms 3
Healy Consultants Macro to remove inside organization distribution list email address when reply to all recepients Outlook VBA and Custom Forms 0
A Is it possible to remove the word Categories: from task list views? Using Outlook 2
C Remove old form-folder-entries from List Using Outlook 5
E Edit incoming emails to remove a certain sentence added by the "system" Using Outlook 1
O VBA - Regex - remove double line spacing Outlook VBA and Custom Forms 1
TomHuckstep Remove Send/Receive All Folders (IMAP/POP) button from Outlook 365 Ribbon Using Outlook 2
Rupert Dragwater How to permanently remove an email address Using Outlook 9
D Auto Remove [EXTERNAL] from subject - Issue with Macro Using Outlook 21
E Remove flag automatically Using Outlook 4
N Can't create NEW GROUP and add/remove a member from existing Group in Outlook Using Outlook 1
Timmon Remove just one attachment before AutoForward Outlook VBA and Custom Forms 0
Z Remove GMAIL IMAP account from Outlook 2016 Using Outlook 2
C-S-R Manage Add-ins (Remove Wunderlist) Using Outlook 6
O Remove duplicates within two accounts Using Outlook 2
D How to remove a folder, option grayed out Using Outlook 4
T Outlook 2016 remove envelope icon for certain folders Using Outlook 5
M In Outlook Calendar remove the buttons: 'Today' and '<' (Back a day) and '>' (Forward a day) that are below the Ribbon and above the calendar display. Using Outlook 0
P [SOLVED] Auto remove [EXTERNAL] from subject Using Outlook 16
P Add, remove, & reorder folder pane Using Outlook 6
T Cannot remove needless PST Using Outlook 1
S Unable to remove rule outlook 2010 Using Outlook 0
N How to remove signature formatting from Text in Word (accidentally taken from Outlook) Using Outlook 0
B Remove Subject Residual Outlook VBA and Custom Forms 3
P how to remove unwanted PST file default categories assigned to many calendar entries Using Outlook 7
J Remove text to Clean Up Outlook VBA and Custom Forms 1
B Automatically Forward Emails and Remove/Replace All or Part of Body Outlook VBA and Custom Forms 8
D Remove text in subject using VBA Outlook VBA and Custom Forms 4
T Remove Old Location From Tasks Pane Using Outlook 1
A remove or turn off outlook.com contact folder from outlook 2016 Using Outlook 4
R Chancing / remove “ something ” in the subject, online archive Outlook VBA and Custom Forms 8
Morgan Fowler Remove Signature Using Outlook 1
R New Links on Navigation Pane, How to Remove? Using Outlook 1
M VBA to remove deferred delivery on a MeetingItem Outlook VBA and Custom Forms 2
J Remove extra line above signature in reply Outlook VBA and Custom Forms 5
Diane Poremsky How to Remove RSS Support from Outlook Using Outlook 0
Diane Poremsky Remove Attachments From Messages Using Outlook 0
Diane Poremsky Remove Office 2013 Update Banner Using Outlook 0
O Remove duplicate mail items Outlook VBA and Custom Forms 6
Diane Poremsky Remove a password from an Outlook *.pst File Using Outlook 3
G VBA/Macro to remove page colour when replying or forwarding email Outlook VBA and Custom Forms 2
L Fake reminder apperaring (not in calendar) - how to remove? Using Outlook 5
J Your IMAP server wants to alert you to the following: cannot remove system folder Using Outlook 3
A Auto Insert of filename when selecting 'Remove Attachment' Using Outlook 1
C how to remove icons on right hand side outlook 2013 Using Outlook 2
K Remove Manage APPS button for users Exchange Server Administration 1
P Remove name and parenthses from email Using Outlook 1
G Outlook calendar entry corrupted. Constant pop up when you open outlook. Unable to delete or remove. Using Outlook 2
Diane Poremsky How to remove the primary account from Outlook 2010/2013 Using Outlook 0
Diane Poremsky Remove an Address from Reply All Using Outlook 0

Similar threads

Back
Top