Solved

Script Outlook to Update GAL --> Contacts

Posted on 2014-02-03
71
133 Views
Last Modified: 2016-04-25
Users are required to manually import all users in the GAL to their personal contacts list in order for those contacts to be seen on their phone natively.  I'm wondering if it's possible to script Outlook to do this in the background.  Any ideas?
0
Comment
Question by:ITPro44
  • 28
  • 27
  • 10
  • +1
71 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, CaseForensics.

Assuming that your your organization is using Active Directory, then yes, it is possible after a fashion.  In wouldn't be "in the background" as in silently and automatically adding contacts as they're added to AD, but it could be a process that you schedule using Windows Task Scheduler.
0
 

Author Comment

by:ITPro44
Comment Utility
Awesome!  Can you help me design a script to do this?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I can.  Does the script need to just add contacts or does it need to update and delete them also?  What details do you want to pull for each contact?
0
 

Author Comment

by:ITPro44
Comment Utility
It will need to update them for sure.  As for deleting, this could be helpful, but it should be optional, meaning, the script should be able to run without deleting.  Perhaps it would be easiest to design the update portion first.  

As you mentioned, running this as a scheduled task would be great.  Will you design this so that it can be run without Outlook being open?  Without the user seeing anything?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Yes, my solution will run without Outlook being open.  "Open" in this context means that the user does not have to have Outlook open.  The script will open Outlook in the background, but that will be transparent to the end-user.  They won't see anything on the screen.  

As to updates and deletes, the simplest possible solution is to have the script tag the contacts it adds and delete them all at the beginning of each run.  That saves having to write code to handle adds, updates, and deletes separately.  By deleting the contacts each time the script will treat them all as adds.  Note that I'm only talking about deleting the contacts that the script has imported from Active Directory.  It wouldn't delete any contacts that the end-user had entered manually.  The alternative is to write code to handle adds, updates, and deletes individually.  More code means there's more chance of something going wrong and it means each run will take longer.  I don't know how many entries you have in Active Directory, so I can't forecast how much longer it will take.  

Let me know how you'd like to proceed and we'll take it from there.  I've already written the code, so once I know which approach you want to take I'll adjust it accordingly and then post it along with instructions.
0
 

Author Comment

by:ITPro44
Comment Utility
Great, I'm glad to hear it will run in the background.  

I understand what you are saying about how to design the script.  I'm not comfortable, at this point, having it delete the contacts first, as users may add other information to these contacts that should not get deleted.  That said, I'd like to have it update/overwrite the contact fields with any new information that exists within AD.   Does this make sense?  

Another way to put it is this.  Currently we use the following method to do this on an individual basis.  We open up the GAL, select all contacts and then choose Add to Contacts.  Outlook then updates the existing contacts with new information only.  Please see the attached pictures.  This is the process I'm trying to replicate.  Hopefully this explains what I'm trying to do better.  I realize we may not be able to do everything the same way outlook is doing it.  If that's the case, please tell me and we can adjust according to what is possible.
outlookupdate2.jpg
updateoutlook.jpg
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Here it is.  The script reads all of the user entries in AD and copies the necessary information to an Excel spreadsheet.  It then reads that spreadsheet and creates a contact for each person in it.  On subsequent runs the script will update a contact if it exists, create it if it doesn't.  The script keys on the NickName property of a contact, so don't edit that field in the resulting Outlook contact or you'll end up with duplicates.  The contacts will all be created in your Contacts folder.

I don't know what AD account fields you have filled in, so I used the most common fields.  I can adjust that for you if I left something out or included something you don't want.  

I grouped the contacts together in a category I called "AutoUpdate".  That way if something goes wrong, then you can find and delete all the contacts the script added by switching to category view and deleting all the members of that category.  

The script may take awhile to run, especially if you have a large number of accounts in AD.  There's no visual notification of when it's done.

I tested the script in my AD environment and it worked perfectly.

Follow these instructions to use it.

1.  Open Notepad
2.  Copy the code below and paste it into Notepad
3.  Save the file.  Name it anything you choose, just be sure the extension is .vbs
4.  Double-click the saved file to give the script a test run
5.  Create a scheduled task
6.  Set the task to run this script

'--> Create some constants
	Const olFolderContacts = 10

'--> Create some variables
	Dim excApp, excWkb, excWks, olkApp, olkSes, olkFld, olkCon, objADRDSE, adoCon, adoCmd, adoRS, adoField, intRow, strFields, strSource, arrRooms, strDNC, strManager, objManager

'--> Initialize variables
	strFields = "manager,postalCode,st,l,roomNumber,streetAddress,Department,Company,physicalDeliveryOfficeName,mobile,TelephoneNumber,mail,title,givenName,SN,samAccountName"

'--> Turn error handling off
On Error Resume Next

'--> Create the Excel spreadsheet and write a header to it
	Set excApp = CreateObject("Excel.Application")
	Set excWkb = excApp.Workbooks.Add
	Set excWks = excWkb.Worksheets(1)
	With excWks
		.cells(1,1) = "Account"
		.cells(1,2) = "Last Name"
		.cells(1,3) = "First Name"
		.cells(1,4) = "Title"
		.cells(1,5) = "Email"
		.cells(1,6) = "Telephone"
		.cells(1,7) = "Mobile"
		.cells(1,8) = "Office"
		.cells(1,9) = "Company"
		.cells(1,10) = "Department"
		.cells(1,11) = "Street"
		.cells(1,12) = "Room"
		.cells(1,13) = "City"
		.cells(1,14) = "State"
		.cells(1,15) = "Zip"
		.cells(1,16) = "Manager"
	End With
	
