Link to home
Start Free TrialLog in
Avatar of ITPro44
ITPro44Flag for United States of America

asked on

Script a way to Delete Contacts within Oulook

I would like assistance creating a script that can delete certain contacts within Outlook's default contact folder.  We need to delete all contacts that have an email address with a specified domain and leave all other contacts alone.  Thanks in advance.
Avatar of Bill Prew
Bill Prew

There are several email addresses that a contact can have, which one(s) do you want to check?

~bp
Avatar of ITPro44

ASKER

I don't really understand what you mean.  Email, Email2, Email3?  If that is the case then, we just need to check the "Email" field.
Also, is this a one time need, or something you need to run over and over again, or on many computers?  If it was a one time need, then you just use Outlook Search Tool and look for the contacts with an email containing the text involved, and then highlight them all in the results list, and delete them.

~bp
Yes, that was what I meant on email fields.

~bp
Avatar of ITPro44

ASKER

yes, we'll be running this across all computers within our company which is why it will require a script.  Can you help?
SOLUTION
Avatar of David Lee
David Lee
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes.  I would say a VBS script outside of Outlook may be the easiest to "deploy" and execute.  The alternative is a VBA procedure, but that would need to be loaded into each stations Outlook before execution, which is a bit harder.  I will put together a VBS approach for you to test.

~bp
Actually it looks like David has already worked that up, give it a try and see if that meets your needs.  You would save that as a .VBS file and run it either by just clicking on it, or in a command line or BAT script as:

cscript //nologo EE28942837.vbs

~bp
Avatar of ITPro44

ASKER

Thanks David.  I ran it and get the following "Success" message, but none of the contacts actually get deleted.  User generated image
Did you notice the following lines, and did you change it to TRUE?
'On the next line set the value to True to delete the accounts with a matching domain.  A value of False will cause the solution to display the number of accounts that would be deleted without actually deleting anything.
Const PRODUCTION_MODE = False

Open in new window

~bp
Avatar of ITPro44

ASKER

oh, perfect!  that did the trick.  One that modification request.  Can you change it so the name of the contacts folder is a variable and listed at the top of the script.  This would allow us to enter "Contacts" if we wanted this to run on the default contacts folder and replace it with the name of another folder when necessary.
That's a bit more involved, but doable.  I'm sure David can handle that, but will keep my eye on this question in case he can't get to it.

~bp
Unless your plan is to restrict usage to the default contacts folder and any sub-folders, then a folder name won't work.  There could be any number of folders with any given name making it impossible for the solution to know which one you want to process.  You'll need a folder path or I can modify the solution to display a select folder dialog-box allowing you to select the folder visually.
Avatar of ITPro44

ASKER

I'm not sure I understand.  The script should only ever be run on one contacts folder at a time.  I'm asking for that contacts folder to be able to be specified so that we can change which one it is run on.
Folder names aren't unique. There can be any number of contacts folders with any given name. Say that you enter "Company Contacts" as the folder to process and there are three contacts folders with that name. How would the script know which of the three you want it to run against?
Avatar of ITPro44

ASKER

Contact Folder names do appear to be unique.  That said, maybe I should have said, yes to your initial comment of:

"Unless your plan is to restrict usage to the default contacts folder and any sub-folders, then a folder name won't work. "

I do plan to restrict it's usage to the default contacts folder and any sub-folders.  

User generated image
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ITPro44

ASKER

That's great!  Thank you for your prompt help on this David!  You do great work!
Avatar of ITPro44

ASKER

Thanks again!
Thanks and you're welcome!
Avatar of ITPro44

ASKER

ok, so I learned that I will need a modification made to this script.

