Firstly, I am not a programmer. I don't like programming, it's just not for me.... So this may not be the best method or the best way for you, I'm just telling you what I did!!


The company I used work for was purchased by another company, we used Exchange & they used Domino/Notes. Since no WAN connectivity was planned between them,
I couldn't use the Exchange Connectors for Notes to sync the users & email addresses. Instead, we were just doing swaps of basic info (first name, last name, office, email address)
 in an Excel spreadsheet. So now I was tasked with how to import data from an Excel spreadsheet into AD, lucky for me I found Guy Thomas & his site
http://computerperformance.co.uk He has a very helpful site with a VB Script to import an Excel file into AD as Exchange enabled contacts.

While his script worked as advertised, I did find one flaw. Text only emails to the contacts worked fine, but attachments got renamed winmail.dat. I was fortunate enough to find
http://support.microsoft.com/kb/924240 and add the two missing attributes (mapirecipient & internetencoding) to the script. Worked like a charm after that!

However (yeah, there's always a "however")... the script works great for one time use but in this case where they'll be doing this on a regular basis, I needed to add some additional
functionality. Basically when I get a new Excel file, the people in it fall into 3 categories:

a) They are on the spreadsheet and not in AD, I need to create a contact.
b) They are on the spreadsheet and they are in AD, I need to ignore them.
c) They are not on the spreadsheet and they are in AD, I need to delete them from AD.

Steps A & B are easy, the script will create new users and error (which I tell it to ignore) on existing users. Step C is where it got tricky for me, how do I easily identify who is in
AD but not on the spreadsheet?

Then I thought, what if I could notate the people who met condition A & B? That would leave the people who met condition C different than those who met A & B, right? So I
thought the description field in AD could help me here. If people met who condition A & B got a "2" in their description, and the people who did not still had a blank description,
I could sort by description in AD and delete the people who had no description. Since the description field plays no role in anything, I can change it all I want as time goes by. In
the future when I run the script, all I have to do is change the description for people who met A & B to something different than what it is.. it doesn't matter what, just something different.

So, here's what I came up with based on Guy's script...



' BulkContact.vbs
' Example VBScript to Bulk Import Contacts into Exchange 2003
' Author Guy Thomas http://computerperformance.co.uk/
' Modified by Doug for use 8-6-07
' --------------------------------------------------------------'
Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet, objItem
Dim strOU, strContactName, strPathExcel, strEmail, strProxy
Dim intRow, strYourDescription, strFirst, strLast, strMainDefault
Dim strMailbox, strNick, strOffice, strDisplay

' Set string variables
strOU = "OU=Contacts,OU=Oldcastle,OU=GALSync ," ' Note the comma
strPathExcel = "e:\galimport\importme.xls"
intRow = 3 ' Rows 1 & 2 contains headings

' Just a confirmation that the script is running
WScript.echo "Contacts will be created, Click OK to Begin"

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
On error resume next
Do Until (objExcel.Cells(intRow,1).Value) = ""
strContactName = objExcel.Cells(intRow, 5).Value
strEmail = objExcel.cells(intRow, 4).Value
strFirst = objExcel.cells(intRow, 2).Value
strLast = objExcel.cells(intRow, 1).Value
strProxy = objExcel.cells(intRow, 6).Value
strMailbox = objExcel.cells(intRow, 7).Value
strOffice = objExcel.cells(intRow, 9).Value
strDisplay = objExcel.cells(intRow, 8).Value
strNick =strContactName

' Build the actual contacts.
Set objContact = objContainer.Create("Contact",_
"cn=" & strContactName)
objContact.Put "Mail", strEmail
objContact.Put "givenName", strFirst
objContact.Put "sn", strLast
objContact.Put "proxyAddresses", strProxy
objContact.Put "targetAddress", strProxy
objContact.Put "legacyExchangeDN", strMailbox
objContact.Put "mailNickname", strNick
objContact.Put "PhysicalDeliveryOfficeName", strOffice
objContact.Put "displayName", strDisplay
objContact.PUT "Company", "Oldcastle"
objcontact.Put "internetEncoding", 1310720
objContact.Put "mAPIRecipient", False
objContact.Put "description", "2"
objContact.SetInfo

' If the object exists, change the description
if err.number then
Set objItem = GetObject ("LDAP://cn=" & strContactName & ",OU=Contacts,OU=Oldcastle,OU=GALSync,dc=na,dc=butler,dc=corp")
objItem.Put "description", "2"
objItem.Setinfo
end if

intRow = intRow + 1
Loop
objExcel.Quit
WScript.echo "Contacts have been created, Click OK to End"
WScript.Quit
' End of Sample ExchContact VBScript




Again, it may not be pretty but it works.. Basically it reads the lines from the Excel file (see Guy's sample file at his site). It tries to create a contact with that information, if they are new it works fine and loops to run again. Since pretty much the only error I may encounter is that the contact already exists, I just told it that if you get an error, get the contact & change the desription, then loop back and go again.

All I have to do before I run the script is to change the two "description" entries to something different. Again, it doens't matter what, just different.