'--> Connect to and read AD
	Set objADRDSE = GetObject("LDAP://RootDSE")
	strDNC = objADRDSE.Get("defaultnamingcontext")
	strSource = "'LDAP://" & strDNC & "'"
	Set adoCon = CreateObject("ADODB.Connection")
	adoCon.CursorLocation = 3
	adoCon.Provider = "ADsDSOObject"
	adoCon.Open "ADSI"
	Set adoCmd = CreateObject("ADODB.Command")
	adoCmd.ActiveConnection = adoCon
	adoCmd.CommandText = "SELECT " & strFields & " FROM " & strSource & " Where objectClass='user' AND objectCategory='Person' ORDER BY samAccountName"
	adoCmd.Properties("Size Limit") = 5000
	adoCmd.Properties("Page Size") = 100
	adoCmd.Properties("Timeout") = 30
	adoCmd.Properties("Cache Results") = False
	Set adoRS = adoCmd.Execute()
	If Not adoRS.EOF Then
		intRow = 2
		Do While Not adoRS.EOF
			With adoRS
				For Each adoField In .Fields
					Select Case LCase(adoField.name)
						Case "samaccountname"
							excWks.cells(intRow, 1) = adoField.value
						Case "sn"
							excWks.cells(intRow, 2) = adoField.value
						Case "givenname"
							excWks.cells(intRow, 3) = adoField.value
						Case "title"
							excWks.cells(intRow, 4) = adoField.value
						Case "mail"
							excWks.cells(intRow, 5) = adoField.value
						Case "telephonenumber"
							excWks.cells(intRow, 6) = adoField.value
						Case "mobile"
							excWks.cells(intRow, 7) = adoField.value
						Case "physicaldeliveryofficename"
							excWks.cells(intRow, 8) = adoField.value
						Case "company"
							excWks.cells(intRow, 9) = adoField.value
						Case "department"
							excWks.cells(intRow, 10) = adoField.value
						Case "streetaddress"
							excWks.cells(intRow, 11) = adoField.value
						Case "roomnumber"
							If Not IsNull(adoField.Value) Then
								arrRooms = adoField.Value
								excWks.cells(intRow, 12) = arrRooms(0)
							End If
						Case "l"
							excWks.cells(intRow, 13) = adoField.value
						Case "st"
							excWks.cells(intRow, 14) = adoField.value
						Case "postalcode"
							excWks.cells(intRow, 15) = adoField.value
						Case "manager"
							If Not IsNull(adoField.value) Then
								Set objManager = GetObject("LDAP://" &  adoField.Value)
								strManager = objManager.DisplayName
							Else
								strManager = ""
							End If
							excWks.cells(intRow,16) = strManager
							Set objManager = Nothing
							strManager = ""
					End Select
				Next
				intRow = intRow + 1
				.MoveNext
			End With
		Loop
	End If

'--> Clean up AD objects
	adoRS.Close
	Set adoRS = Nothing
	adoCon.Close
	Set adoCon = Nothing

'--> Connect to Outlook
	Set olkApp = CreateObject("Outlook.Application")
	Set olkSes = olkApp.GetNamespace("MAPI")
	olkSes.Logon olkApp.DefaultProfileName
	Set olkFld = olkSes.GetDefaultFolder(olFolderContacts).Items
	
'--> Read the contacts downloaded from AD and add, update, or delete contacts from Outlook
	For intRow = 2 To excWks.UsedRange.Rows.Count
		If excWks.cells(intRow,2).value = "" or excWks.cells(intRow,3).value = "" Or excWks.cells(intRow,5).value = "" Then
		Else
			Set olkCon = olkFld.Find("[Nickname] = '" & Replace(excWks.cells(intRow,1).value, "'", "''") & "'")
			If TypeName(olkCon) = "Nothing" Then
				Set olkCon = olkFld.Add
				olkCon.nickname = excWks.cells(intRow,1).value
			End If
			With olkCon
				.LastName = excWks.cells(intRow,2).value
				.Firstname = excWks.cells(intRow,3).value
				.JobTitle = excWks.cells(intRow,4).value
		        .Email1Address = excWks.cells(intRow,5).value
		        .BusinessTelephoneNumber = excWks.cells(intRow,6).value
		        .MobileTelephoneNumber = excWks.cells(intRow,7).value
		        .OfficeLocation = excWks.cells(intRow,8).value
		        .CompanyName = excWks.cells(intRow,9).value
		        .Department = excWks.cells(intRow,10).value
		        .BusinessAddressStreet = excWks.cells(intRow,11).value
		        .BusinessAddressCity = excWks.cells(intRow,13).value
		        .BusinessAddressState = excWks.cells(intRow,14).value
		        .BusinessAddressPostalCode = excWks.cells(intRow,15).value
		        .ManagerName = excWks.cells(intRow,16).value
		        .Categories = "AutoUpdate"
		        .Save
			End With
			Set olkCon = Nothing
		End If
	Next

'--> Disconnect from Outlook
	olkSes.Logoff
	Set olkFld = Nothing
	Set olkSes = Nothing
	Set olkApp = Nothing

'--> Save and close the spreadsheet, then close Excel
	excWkb.Close False
	Set excWks = Nothing
	Set excWkb = Nothing
	excApp.Quit
	Set excApp = Nothing
	
'--> Notify the user that the script has finished then terminate processing
	WScript.Quit

Open in new window

1
 

Author Comment

by:ITPro44
Comment Utility
Several things...

1. This is Awesome!!  You're the man!

2. I noticed the script doesn't sync all user accounts.  How does it determine which accounts to sync?  I need to find a way to filter out certain accounts.

3. Would it be possible to have this script create a new contacts list list named "Company Contacts" and sync all the contacts to that list?

4. This script is awesome!!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
1.  Thanks!

2.  The script syncs accounts that have an AD objectClass of "user" and an objectCategory of "'Person".  That should copy all user accounts while leaving out such things as contacts.  A contact in AD is an entry that lists an address for someone outside of the company.  I further restricted the syncing to just those accounts that have both a first and last name, and an email address.  That should filter out most service account entries.  I can filter further if you want, I just need to know what you want to filter on.

3.  I assume you mean a distribution list.  Yes, that's possible so long as you don't have too many contacts coming from AD.  There's a limit to the number of entries you can have in a distribution list.

4.  Thanks again.  Glad you like it.
0
 

Author Comment

by:ITPro44
Comment Utility
2. I see.  That's a good choice to filter by.  I need to think if I need it filtered by anything else.

3. What I meant by this is not creating a distribution list, but creating a separate contact list. Please see the attached picture.  

5. I realize this may change things significantly, but is it possible for this script to use the Outlook GAL instead of AD to pull this info?  The problem is, we have some users who are remote and don't have a connection to AD.  I assume the script would have to use the cache credentials for outlook in order to make a connection to the server.  Is this possible?
company-contacts.jpg
0
 

Author Comment

by:ITPro44
Comment Utility
2. Would it be possible to just filter by security group called "Company Contacts"?  I would place all users and or contacts into a security group that I want synced.
0
 

Author Comment

by:ITPro44
Comment Utility
Hey BlueDevil,
I haven't heard back from you in a while.  Are you going to be able to make some of these additional changes?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, CaseForensics.

Apologies.  I sometimes get distracted and forget about a question.

3. What I meant by this is not creating a distribution list, but creating a separate contact list. Please see the attached picture.

The script isn't creating a distribution list.  It's adding/updating contacts in your default contacts folder.  If you want it to add/update contacts in a different contacts folder, then yes, that's possible.  

I realize this may change things significantly, but is it possible for this script to use the Outlook GAL instead of AD to pull this info?

Outlook's GAL and AD are the same thing.  What you really mean is can the script read Outlook's offline address book instead of AD.  The offline address book is an extract of AD that's copied to the local computer.  When you pull up the GAL in Outlook you are looking at the offline address book.  The answer is that I don't know of a way to do that, but I'll check into it.

Would it be possible to just filter by security group called "Company Contacts"?  I would place all users and or contacts into a security group that I want synced.