What I learned was that the contacts actually have an "EX" Email Type instead of an "SMTP" email type and they were not being deleted because of this.  Their email is actually a different address and does not inlclude an @c  Please see the picture below.  Can you modify the script to accommodate this?  We could either delete all EX email types or modify the variable so that it could accmodate any type of text you insert.  i.e. delete */o=ExchangeLabs/* or *@domain.com*.  Can you help with this?

User generated image
The screenshot isn't taken from Outlook.  That's a screenshot from AD or some Exchange management tool.  Look at the contacts in Outlook.  Do you see any that have the EX style address?  If you don't, and I don't think you will, then then the change you're talking about is pointless as the script would never encounter a contact with that type of address.
Avatar of ITPro44

ASKER

I exported the contact list to a csv file.  Outlook masks that information within the client but that is the raw data.
Avatar of ITPro44

ASKER

Can you modify the script to accommodate this?
I don't want to spend time writing code to do something that my not be necessary.  Look at the the email addresses in Outlook.  If you don't see any with an EX style address, then modifying the script is pointless.  Keep in mind that the script is looking at Outlook contacts.  It's not looking at a distribution list.
Avatar of ITPro44

ASKER

No, outlook does not display the EX path in the "E-mail Address".  It displays the normal email address.  But that's the thing, just because it's displaying this information this way doesn't mean that's what the information actually is (it must be doing some sort of lookup to display it in a readable way).  I demonstrated what the actual information is by exporting it to a CSV.  The data in the field called "E-mail Address" is actually an EX path.  The only place the regular email address exists in that CSV file is within the "E-Mail Display Name" field.  It shows it int he format "First Last (FLast@domain.com)"

Can you create the script so that it's wildcard based, meaning anything entered as the variable, if it exists anywhere in the "Email-Address" field it will delete it?  Right now the script requires an @ symbol and only looks to a subset of the information in the "Email-Address" field.
I demonstrated what the actual information is by exporting it to a CSV.

As I noted in post #41579448, that screenshot isn't from Outlook.  It's from something else.  Just because an EX address appears in some other source doesn't mean the same address is being used in the Outlook contact.  What did you export that CSV from?
Avatar of ITPro44

ASKER

I exported that CSV from outlook.  I exported all contacts into CSV form.
I think this should do it.  I changed the code to handle multiple domains instead of a single domain.  Edit the DOMAINS_TO_DELETE constant at the top of the code so it includes all the SMTP domains and/or X.400 domains you want to remove.  Each entry in that list must be separated by the | character.  

'On the next line enter the folder name to delete from.  The solution expects this folder to exist one level below the default Contacts folder.
Const FOLDER_NAME = "Testing"
'On the next line enter the email domain of the accounts you want to delete.  Be sure to include the @
Const DOMAINS_TO_DELETE = "@looneytunes.com|/o=ExchangeLabs/"
'On the next line set the value to True to delete the accounts with a matching domain.  
Const PRODUCTION_MODE = True
Const SCRIPT_NAME = "Delete Contacts from Specific Domain (v1.0)"
Const olFolderContacts = 10
Const olContact = 40

Dim olkApp, olkSes, olkFld, olkItm, intPtr, intCnt, strTmp, arrTmp, varTmp

On Error Resume Next

intCnt = 0

Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNameSpace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

If FOLDER_NAME = "Contacts" Then
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts)
Else
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts).Folders(FOLDER_NAME)
End If
If IsEmpty(olkFld) Then
    MsgBox "Operation cancelled.  The folder " & FOLDER_NAME & " does not exist.", vbCritical+vbOkOnly, SCRIPT_NAME
Else
    For intPtr = olkFld.Items.Count to 1 Step -1
        Set olkItm = olkFld.Items(intPtr)
        If olkItm.Class = olContact Then
            strTmp = LCase(olkItm.Email1Address & "|" & olkItm.Email2Address & "|" & olkItm.Email3Address)
            arrTmp = Split(DOMAINS_TO_DELETE, "|")
            For Each varTmp in arrTmp
                If InStr(1, strTmp, varTmp) > 0 Then 
                    If PRODUCTION_MODE then olkItm.Delete
                        intCnt = intCnt + 1
                        Exit For
                    End If
                End If
            Next
        End If
    Next
    MsgBox "Deleted " & intCnt & " items from the " & FOLDER_NAME & " folder.", vbInformation+vbOkOnly, SCRIPT_NAME
End If

olkSes.Logoff
Set olkItm = Nothing
Set olkFld = Nothing
Set olkSes = Nothing
Set olkApp = Nothing

Open in new window

Avatar of ITPro44

ASKER

thanks, however, I'm now getting an error message.  Also, can this script work on the default contacts folder or only subfolders under the contacts folder?\

User generated image
Looks like I added an unnecessary END IF.  Please try this version.

Yes, the code can handle the default contacts folder or any folder under it.  If you want to run it against the default contacts folder, then set FOLDER_NAME to "Contacts".  

'On the next line enter the folder name to delete from.  The solution expects this folder to exist one level below the default Contacts folder.
Const FOLDER_NAME = "Testing"
'On the next line enter the email domain of the accounts you want to delete.  Be sure to include the @
Const DOMAINS_TO_DELETE = "@looneytunes.com|/o=ExchangeLabs/"
'On the next line set the value to True to delete the accounts with a matching domain.  
Const PRODUCTION_MODE = True
Const SCRIPT_NAME = "Delete Contacts from Specific Domain (v1.0)"
Const olFolderContacts = 10
Const olContact = 40

Dim olkApp, olkSes, olkFld, olkItm, intPtr, intCnt, strTmp, arrTmp, varTmp

On Error Resume Next

intCnt = 0

Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNameSpace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

If FOLDER_NAME = "Contacts" Then
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts)
Else
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts).Folders(FOLDER_NAME)
End If
If IsEmpty(olkFld) Then
    MsgBox "Operation cancelled.  The folder " & FOLDER_NAME & " does not exist.", vbCritical+vbOkOnly, SCRIPT_NAME
Else
    For intPtr = olkFld.Items.Count to 1 Step -1
        Set olkItm = olkFld.Items(intPtr)
        If olkItm.Class = olContact Then
            strTmp = LCase(olkItm.Email1Address & "|" & olkItm.Email2Address & "|" & olkItm.Email3Address)
            arrTmp = Split(DOMAINS_TO_DELETE, "|")
            For Each varTmp in arrTmp
                If InStr(1, strTmp, varTmp) > 0 Then 
                    If PRODUCTION_MODE then olkItm.Delete
                    intCnt = intCnt + 1
                    Exit For
                End If
            Next
        End If
    Next
    MsgBox "Deleted " & intCnt & " items from the " & FOLDER_NAME & " folder.", vbInformation+vbOkOnly, SCRIPT_NAME
End If

olkSes.Logoff
Set olkItm = Nothing
Set olkFld = Nothing
Set olkSes = Nothing
Set olkApp = Nothing

Open in new window

Avatar of ITPro44

ASKER

thanks David.  It runs but says "Deleted 0 items from the contacts folder.  It does not delete any of the contacts with an "/o=ExchangeLabs/..." address.
There are two possibilities.

  1. The folder the code is looking at is the wrong folder.  Check the folder name.
  2. The folder is correct, but none of the contacts in the folder have an email address containing any of the domains in the list.

The code works properly here.  I can't test the "/o=ExchangeLabs/..." part because I don't have any contacts with that form of address and when I enter an address of that form into a contact, Outlook ignores it as if it doesn't exist.
Avatar of ITPro44

ASKER

1. I've confirmed that the folder its looking is correct.  I know this because it will delete addresses with the specified @domain.com address.  

2. I can also confirm that folder has plenty of addresses with the  "/o=ExchangeLabs/..." format.

Let me see if I can get you a contact with that info so you can test it
Avatar of ITPro44

ASKER

Ok, attached is a csv file that you can import that has the ExchangeLaps format.
TestContact.CSV
It's a case sensitivity issue.  The address in that contact contains "/o=exchangelabs/" while the address the code is looking for is "/o=ExchangeLabs/".  We can handle this in either of two ways.  I can modify the code to make the comparison case insensitive or you can adjust the address accordingly.  Which do you prefer?
Avatar of ITPro44

ASKER

That was it!!!  For future use, it would be very helpful to have this not be case sensitive.  Can you please modify the code?
Change made.

'On the next line enter the folder name to delete from.  The solution expects this folder to exist one level below the default Contacts folder.
Const FOLDER_NAME = "Testing"
'On the next line enter the email domain of the accounts you want to delete.  Be sure to include the @
Const DOMAINS_TO_DELETE = "@looneytunes.com|/o=ExchangeLabs/"
'On the next line set the value to True to delete the accounts with a matching domain.  
Const PRODUCTION_MODE = True
Const SCRIPT_NAME = "Delete Contacts from Specific Domain (v1.0)"
Const olFolderContacts = 10
Const olContact = 40

Dim olkApp, olkSes, olkFld, olkItm, intPtr, intCnt, strTmp, arrTmp, varTmp

On Error Resume Next

intCnt = 0

Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNameSpace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

If FOLDER_NAME = "Contacts" Then
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts)
Else
    Set olkFld = olkSes.GetDefaultFolder(olFolderContacts).Folders(FOLDER_NAME)
End If
If IsEmpty(olkFld) Then
    MsgBox "Operation cancelled.  The folder " & FOLDER_NAME & " does not exist.", vbCritical+vbOkOnly, SCRIPT_NAME
Else
    For intPtr = olkFld.Items.Count to 1 Step -1
        Set olkItm = olkFld.Items(intPtr)
        If olkItm.Class = olContact Then
            strTmp = LCase(olkItm.Email1Address & "|" & olkItm.Email2Address & "|" & olkItm.Email3Address)
            arrTmp = Split(DOMAINS_TO_DELETE, "|")
            For Each varTmp in arrTmp
                If InStr(1, LCase(strTmp), LCase(varTmp)) > 0 Then 
                    If PRODUCTION_MODE then olkItm.Delete
                    intCnt = intCnt + 1
                    Exit For
                End If
            Next
        End If
    Next
    MsgBox "Deleted " & intCnt & " items from the " & FOLDER_NAME & " folder.", vbInformation+vbOkOnly, SCRIPT_NAME
End If

olkSes.Logoff
Set olkItm = Nothing
Set olkFld = Nothing
Set olkSes = Nothing
Set olkApp = Nothing

Open in new window

Avatar of ITPro44

ASKER

Thank you so much David.  I really appreciate it.  Great work!
Thanks and you're welcome!
Avatar of ITPro44

ASKER

So, I'm trying to deploy this script to all workstations with PDQ Deploy.  It's set to run as the logged on user, but I keep getting the following error when it runs.  This error indicates to me that it can't find an outlook profile.  Do you have any ideas as to what other reasons may be causing this error to get thrown?  It works fine if I double click the script locally.  I'm in contact with PDQ and I've tried a million options to no avail.  One thing to note is that it is being called with cscript and/or wscript during this process.  Any suggestions you can give would be appreciated.  Thanks!

User generated image
I've never used PDQ Deploy before, but it's my understanding that it's used to deploy software, not to run an application/script.  Deployment tools typically use a service account or run under the system account.  Neither of those would have an Outlook account associated with it.  That would explain the error.  The script HAS to run under an account that has an Outlook profile associated with it.
Avatar of ITPro44

ASKER

That's the weird thing.  I've insured it is running as my own account on my computer, while I am logged in and I still get this error.  It seems to be something on their end, but they can't figure out why.  I just thought I'd ping you to see if you had any other thoughts.  It seems to me that PDQ is running it as my account but not under the active logged on  session.
How does PDQ fit into things here?  I understand using it to deploy the script, although there are other ways, but why are you using it to run the script?
Avatar of ITPro44

ASKER

This script only needs to be run once.  To run successfully, the computer needs to be connected to the company network so AD can be reached.  PDQ allows me to push this script to machines on the network and immediately run the script.  It then tracks what machines it successfully ran the script on and what machines still need it run on so i can track deployment progress.   Its really a great tool!  That said, in this scenario, the script needs to be run as the user and in the same session/context.  Ive learned that PDQ can run the script as the logged on user but not in that logged on session/context which is what this script requires.
I've no experience with PDQ, so I'm afraid I can't help.