Yes, that's doable.  It changes the way the script access the data though and may result in the script being much, much slower.  Are you sure you want to go that route?
0
 

Author Comment

by:ITPro44
Comment Utility
Hey BlueDevil,  Thanks for the follow up.  Here are my responses to those three items.  Can you do this?

1. Adding/updating the contacts into a different contacts folder will be ideal.  

2. Pulling the information from the offline address book would be ideal, as it will allow this solution to work for users who operate out of home offices and don't have a connection to AD.  

3. The last item of filtering by security group will be unnecessary, since we are going to use the offline address book.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
1.  Ok, I can modify the code to create the items in a different folder from the default contacts folder.

2.  I'm still checking into a way to read the offline address book.  No luck so far.

3.  That's if we can use the offline address book.  I'm not convinced that's possible.
0
 

Expert Comment

by:DarrenEley
Comment Utility
This is excellent, I was asked yesterday to look at the same thing.

Going to have a good read though the thread in the morning...

Darren
0
 

Expert Comment

by:DarrenEley
Comment Utility
Perfect, Simply Perfect!!

I have a few questions...

If I delete a user from AD, and assign a mobile number to a new user, will this do it automatically?

Are there any possible issues with it deleting any local contacts which the user has added manually, IE people not in AD and personal contacts type situation?

Is there any things which I need to be careful with running this script?

But Again Excellent!!

Regards

Darren
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, Darren.

If I delete a user from AD, and assign a mobile number to a new user, will this do it automatically?

CaseForensics didn't want the script to delete entries, so I didn't include that capability.  I could add it though with just a few lines of code.  Yes, the script would pick up a change to a user's mobile number the next time it ran.

Are there any possible issues with it deleting any local contacts which the user has added manually, IE people not in AD and personal contacts type situation?

The script doesn't currently delete anything, so there's no chance of that happening.  If a user manually created a contact and entered a nickname that matches that of a contact in AD, then the script could update the wrong contact.  So long as the users leave the nickname field alone, then there shouldn't be any problems.

Is there any things which I need to be careful with running this script?

You should of course test it in your environment to make sure it works and that it's doing what you want and expect.  The script depends on AD being accessible to work and does not include any tests to verify connectivity to AD.  If AD is not available (e.g. the network is down, the user's computer is not connected to your network), then the script will error out.  Beyond that, I can't think of any issues that would be a problem.
0
 

Expert Comment

by:DarrenEley
Comment Utility
Thank you for such a detailed reply.

I think will leave users to do a manual clean up as they do have to take ownership of something's :)

Is there a way that I could add a list of contacts not to sync? Example we have Meeting rooms which are users which it imports?

Also we have helpdesk email users which I would like to remove such as ITHelpdesk, and Facilities Management etc?

Something like a exception list I could add manually in to the script for user accounts not to sync.

Last question, as I know I will be asked it by someone, the file as, can this be changed to First Name, Surname?

Again thank you in advance.

Regards

Darren
0
 

Author Comment

by:ITPro44
Comment Utility
Can you design the script to run this way?

1. Adding/updating the contacts into a different contacts folder.

2. Pulling the information from the offline address book would be ideal, as it will allow this solution to work for users who operate out of home offices and don't have a connection to AD.  

3. The last item of filtering by security group will be unnecessary, since we are going to use the offline address book.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
CaseForensics,

I thought I'd already answered those three questions.

1.  Yes, I can modify the code to create the items in a different folder from the default contacts folder.

2.  I've been exploring a way to read the offline address book (OAB) in place of AD.  

3.  I noted that this was contingent on being able to read the OAB.  

The good news is that I believe I have found a way to read the OAB instead of AD.  I'll modify the original code and post an update as soon as I can (next 24 hours or so).
0
 

Author Comment

by:ITPro44
Comment Utility
sounds good.  I wasn't aware that you were in ht middle of pursuing that.  thanks!
0
 

Expert Comment

by:DarrenEley
Comment Utility
Good Morning, Sorry to be a pain. Any idea about my last questions?

Again sorry to be pain..

Regards

Darren
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, Darren.

Yes, we can add an exception list.  The email address would probably be the best field to key on.

Yes, I can set File As to whatever you want.  That shouldn't really be necessary though since File As is filled in automatically by Outlook when creating a new contact.

I need to finish the code for CaseForensics since this is his question.  Are you interested in using the offline address book approach too, or do you prefer sticking with the AD based approach?
0
 

Expert Comment

by:DarrenEley
Comment Utility
Hiya, It looks like the AD system would be perfect, with the being able to add exceptions.

Really appreciate it.

Regards

Darren
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
CaseForensics,

Here's the modified version that reads from the OAB and puts the contacts in the folder of your choice.  You have to provide the path to the folder on line #3 of the code

'--> Create some constants
    'On the next line, edit the path to the Outlook folder you want the contacts to go in
    Const MY_FOLDER = "Mailbox - Doe, John\Contacts\Mobile"
    Const olFolderContacts = 10

'--> Create some variables
    Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt, olkExu, olkMgr, intCnt

'--> Connect to Outlook
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    Set olkFld = OpenOutlookFolder(MY_FOLDER).Items
    
'--> Turn error handling off
    On Error Resume Next
   
'--> Read entries from the GAL.  If Outlook is in cached mode, then this will read from the OAB.
    Set olkAdr = olkSes.AddressLists
    Set olkGAL = olkAdr.Item("Global Address List")
    For Each olkEnt In olkGAL.AddressEntries
        Set olkExu = olkEnt.GetExchangeUser
        If olkExu.FirstName = "" Or olkExu.LastName = "" Then
            'Skip this entry
        Else
            Set olkCon = olkFld.Find("[Nickname] = '" & olkExu.PrimarySmtpAddress & "'")
            Select Case TypeName(olkCon)
                Case "Empty","Nothing"
                    'WScript.Echo "ADDING: " & olkExu.PrimarySmtpAddress
                    Set olkCon = olkFld.Add
                    olkCon.NickName = olkExu.PrimarySmtpAddress
                Case Else
                    'WScript.Echo "UPDATING: " & olkExu.PrimarySmtpAddress
            End Select
            With olkCon
                .LastName = olkExu.LastName
                .FirstName = olkExu.FirstName
                .JobTitle = olkExu.JobTitle
                .Email1Address = olkExu.PrimarySmtpAddress
                .BusinessTelephoneNumber = olkExu.BusinessTelephoneNumber
                .MobileTelephoneNumber = olkExu.MobileTelephoneNumber
                .OfficeLocation = olkExu.OfficeLocation
                .CompanyName = olkExu.CompanyName
                .Department = olkExu.Department
                .BusinessAddressStreet = olkExu.StreetAddress
                .BusinessAddressCity = olkExu.City
                .BusinessAddressState = olkExu.StateOrProvince
                .BusinessAddressPostalCode = olkExu.PostalCode
                Set olkMgr = olkExu.GetExchangeUserManager
                Select Case TypeName(olkMgr)
                    Case "Empty","Nothing"
                    Case Else
                        .ManagerName = olkMgr.Name
                End Select
                .Categories = "AutoUpdate"
                .Save
            End With
            Set olkCon = Nothing
            intCnt = intCnt + 1
            If intCnt = 25 Then
                Exit For
            End If
        End If
    Next

'--> Disconnect from Outlook
    olkSes.Logoff
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
   
'--> Notify the user that the script has finished then terminate processing
    WScript.Quit
    
Function OpenOutlookFolder(strFolderPath)
    Dim arrFolders, varFolder, bolBeyondRoot
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = olkSes.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Open in new window

0
 

Expert Comment

by:DarrenEley
Comment Utility
Hello, Good Evening. I tested the script with 2 users today one with an android phone and one with an Iphone, and something strange happened.

Android user was perfect everything worked as expected, However the iphone user now has email and txts messages showing the login for the user as the display name. I.e Shows Deley and not Darren Eley.. Example is attached of emails sent to my manager.

Any ideas?

Thanks in advance.

Regards

Darren
Intresting.PNG
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
CaseForensics,

I mistakenly left some code in that limits the sync to the first 25 items.  Delete line 59 - 62 to get rid of that limit.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Darren,

I wonder if the iPhone is using the "Nickname" field in the Outlook contact.  As a test, how about changing the Nickname of a contact and see if that changes the behavior on the iPhone?
0
 

Expert Comment

by:DarrenEley
Comment Utility
I will ask him to give it a go.. and get back to you.

Thank you.

Darren
0
 

Author Comment

by:ITPro44
Comment Utility
Thanks BlueDevil!
How do I locate the path to the contacts folder?  It looks as if this path will be different for each user.  Is there a variable that can be used for the path so that the only portion that has to be changed is name of the contacts folder?

Const MY_FOLDER = "Mailbox - Doe, John\Contacts\Mobile"
0
 

Author Comment

by:ITPro44
Comment Utility
There error I'm getting is

'Const' is not recognized as an internal or external command, operable program or batch file.
0
 

Author Comment

by:ITPro44
Comment Utility
C:\WINDOWS\system32>'-- some constants 1>Create
''--' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>'On the next line, edit the path to the Outlook folder you w
ant the contacts to go in
''On' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Const MY_FOLDER = "Mailbox - Doe, John\Contacts\Mobile"
'Const' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Const olFolderContacts = 10
'Const' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>'-- some variables 1>Create
''--' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt,
olkExu, olkMgr, intCnt
'Dim' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>'-- to Outlook 1>Connect
''--' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Set olkApp = CreateObject("Outlook.Application")

C:\WINDOWS\system32>Set olkSes = olkApp.GetNamespace("MAPI")

C:\WINDOWS\system32>olkSes.Logon olkApp.DefaultProfileName
'olkSes.Logon' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Set olkFld = OpenOutlookFolder(MY_FOLDER).Items

C:\WINDOWS\system32>'-- error handling off 1>Turn
''--' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>On Error Resume Next
'On' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>'-- entries from the GAL.  If Outlook is in cached mode, the
n this will read from the OAB. 1>Read
''--' is not recognized as an internal or external command,
operable program or batch file.

C:\WINDOWS\system32>Set olkAdr = olkSes.AddressLists

C:\WINDOWS\system32>Set olkGAL = olkAdr.Item("Global Address List")
Each was unexpected at this time.

C:\WINDOWS\system32>    For Each olkEnt In olkGAL.AddressEntries

C:\WINDOWS\system32>
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I don't understand the C:\WINDOWS\system32 prompts.  Did you go to a command prompt and type those commands in?  If so, why?  If not, how did you run the script?

As to the path to the contacts folder, Outlook folders are not stored in the file system and SQL has nothing to do with them.  A folder path in Outlook is essentially the same as a folder path in the file system.  The one difference being that Outlook folder paths do not include a drive letter.  The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \).  Consider the following folder structure:

Mailbox - Doe, John
    - Calendar
    - Inbox
    - Tasks
Personal Folders
    + Marketing
        + Proposals
        + Reviews
    + Projects
        + Project 1
        + Project 2

The path to "Inbox" is "Mailbox - Doe, John\Inbox".
The path to "Reviews" is "Personal Folders\Marketing\Reviews".
The path to "Project 1" is "Personal Folders\Projects\Project 1".
0
 

Author Comment

by:ITPro44
Comment Utility
nm, I made silly mistake and was running the script as a bat file instead of a vbs file.  it works!

However, I did find that the contacts folder exist before I can run the script.  If not, I get the error seen in the attached picture.  Is it possible to have this script create the contacts folder if it doesn't already exist?
0
 

Author Comment

by:ITPro44
Comment Utility
Also, the mailbox path I used was "username@domain.com\Contacts\Company Contacts"

This path worked, however, is it possible to use a variable for the username?  Otherwise this script has to be hard coded for each user, which is obviously pretty ugly.

I also noticed that it takes roughly 2 seconds to add each contact.  2x100 means this script takes roughly 200 seconds to run.  Is this expected or is there something I can do to speed that up?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Ok, I've made those changes.  The script will create the folder if it doesn't exist, and since the folder will be under the default Contacts folder I've done away with the need for you to enter a path.  

As to the time it takes to run, if this is running from a scheduled task, then it really shouldn't matter that much if it takes 3 minutes.  The user shouldn't see anything happening.  It should be transparent to them.  When I was testing to make sure that the script was in fact reading the OAB and not contacting the Exchange server, I set Outlook to work off-line.  I noticed that when Outlook was off-line the script seemed to run faster.  That makes me think that if Outlook is in contact with the Exchange server, then it goes to it for some of the information.  Otherwise, it gets it all from the OAB.  Try setting Outlook to work off-line and see if you get the same result.  If you see noticeably better performance, then I believe I can set Outlook to work off-line, run the update, then put Outlook back on-line.

'--> Create some constants
    Const olFolderContacts = 10

'--> Create some variables
    Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt, olkExu, olkMgr, olkRot, intCnt

'--> Turn error handling off
    On Error Resume Next
   
'--> Connect to Outlook
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    Set olkRot = olkSes.GetDefaultFolder(olFolderContacts)
    Set olkFld = olkRot.Folders("Company Contacts")
    If TypeName(olkFld) = "Empty" Then
        Set olkFld = olkRot.Folders.Add("Company Contacts")
    End If
    
'--> Read entries from the GAL.  If Outlook is in cached mode, then this will read from the OAB.
    Set olkAdr = olkSes.AddressLists
    Set olkGAL = olkAdr.Item("Global Address List")
    For Each olkEnt In olkGAL.AddressEntries
        Set olkExu = olkEnt.GetExchangeUser
        If olkExu.FirstName = "" Or olkExu.LastName = "" Then
            'Skip this entry
        Else
            Set olkCon = olkFld.Find("[Nickname] = '" & olkExu.PrimarySmtpAddress & "'")
            Select Case TypeName(olkCon)
                Case "Empty","Nothing"
                    'WScript.Echo "ADDING: " & olkExu.PrimarySmtpAddress
                    Set olkCon = olkFld.Add
                    olkCon.NickName = olkExu.PrimarySmtpAddress
                Case Else
                    'WScript.Echo "UPDATING: " & olkExu.PrimarySmtpAddress
            End Select
            With olkCon
                .LastName = olkExu.LastName
                .FirstName = olkExu.FirstName
                .JobTitle = olkExu.JobTitle
                .Email1Address = olkExu.PrimarySmtpAddress
                .BusinessTelephoneNumber = olkExu.BusinessTelephoneNumber
                .MobileTelephoneNumber = olkExu.MobileTelephoneNumber
                .OfficeLocation = olkExu.OfficeLocation
                .CompanyName = olkExu.CompanyName
                .Department = olkExu.Department
                .BusinessAddressStreet = olkExu.StreetAddress
                .BusinessAddressCity = olkExu.City
                .BusinessAddressState = olkExu.StateOrProvince
                .BusinessAddressPostalCode = olkExu.PostalCode
                Set olkMgr = olkExu.GetExchangeUserManager
                Select Case TypeName(olkMgr)
                    Case "Empty","Nothing"
                    Case Else
                        .ManagerName = olkMgr.Name
                End Select
                .Categories = "AutoUpdate"
                .Save
            End With
            Set olkCon = Nothing
        End If
    Next

'--> Disconnect from Outlook
    olkSes.Logoff
    Set olkMgr = Nothing
    Set olkExu = Nothing
    Set olkEnt = Nothing
    Set olkGAL = Nothing
    Set olkAdr = Nothing
    Set olkRot = Nothing
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
   
'--> Notify the user that the script has finished then terminate processing
    WScript.Quit

Open in new window

0
 

Author Comment

by:ITPro44
Comment Utility
I placed outlook in offline mode and the script doesn't work.  It runs, and creates the necessary contacts folder and doesn't give any errors, but no contacts appear.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Are you certain that Outlook is set to use cached mode and that you do in fact have an OAB?
0
 

Author Comment

by:ITPro44
Comment Utility
yes.  I'm sure.  The script doesn't work while connected to exchange either.
0
 

Expert Comment

by:DarrenEley
Comment Utility
Hi, wondering how things were getting on? I asked the user to check the Nickname setting and it was indeed the SAMACCOUNT when he changed it to the users Full name it updated all was working.

So would it be possible when you have a chance to look at a version of the script for us to have the nickname as the Full name of the user and not the SAM Account

Then I think we will be perfect, with the exclusion list.

Thank you in advance.

Regards

Darren
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
CaseForensics,

Then I'm stumped.  It works in my environment both when connected to Exchange and when working off-line.  I can't imagine why it doesn't do the same in your environment.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Darren,

Replacing samAccountName with the fullname won't work.  The script is matching on that field and the match has to be a unique value to work.  Fullname isn't guaranteed to be unique.  samAccountName is.  What I can do is use some other field to store the samAccountName and change the script to match against that field instead of matching against the nickname field.  I can then set nickname to fullname without breaking anything.
0
 

Expert Comment

by:DarrenEley
Comment Utility
Hello, Good Afternoon.. Sounds like a plan to me :) Really what ever you think is best.

Regards

Darren
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Darren,

I've posted my revised solution to your original question.  I did that to avoid making things any more confusing than they already are for any future readers of this question.
0
 

Expert Comment

by:DarrenEley
Comment Utility
Thank you, I will take a look now.

Regards

Darren
0
 

Author Comment

by:ITPro44
Comment Utility
hmmm..... I'll try on another computer.  I'm currently using Outlook 2013 on a Win 8.1 machine.  What OS and Outlook version are you testing it on?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Outlook 2013 on a Windows 7 computer.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Sorry, that should be Outlook 2010 on a Windows 7 computer.
0
 

Author Comment

by:ITPro44
Comment Utility
I just tried on a Outlook 2010 Win 7 computer and it also did not work.  I did get an outlook pop up as soon as I ran the script, saying that it was trying to connect to office365, which is the same message displayed when I attempt to access the online GAL for the first time after starting outlook.  So it appears that it is not accessing the OAB.  Also, no contacts folder was made and no contacts were imported.

Can you help me troubleshoot?
0
 

Author Comment

by:ITPro44
Comment Utility
Are you able to help me further?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
If you set Outlook to work off-line, can you still access the GAL?  If you can, can you view the properties of some entry in the GAL?
0
 

Author Comment

by:ITPro44
Comment Utility
When I put outlook in offline mode I can access the "Offline Global Address List" but not the "Global Address List".
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Your setup is different than mine when off-line.  But, if I understand correctly, it's not working even when you're online.  That's a mystery.  The only solution I can think of is for me to add debugging code.  The debugging code would create and write a log of what happens when the script runs.  You'd run the script then send me the log.  Does that sound like a plan?

One additional question, how are you running the script now?  Are you double-clicking the script to run it, or running it from the task scheduler?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
CaseForensics,

Please download and run this version.  Once it's finished, you should find a file named Debug.log in your My Documents folder.  Please upload that file back to this question so I can take a look at it and see if I can figure out what's going wrong.  

'--> Create some constants
    Const olFolderContacts = 10

'--> Create some variables
    Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt, olkExu, olkMgr, olkRot, intCnt, strDbg, objShl
    
'--> Calculate the name of the debug log file
    Set objShl = CreateObject("WScript.Shell")
    strDbg = objShl.SpecialFolders("MyDocuments") & "\Debug.log"
    Set objShl = Nothing

'--> Turn error handling off
    On Error Resume Next
   
'--> Connect to Outlook
    WriteToLogFile strDbg, "Connecting to Outlook"
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    WriteToLogFile strDbg, "Connected to Outlook"
    Set olkRot = olkSes.GetDefaultFolder(olFolderContacts)
    Set olkFld = olkRot.Folders("Company Contacts")
    If TypeName(olkFld) = "Empty" Then
        WriteToLogFile strDbg, "Creating Company Contacts folder"
        Set olkFld = olkRot.Folders.Add("Company Contacts")
    Else
        WriteToLogFile strDbg, "Company Contacts folder exists"
    End If
    
'--> Read entries from the GAL.  If Outlook is in cached mode, then this will read from the OAB.
    Set olkAdr = olkSes.AddressLists
    WriteToLogFile strDbg, "olkAdr is " & TypeName(olkAdr)
    Set olkGAL = olkAdr.Item("Global Address List")
    WriteToLogFile strDbg, "olkGAL is " & TypeName(olkGAL)
    WriteToLogFile strDbg, "----------"
    For Each olkEnt In olkGAL.AddressEntries
        Set olkExu = olkEnt.GetExchangeUser
        WriteToLogFile strDbg, "olkExu is " & TypeName(olkExu)
        If olkExu.FirstName = "" Or olkExu.LastName = "" Then
            'Skip this entry
            WriteToLogFile strDbg, "Skipping entry"
        Else
            Set olkCon = olkFld.Find("[Nickname] = '" & olkExu.PrimarySmtpAddress & "'")
            WriteToLogFile strDbg, "olkCon is " & TypeName(olkCon)
            Select Case TypeName(olkCon)
                Case "Empty", "Nothing"
                    'WScript.Echo "ADDING: " & olkExu.PrimarySmtpAddress
                    Set olkCon = olkFld.Add
                    olkCon.NickName = olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Adding contact"
                Case Else
                    'WScript.Echo "UPDATING: " & olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Updating contact"
            End Select
            With olkCon
                .LastName = olkExu.LastName
                .FirstName = olkExu.FirstName
                .JobTitle = olkExu.JobTitle
                .Email1Address = olkExu.PrimarySmtpAddress
                .BusinessTelephoneNumber = olkExu.BusinessTelephoneNumber
                .MobileTelephoneNumber = olkExu.MobileTelephoneNumber
                .OfficeLocation = olkExu.OfficeLocation
                .CompanyName = olkExu.CompanyName
                .Department = olkExu.Department
                .BusinessAddressStreet = olkExu.StreetAddress
                .BusinessAddressCity = olkExu.City
                .BusinessAddressState = olkExu.StateOrProvince
                .BusinessAddressPostalCode = olkExu.PostalCode
                Set olkMgr = olkExu.GetExchangeUserManager
                Select Case TypeName(olkMgr)
                    Case "Empty", "Nothing"
                    Case Else
                        .ManagerName = olkMgr.Name
                End Select
                .Categories = "AutoUpdate"
                .Save
            End With
            Set olkCon = Nothing
        End If
        WriteToLogFile strDbg, "----------"
    Next

'--> Disconnect from Outlook
    WriteToLogFile strDbg, "Disconnecting from Outlook"
    olkSes.Logoff
    Set olkMgr = Nothing
    Set olkExu = Nothing
    Set olkEnt = Nothing
    Set olkGAL = Nothing
    Set olkAdr = Nothing
    Set olkRot = Nothing
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
   
'--> Notify the user that the script has finished then terminate processing
    WriteToLogFile strDbg, "Terminating script"
    WScript.Quit

Sub WriteToLogFile(strLog, strMsg)
    Const ForAppending = 8
    Dim objFSO, objFil
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFil = objFSO.OpenTextFile(strLog, ForAppending, True)
    objFil.WriteLine Now & vbTab & strMsg
    objFil.Close
    Set objFil = Nothing
    Set objFSO = Nothing
End Sub

Open in new window

0
 

Author Comment

by:ITPro44
Comment Utility
Here are the results:

3/27/2014 9:01:21 AM      Connecting to Outlook
3/27/2014 9:01:21 AM      Connected to Outlook
3/27/2014 9:01:21 AM      Company Contacts folder exists
3/27/2014 9:01:21 AM      olkAdr is AddressLists
3/27/2014 9:01:21 AM      olkGAL is AddressList
3/27/2014 9:01:21 AM      ----------
3/27/2014 9:01:23 AM      olkExu is Nothing
3/27/2014 9:01:23 AM      Skipping entry
3/27/2014 9:01:23 AM      ----------
3/27/2014 9:01:23 AM      olkExu is Nothing
3/27/2014 9:01:23 AM      Skipping entry
3/27/2014 9:01:23 AM      ----------
3/27/2014 9:01:23 AM      olkExu is ExchangeUser
3/27/2014 9:01:23 AM      olkCon is Empty
3/27/2014 9:01:23 AM      Adding contact
3/27/2014 9:01:24 AM      ----------
3/27/2014 9:01:24 AM      olkExu is Nothing
3/27/2014 9:01:24 AM      Skipping entry
3/27/2014 9:01:24 AM      ----------
3/27/2014 9:01:24 AM      olkExu is ExchangeUser
3/27/2014 9:01:24 AM      olkCon is Nothing
3/27/2014 9:01:24 AM      Adding contact
3/27/2014 9:01:28 AM      ----------
3/27/2014 9:01:28 AM      olkExu is ExchangeUser
3/27/2014 9:01:28 AM      olkCon is Nothing
3/27/2014 9:01:28 AM      Adding contact
3/27/2014 9:01:30 AM      ----------
3/27/2014 9:01:30 AM      olkExu is ExchangeUser
3/27/2014 9:01:30 AM      olkCon is Nothing
3/27/2014 9:01:30 AM      Adding contact
3/27/2014 9:01:32 AM      ----------
3/27/2014 9:01:33 AM      olkExu is ExchangeUser
3/27/2014 9:01:33 AM      olkCon is Nothing
3/27/2014 9:01:33 AM      Adding contact
3/27/2014 9:01:34 AM      ----------
3/27/2014 9:01:34 AM      olkExu is Nothing
3/27/2014 9:01:34 AM      Skipping entry
3/27/2014 9:01:34 AM      ----------
3/27/2014 9:01:34 AM      olkExu is ExchangeUser
3/27/2014 9:01:34 AM      olkCon is Nothing
3/27/2014 9:01:34 AM      Adding contact
3/27/2014 9:01:36 AM      ----------
3/27/2014 9:01:36 AM      olkExu is Nothing
3/27/2014 9:01:36 AM      Skipping entry
3/27/2014 9:01:36 AM      ----------
3/27/2014 9:01:36 AM      olkExu is ExchangeUser
3/27/2014 9:01:36 AM      olkCon is Nothing
3/27/2014 9:01:36 AM      Adding contact
3/27/2014 9:01:37 AM      ----------
3/27/2014 9:01:37 AM      olkExu is ExchangeUser
3/27/2014 9:01:37 AM      olkCon is Nothing
3/27/2014 9:01:37 AM      Adding contact
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is Nothing
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is Nothing
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is Nothing
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is Nothing
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is Nothing
3/27/2014 9:01:38 AM      Skipping entry
3/27/2014 9:01:38 AM      ----------
3/27/2014 9:01:38 AM      olkExu is ExchangeUser
3/27/2014 9:01:38 AM      olkCon is Nothing
3/27/2014 9:01:38 AM      Adding contact
3/27/2014 9:01:40 AM      ----------
3/27/2014 9:01:40 AM      olkExu is ExchangeUser
3/27/2014 9:01:40 AM      olkCon is Nothing
3/27/2014 9:01:40 AM      Adding contact
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is Nothing
3/27/2014 9:01:41 AM      Skipping entry
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is ExchangeUser
3/27/2014 9:01:41 AM      Skipping entry
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is Nothing
3/27/2014 9:01:41 AM      Skipping entry
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is Nothing
3/27/2014 9:01:41 AM      Skipping entry
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is Nothing
3/27/2014 9:01:41 AM      Skipping entry
3/27/2014 9:01:41 AM      ----------
3/27/2014 9:01:41 AM      olkExu is ExchangeUser
3/27/2014 9:01:41 AM      olkCon is Nothing
3/27/2014 9:01:41 AM      Adding contact
3/27/2014 9:01:42 AM      ----------
3/27/2014 9:01:42 AM      olkExu is ExchangeUser
3/27/2014 9:01:42 AM      olkCon is Nothing
3/27/2014 9:01:42 AM      Adding contact
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Ok, I think I see the problem.  Please download and try this version.  Once it's run, please check to see if the contacts were created and please upload the log file again.

'--> Create some constants
    Const olFolderContacts = 10

'--> Create some variables
    Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt, olkExu, olkMgr, olkRot, intCnt, strDbg, objShl
    
'--> Calculate the name of the debug log file
    Set objShl = CreateObject("WScript.Shell")
    strDbg = objShl.SpecialFolders("MyDocuments") & "\Debug.log"
    Set objShl = Nothing

'--> Turn error handling off
    On Error Resume Next
   
'--> Connect to Outlook
    WriteToLogFile strDbg, "Connecting to Outlook"
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    WriteToLogFile strDbg, "Connected to Outlook"
    Set olkRot = olkSes.GetDefaultFolder(olFolderContacts)
    Set olkFld = olkRot.Folders("Company Contacts")
    If TypeName(olkFld) = "Empty" Then
        WriteToLogFile strDbg, "Creating Company Contacts folder"
        Set olkFld = olkRot.Folders.Add("Company Contacts")
    Else
        WriteToLogFile strDbg, "Company Contacts folder exists"
    End If
    
'--> Read entries from the GAL.  If Outlook is in cached mode, then this will read from the OAB.
    Set olkAdr = olkSes.AddressLists
    WriteToLogFile strDbg, "olkAdr is " & TypeName(olkAdr)
    Set olkGAL = olkAdr.Item("Global Address List")
    WriteToLogFile strDbg, "olkGAL is " & TypeName(olkGAL)
    WriteToLogFile strDbg, "----------"
    For Each olkEnt In olkGAL.AddressEntries
        Set olkExu = olkEnt.GetExchangeUser
        WriteToLogFile strDbg, "olkExu is " & TypeName(olkExu)
        If olkExu.FirstName = "" Or olkExu.LastName = "" Then
            'Skip this entry
            WriteToLogFile strDbg, "Skipping entry"
        Else
            Set olkCon = olkFld.Find("[Email1Address] = '" & olkExu.PrimarySmtpAddress & "'")
            WriteToLogFile strDbg, "olkCon is " & TypeName(olkCon)
            Select Case TypeName(olkCon)
                Case "Empty", "Nothing"
                    'WScript.Echo "ADDING: " & olkExu.PrimarySmtpAddress
                    Set olkCon = olkFld.Add
                    olkCon.Email1Address = olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Adding contact"
                Case Else
                    'WScript.Echo "UPDATING: " & olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Updating contact"
            End Select
            With olkCon
                .LastName = olkExu.LastName
                .FirstName = olkExu.FirstName
                .JobTitle = olkExu.JobTitle
                .Email1Address = olkExu.PrimarySmtpAddress
                .BusinessTelephoneNumber = olkExu.BusinessTelephoneNumber
                .MobileTelephoneNumber = olkExu.MobileTelephoneNumber
                .OfficeLocation = olkExu.OfficeLocation
                .CompanyName = olkExu.CompanyName
                .Department = olkExu.Department
                .BusinessAddressStreet = olkExu.StreetAddress
                .BusinessAddressCity = olkExu.City
                .BusinessAddressState = olkExu.StateOrProvince
                .BusinessAddressPostalCode = olkExu.PostalCode
                Set olkMgr = olkExu.GetExchangeUserManager
                Select Case TypeName(olkMgr)
                    Case "Empty", "Nothing"
                    Case Else
                        .ManagerName = olkMgr.Name
                End Select
                .Categories = "AutoUpdate"
                .Save
            End With
            Set olkCon = Nothing
        End If
        WriteToLogFile strDbg, "----------"
    Next

'--> Disconnect from Outlook
    WriteToLogFile strDbg, "Disconnecting from Outlook"
    olkSes.Logoff
    Set olkMgr = Nothing
    Set olkExu = Nothing
    Set olkEnt = Nothing
    Set olkGAL = Nothing
    Set olkAdr = Nothing
    Set olkRot = Nothing
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
   
'--> Notify the user that the script has finished then terminate processing
    WriteToLogFile strDbg, "Terminating script"
    WScript.Quit

Sub WriteToLogFile(strLog, strMsg)
    Const ForAppending = 8
    Dim objFSO, objFil
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFil = objFSO.OpenTextFile(strLog, ForAppending, True)
    objFil.WriteLine Now & vbTab & strMsg
    objFil.Close
    Set objFil = Nothing
    Set objFSO = Nothing
End Sub

Open in new window

0
 

Author Comment

by:ITPro44
Comment Utility
still didn't work:  Full log has been uploaded

3/27/2014 11:09:49 AM      Connecting to Outlook
3/27/2014 11:09:49 AM      Connected to Outlook
3/27/2014 11:09:49 AM      Company Contacts folder exists
3/27/2014 11:09:49 AM      olkAdr is AddressLists
3/27/2014 11:09:49 AM      olkGAL is AddressList
3/27/2014 11:09:49 AM      ----------
3/27/2014 11:09:49 AM      olkExu is Nothing
3/27/2014 11:09:49 AM      Skipping entry
3/27/2014 11:09:49 AM      ----------
3/27/2014 11:09:49 AM      olkExu is Nothing
3/27/2014 11:09:49 AM      Skipping entry
3/27/2014 11:09:49 AM      ----------
3/27/2014 11:09:49 AM      olkExu is ExchangeUser
3/27/2014 11:09:49 AM      olkCon is Empty
3/27/2014 11:09:49 AM      Adding contact


3/27/2014 11:10:53 AM      ----------
3/27/2014 11:10:53 AM      olkExu is ExchangeUser
3/27/2014 11:10:53 AM      olkCon is Nothing
3/27/2014 11:10:53 AM      Adding contact
3/27/2014 11:10:54 AM      ----------
3/27/2014 11:10:54 AM      olkExu is Nothing
3/27/2014 11:10:54 AM      Skipping entry
3/27/2014 11:10:54 AM      ----------
3/27/2014 11:10:54 AM      Disconnecting from Outlook
3/27/2014 11:10:54 AM      Terminating script
Debug.log
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 500 total points
Comment Utility
One more time, please.

'--> Create some constants
    Const olFolderContacts = 10

'--> Create some variables
    Dim olkApp, olkSes, olkFld, olkCon, olkAdr, olkGAL, olkEnt, olkExu, olkMgr, olkRot, intCnt, strDbg, objShl
    
'--> Calculate the name of the debug log file
    Set objShl = CreateObject("WScript.Shell")
    strDbg = objShl.SpecialFolders("MyDocuments") & "\Debug.log"
    Set objShl = Nothing

'--> Turn error handling off
    On Error Resume Next
   
'--> Connect to Outlook
    WriteToLogFile strDbg, "Connecting to Outlook"
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    WriteToLogFile strDbg, "Connected to Outlook"
    Set olkRot = olkSes.GetDefaultFolder(olFolderContacts)
    Set olkFld = olkRot.Folders("Company Contacts")
    If TypeName(olkFld) = "Empty" Then
        WriteToLogFile strDbg, "Creating Company Contacts folder"
        Set olkFld = olkRot.Folders.Add("Company Contacts")
    Else
        WriteToLogFile strDbg, "Company Contacts folder exists"
        WriteToLogFile strDbg, "Items: " & olkFld.Items.Count
    End If
    WriteToLogFile strDbg, "olkFld = " & olkFld.FolderPath

'--> Read entries from the GAL.  If Outlook is in cached mode, then this will read from the OAB.
    Set olkAdr = olkSes.AddressLists
    WriteToLogFile strDbg, "olkAdr is " & TypeName(olkAdr)
    Set olkGAL = olkAdr.Item("Global Address List")
    WriteToLogFile strDbg, "olkGAL is " & TypeName(olkGAL)
    WriteToLogFile strDbg, "----------"
    For Each olkEnt In olkGAL.AddressEntries
        Set olkExu = olkEnt.GetExchangeUser
        WriteToLogFile strDbg, "olkExu is " & TypeName(olkExu)
        If olkExu.FirstName = "" Or olkExu.LastName = "" Then
            'Skip this entry
            WriteToLogFile strDbg, "Skipping entry"
        Else
            Set olkCon = olkFld.Items.Find("[Email1Address] = '" & olkExu.PrimarySmtpAddress & "'")
            WriteToLogFile strDbg, "olkCon is " & TypeName(olkCon)
            Select Case TypeName(olkCon)
                Case "Empty", "Nothing"
                    'WScript.Echo "ADDING: " & olkExu.PrimarySmtpAddress
                    Set olkCon = olkFld.Items.Add
                    olkCon.Email1Address = olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Adding contact"
                Case Else
                    'WScript.Echo "UPDATING: " & olkExu.PrimarySmtpAddress
                    WriteToLogFile strDbg, "Updating contact"
            End Select
            WriteToLogFile strDbg, "olkCon is " & TypeName(olkCon)
            With olkCon
                .LastName = olkExu.LastName
                .FirstName = olkExu.FirstName
                .JobTitle = olkExu.JobTitle
                .Email1Address = olkExu.PrimarySmtpAddress
                .BusinessTelephoneNumber = olkExu.BusinessTelephoneNumber
                .MobileTelephoneNumber = olkExu.MobileTelephoneNumber
                .OfficeLocation = olkExu.OfficeLocation
                .CompanyName = olkExu.CompanyName
                .Department = olkExu.Department
                .BusinessAddressStreet = olkExu.StreetAddress
                .BusinessAddressCity = olkExu.City
                .BusinessAddressState = olkExu.StateOrProvince
                .BusinessAddressPostalCode = olkExu.PostalCode
                Set olkMgr = olkExu.GetExchangeUserManager
                Select Case TypeName(olkMgr)
                    Case "Empty", "Nothing"
                    Case Else
                        .ManagerName = olkMgr.Name
                End Select
                .Categories = "AutoUpdate"
                .Save
                WriteToLogFile strDbg, "Items: " & olkFld.Items.Count
	            'intCnt = intCnt + 1
		        'If intCnt = 5 Then
		        '	Exit For
		        'End If
            End With
            Set olkCon = Nothing
        End If
        WriteToLogFile strDbg, "----------"
    Next

'--> Disconnect from Outlook
    WriteToLogFile strDbg, "Disconnecting from Outlook"
    olkSes.Logoff
    Set olkMgr = Nothing
    Set olkExu = Nothing
    Set olkEnt = Nothing
    Set olkGAL = Nothing
    Set olkAdr = Nothing
    Set olkRot = Nothing
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
   
'--> Notify the user that the script has finished then terminate processing
    WriteToLogFile strDbg, "Terminating script"
    WScript.Quit

Sub WriteToLogFile(strLog, strMsg)
    Const ForAppending = 8
    Dim objFSO, objFil
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFil = objFSO.OpenTextFile(strLog, ForAppending, True)
    objFil.WriteLine Now & vbTab & strMsg
    objFil.Close
    Set objFil = Nothing
    Set objFSO = Nothing
End Sub

Open in new window

0
 

Author Comment

by:ITPro44
Comment Utility
That worked!  Log file attached. I tried it in offline mode and it did not work.  When in online mode - outlook boggs down significantly rendering it unusable for ~1.5min while the contacts update.  We have 75 contacts.  I'm guessing that if this worked in offline mode and pulled form the offline Address book the import would go faster and wouldn't bog down outlook.
Debug.log
offlinemode-Debug.log
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Good deal.  Now that we have it working when you're online, let's dig into the offline issue.  On my system, I see the GAL both when I'm online and when I'm offline.  You don't though.  You mentioned that when offline you see Offline Global Address List.  So, let's try this.  Edit line 35 changing it from

Set olkGAL = olkAdr.Item("Global Address List")

Open in new window


to

Set olkGAL = olkAdr.Item("Offline Global Address List")

Open in new window


Once you've done that, set Outlook to work offline and run the script again.  

As to the how long it takes the script to run, that will improve some once I remove the debugging code.  If we can get the script to read the offline address book, then it should improve more.
0
 

Author Comment

by:ITPro44
Comment Utility
Do you think you can get it to work using the Offline Address Book?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Not enough information to confirm an answer.
0
 

Author Closing Comment

by:ITPro44
Comment Utility
thanks again!
0
 

Author Comment

by:ITPro44
Comment Utility
Is it common practice to delete questions?  If so, why?
0
 

Author Comment

by:ITPro44
Comment Utility
I read that and the cleanup volunteer said it would be closed, not deleted.  That said, I still don't see the reason to delete questions.  Closing them without a solution should be adequate.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Resolve DNS query failed errors for Exchange
Outlook Free & Paid Tools
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now