Script Adding Entries to Distribution Lists in the GAL


Last week, a reader named Ashwath posted a comment asking about the possibility of updating a public distribution list. Here’s what Ashwath said.

I am trying to find an automated way to add members to a Public Distribution List on the Global Address List. Right now, the only way to do it is to manually look up for a contact one by one in the Global Address List and then add them, but I’ve close to 300+ names to do this. Would you be help to help with a macro for either outlook 2007/Excel 2007 that can automatically pick e-mail addresses from an excel sheet and then add them to the PDL?

P.S: This Distribution List is not an Outlook Distribution List. It is a Distribution List created on the GLobal Address List by my ‘IT’ department; so there is no easy way to copy and paste multiple e-mail addresses.

Public distribution lists (i.e. a mail enabled Active Directory groups) have little to do with Outlook from a scripting perspective. They reside in Active Directory and are not created or updated through the scripting of Outlook. Instead, they are handled through ADSI (Active Directory Services Interfaces) scripting. Although the solution doesn’t involve Outlook, I could have written the necessary script in VBA and run it from either Outlook or Excel. There’s little point to doing it in Outlook, and it makes more sense to keep the script outside of Excel since that will give Ashwath more flexibility. In my opinion, the best solution is to create the script using VBscript, which is the approach I’ve taken here. One of the benefits of using VBscript is that Ashwath can drop the script in his computer’s Send to folder, allowing him to send a spreadsheet to the script for processing. With this approach, Ashwath can even update different distribution lists from the same spreadsheet.

The script works by opening and reading an Excel spreadsheet. For each line in the spreadsheet, the script gets an email address from column A and a distribution list (i.e. group) name from column B. After checking to make sure that the distribution list exists and that there is a user account with the email address, the script adds the user account associated with the email address to the distribution list. If the distribution list does not exist or an account with the specified email address isn’t found, then the script writes “Group not found” or “User account not found” (respectively) in column C of that entry. When the script has finished processing, Ashwath can open the spreadsheet and see which updates succeeded (column C will be empty) and which ones failed (column C will say “Failed”).

Instructions.

  1. Open Notepad.
  2. Copy the script and paste it into Notepad.
  3. Edit the script per the comments I included in it. At a minimum you must edit the domain name on line #2.
  4. Save the file to the Send To folder with a .vbs extension. You can name the script anything you want.
  5. Close Notepad.
'On the next line change "company.com" to your domain name
Const AD_DOMAIN_NAME = "company.com"
Const SCRIPT_NAME = "Add Users to Groups"

Dim excApp, excWkb, excWks, lngRow
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Open(WScript.Arguments(0))
Set excWks = excWkb.Worksheets(1)
lngRow = 1
Do While excWks.Cells(lngRow, 1) <> ""
    Add2Group excWks.Cells(lngRow,1), excWks.Cells(lngRow,2)
    lngRow = lngRow + 1
Loop
Set excWks = Nothing
excWkb.Close True
Set excWkb = Nothing
Set excApp = Nothing
MsgBox "Processing complete", vbInformation+vbOKOnly, SCRIPT_NAME
WScript.Quit

Sub Add2Group(strEmail, strGroup)
    Const ADS_PROPERTY_APPEND = 3 
    Dim objGroup, strGroupPath, strUserPath
    strGroupPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME & "' WHERE objectClass='group' AND Name='" & strGroup & "'")
    strUserPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME & "' WHERE objectClass='user' AND objectCategory='Person' AND Mail='" & strEmail & "'")
    If strGroupPath = "Not Found" Then
        excWks.Cells(lngRow,3) = "Group not found"
    else
        If strUserPath = "Not Found" Then
            excWks.Cells(lngRow,3) = "User account not found"
        Else
            Set objGroup = GetObject(strGroupPath)
            If Not objGroup.IsMember(strUserPath) Then
            	objGroup.Add strUserPath
            	objGroup.SetInfo
            End If
        End If
    End If
    Set objGroup = Nothing
End Sub

Function Get_AD_Object_Path(strQuery)
    Dim rsDetails
    Set rsDetails = Wscript.CreateObject("ADODB.Recordset")
    With rsDetails
        .ActiveConnection = "Provider=ADSDSOObject"
        .Source = strQuery
        .CursorType = 0
        .CursorLocation = 2
        .LockType = 1
        .Open()
    End With
    If (Not rsDetails.BOF) And (Not rsDetails.EOF) Then
        Get_AD_Object_Path = rsDetails.Fields("ADsPath").Value
    Else
        Get_AD_Object_Path = "Not Found"
    End If
    Set rsDetails = Nothing
End Function

Usage.

To use the script, right-click on a spreadsheet containing your updates and select Send to then select the script name you used in #3 of the instructions. The script will open the spreadsheet and process the entries on the first sheet only. The script assumes that the sheet does not have a header and that each line contains one entry. For each line the script assumes that column A contains the email address of the account to add and that column B contains the name of the distribution list you want to add the group to. The script will process all lines until it finds that column A is blank. For the script to work you must have the necessary permissions in Active Directory to make changes to the target distribution list(s). As with any script please test it thoroughly before placing it into production.

Revisions.

  • Revision 1

This revision is for Ashwath who has since shared that the user account could be in any one of three different domains. This version of the code allows Ashwath to specify the three domain names which the code then searches looking for the user account. It will stop searching as soon as it finds the user account in a domain (i.e. no unnecessary searches) and will still generate a user account not found message if it fails to find the account in any of the three domains.

'On the next three lines change "company.com" to your domain names
Const AD_DOMAIN_NAME1 = "company.com"
Const AD_DOMAIN_NAME2 = "company.com"
Const AD_DOMAIN_NAME3 = "company.com"
Const SCRIPT_NAME = "Add Users to Groups"

Dim excApp, excWkb, excWks, lngRow
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Open(WScript.Arguments(0))
Set excWks = excWkb.Worksheets(1)
lngRow = 1
Do While excWks.Cells(lngRow, 1) <> ""
    Add2Group excWks.Cells(lngRow,1), excWks.Cells(lngRow,2)
    lngRow = lngRow + 1
Loop
Set excWks = Nothing
excWkb.Close True
Set excWkb = Nothing
Set excApp = Nothing
MsgBox "Processing complete", vbInformation+vbOKOnly, SCRIPT_NAME
WScript.Quit

Sub Add2Group(strEmail, strGroup)
    Const ADS_PROPERTY_APPEND = 3 
    Dim objGroup, strGroupPath, strUserPath
    strGroupPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME & "' WHERE objectClass='group' AND Name='" & strGroup & "'")
    strUserPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME1 & "' WHERE objectClass='user' AND objectCategory='Person' AND Mail='" & strEmail & "'")
    If strUserPath = "Not Found" Then
        strUserPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME2 & "' WHERE objectClass='user' AND objectCategory='Person' AND Mail='" & strEmail & "'")
        If strUserPath = "Not Found" Then
            strUserPath = Get_AD_Object_Path("SELECT ADsPath FROM 'LDAP://" & AD_DOMAIN_NAME3 & "' WHERE objectClass='user' AND objectCategory='Person' AND Mail='" & strEmail & "'")
        End If
    End If
    If strGroupPath = "Not Found" Then
        excWks.Cells(lngRow,3) = "Group not found"
    else
        If strUserPath = "Not Found" Then
            excWks.Cells(lngRow,3) = "User account not found"
        Else
            Set objGroup = GetObject(strGroupPath) 
            objGroup.Add strUserPath
            objGroup.SetInfo
        End If
    End If
    Set objGroup = Nothing
End Sub

Function Get_AD_Object_Path(strQuery)
    Dim rsDetails
    Set rsDetails = Wscript.CreateObject("ADODB.Recordset")
    With rsDetails
        .ActiveConnection = "Provider=ADSDSOObject"
        .Source = strQuery
        .CursorType = 0
        .CursorLocation = 2
        .LockType = 1
        .Open()
    End With
    If (Not rsDetails.BOF) And (Not rsDetails.EOF) Then
        Get_AD_Object_Path = rsDetails.Fields("ADsPath").Value
    Else
        Get_AD_Object_Path = "Not Found"
    End If
    Set rsDetails = Nothing
End Function
Advertisements

50 comments on “Script Adding Entries to Distribution Lists in the GAL

  1. This code is great. One question with it. If the user already exists in the distribution list then the code errors out. Is there a way to make it just move on to the next user if the user already exists in the distribution list?

    • Hi, Justin.

      Good catch. I don’t know why I didn’t include that to begin with. I’ve updated the code in the original post. Please download it again and use it instead of the original code.

  2. David,

    I am in need of something similar. One of my companies divisions has a Contact List in the Public Folder that they maintain themselves. They are wanting to create distribution lists out of these contacts. They want to have a Contact that they place an identifier somewhere in the details and that contact automatically gets added to the Distribution List. I have very little experience with VBA and came accross this while searching for an add-in or script.

    Thanks

    • Hi, Tim.

      “One of my companies divisions has a Contact List in the Public Folder that they maintain themselves. They are wanting to create distribution lists out of these contacts.”
      “They want to have a Contact that they place an identifier somewhere in the details and that contact automatically gets added to the Distribution List. ”

      Sorry, but I don’t understand what you want. The two statements seem to be asking for different things. The first one suggests that you want to create contact lists from the public folder contact list. The second statement suggests that you want to add a contact to the public folder contact list based on some data in the contact itself. Please help me understand what it is you want to accomplish.

    • David,

      Sorry for the confusion. I am looking for a way to create Distributions lists automatically from the Public Folder Contact List. They want to add an Identifier to a field in the Contact and have the Contact added to a Distrobution List based on that.

      Example: They have several Contacts for different Trucking companies in the Public Folder Contact List. They want to add “Trucking” to a field in a Contact and have that Contact added to a Trucking Distrobution List withou further interaction.

    • Tim,

      No worries.

      Yes, that’s possible. Where in the contact do they want to add the keyword (e.g. Trucking)? For example, do they want to add the contact to a Trucking category, or perhaps add it as a hashtag (e.g. #Trucking) in the body of the contact? I’ll need to know that ahead of time to write the code.

    • David,

      Thanks. They don’t have anywhere specific that they want to add the Keyword. They really didn’t think what they were asking for was possible. Adding with a Hashtag in the body would work great.

      Tim

    • Good morning, Tim.

      I’ve put the code together for this. In the process of doing that I thought of an issue that I should have thought of earlier. The contacts and distribution lists are in a public folder, and, if I understand correctly, there will be multiple people using the solution. The code monitors the folder watching for new contacts or changes to existing contacts. When it sees a new contact or a change to an existing contact, it checks the body for hashtags. If it finds any, then for each hashtag it checks to see if the contact is a member of the list pointed to by the hashtag. If the contact is not a member of the list, then the code adds them to the list. The problem is that with multiple people running the same code, each of them is going to run through the same process. Say for example that you have 10 people running the code. You add a contact with a hashtag. All 10 of the computers running the code are going to go through these same steps. That doesn’t mean that the contact will get added 10 times. It should still only be added once. What it does mean though is that all 10 computers will spend time running through the process. I don’t know for sure, I’m unable to test this in a multi-user environment, what affect that will have on each computer. You’ll have to test this out to see for yourself what impact it has, if any.

      One other note. The hashtags must exactly match the names of the distribution lists and they cannot include spaces. For example, if you have a dist list called “Truckers” then the corresponding hashtag would be #Truckers. If the dist list name has a space in it, then you’ll have to rename it to something that does not include a space. Oh, and the distribution list must already exist. The code will not create them for you.

      Here’s the code. You must add it to the ThisOutlookSession module in Outlook. If you need instructions for doing that, then let me know.

      'On the next line, edit the path to the public folder containing both the contacts and the distribution lists.  They must be in the same folder.
      Const PUBLIC_FOLDER_PATH = "Public Folders - someone@company.com\All Public Folders\Contacts"
      
      Dim WithEvents olkFld As Outlook.Items
      
      Private Sub Application_Quit()
          Set olkFld = Nothing
      End Sub
      
      Private Sub Application_Startup()
          Dim olkTmp As Object
          Set olkTmp = OpenOutlookFolder(PUBLIC_FOLDER_PATH)
          If TypeName(olkTmp) <> "Nothing" Then
              Set olkFld = olkTmp.Items
          Else
              MsgBox "I could not find the folder pointed to be the path" & vbCrLf & vbCrLf & PUBLIC_FOLDER_PATH, vbCritical + vbOKOnly + vbApplicationModal, "Application Startup Event"
          End If
          Set olkTmp = Nothing
      End Sub
      
      Private Sub olkFld_ItemAdd(ByVal Item As Object)
          If Item.Class = olContact Then
              UpdateList Item
          End If
      End Sub
      
      Private Sub olkFld_ItemChange(ByVal Item As Object)
          If Item.Class = olContact Then
              UpdateList Item
          End If
      End Sub
      
      Private Sub UpdateList(olkCon As Outlook.ContactItem)
          Dim olkLst As Outlook.DistListItem, _
              olkRec As Outlook.Recipient, _
              arrTag As Variant, _
              varTag As Variant, _
              strTag As String
          strTag = FindString(olkCon.Body, "\#\w*")
          If strTag <> "Not found" Then
              arrTag = Split(strTag, "|")
              For Each varTag In arrTag
                  Set olkLst = olkFld.Items.Find("[Name] = '" & Replace(varTag, "#", "") & "'")
                  If TypeName(olkLst) = "DistListItem" Then
                      If Not IsMember(olkLst, olkCon.Email1Address) Then
                          Set olkRec = Session.CreateRecipient(olkCon.FullName)
                          olkRec.Resolve
                          If olkRec.Resolved Then
                              olkLst.AddMember olkRec
                              olkLst.Save
                          End If
                      End If
                  End If
              Next
          End If
      End Sub
      
      Private Function FindString(ByRef strText As String, ByRef strFind As String) As String
          Dim objRegEx As Object, colMatches As Object, objMatch As Object
          Set objRegEx = CreateObject("VBscript.RegExp")
          With objRegEx
              .IgnoreCase = False
              .Global = True
              .Pattern = strFind
              Set colMatches = .Execute(strText)
          End With
          If colMatches.count > 0 Then
              For Each objMatch In colMatches
                  FindString = FindString & objMatch.value & "|"
              Next
              FindString = Left(FindString, Len(FindString) - 1)
          Else
              FindString = "Not found"
          End If
          Set objRegEx = Nothing
          Set colMatches = Nothing
          Set objMatch = Nothing
      End Function
      
      Private Function IsMember(olkLst As Outlook.DistListItem, strAdr As String) As Boolean
          Dim lngCnt As Integer, olkRec As Outlook.Recipient
          For lngCnt = 1 To olkLst.MemberCount
              Set olkRec = olkLst.GetMember(lngCnt)
              If LCase(olkRec.Address) = LCase(strAdr) Then
                  IsMember = True
                  Exit For
              End If
          Next
          Set olkRec = Nothing
      End Function
      
      Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
          ' Purpose: Opens an Outlook folder from a folder path.'
          ' Written: 4/24/2009'
          ' Author:  David Lee'
          ' Outlook: All versions'
          Dim arrFolders As Variant, _
              varFolder As Variant, _
              bolBeyondRoot As Boolean
          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 = Outlook.Session.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
      
    • David,

      I have gotten it to run.

      I created a Distribution List in the Address Book named Test
      I added #Test in the Note section of a Contact.

      But when I click Save, I get: Run-time error ‘438’: Object doesn’t support this property or method.

      When I click Debug this is the line that is highlighted in yellow:

      Set olkLst = olkFld.Items.Find(“[Name] = ‘” & Replace(varTag, “#”, “”) & “‘”)

      Is there a redistributable or something that needs to be installed?

      This is awesome by the way and if I am taking up a bunch of your time I can try and figure it out on my own.

      Thanks,

      Tim

    • Tim,

      Please change this line

      Set olkLst = olkFld.Items.Find("[Name] = '" & Replace(varTag, "#", "") & "'")
      

      to

      Set olkLst = olkFld.Find("[Name] = '" & Replace(varTag, "#", "") & "'")
      
    • David,

      Sorry to keep bugging.

      I don’t get an error message anymore, but the Contact doesn’t get added to the Distribution List.

      Just so you know, incase it makes a difference when putting in the path to the Public folder.

      You said the format was “Public Folders – someone@company.com\All Public Folders\Contacts”.

      I couldn’t get that to work so used “\\Public Folders\All Public Folders\DEPARTMENT\Contact List” and it was apparently able to get there as the error went away.

      Thanks,

      Tim

    • Tim,

      No worries.

      Is the distribution list in the same folder as the contact (i.e both are in the folder pointed to by “\\Public Folders\All Public Folders\DEPARTMENT\Contact List”)?

    • David,

      Sorry about that, in my haste to test it I named the Distribution List Test and the Contact Test. Once I changed the Contact name it added without issue.

      So just quick clarifications:

      It only adds and won’t delete if the ‘#’ is removed correct? not an issue just want to be aware of the behavior

      If say I only have one user running it, the additions and changes will only be picked up if that person’s Outlook is running correct?

    • Tim,

      No problem. I’m glad it was something simple.

      Yes, this solution only adds to the list. Removing a contact from one or more lists would be a much more difficult proposition. Say that you have 10 lists. You edit a contact, remove a hashtag, and save the contact. Since the code would have no way of knowing what lists the contact belonged to, it would have to check every list to see if the contact was a member and remove it if it was. So long as there aren’t too many lists, and each list doesn’t have too many entries, the impact would likely by minimal. But, as the number of lists and members on each list increases, the impact is going to grow. Then there’s the issue of deleting a contact. That’s even more problematic. Outlook’s object model does fire an event when a delete occurs, but it does not tell you what item was just deleted. That would mean building a list of all contacts and comparing that list to each distribution list. It’s doable, but here too the impact will grow as the number of contacts and distribution lists grows. One possible solution to both problems is to stop using distribution lists. Here’s another of my posts that describes a way to use categories as a replacement for distribution lists.

      If you had just one user run the code, then that user’s computer would have to be on, logged in, and have Outlook open for the code to work. One way to handle that would be to run the solution like a Windows service. To do that, you’d set up a dedicated computer with Outlook installed, and leave it running all the time.

  3. Hi David,
    I am a novice in outlook and ADSI programming. I have a requirement similar to it. I would be grateful if you can guide me.
    I am the owner of a public distribution list. It is static. I want to be able to add/ remove users from the DL through a script preferably vbscript.
    I guess I don’t have exchange admin or AD admin privileges. But if I am able to add/remove users from the DL from outlook UI, there should be a way to do it through script. I believe the solution you have suggested above to Ashwath assumes that the user running the script has admin access on exchange server. If I run the same script, I keep on getting an error ‘Permission denied.’ (Err.code – 70)

    I would really appreciate if you can show the path to me.

    • How are those different? It is a public distribution list available in MS exchange. Is it information sufficient? I can access the DL through outlook webapp from outside the network. I guess it is not on AD.

    • Hi, Biswajit.

      A public DL that’s in AD isn’t normally editable by anyone other than an admin or a user that has been granted delegate access. The DL is created in AD using a tool such as Active Directory Users and Computers (ADUC for short). A DL in a public folder is typically created by any end-user that has access to the public folder. The DL is created in Outlook itself, not an external tool like ADUC. I can script adding users to either one, but need to know which type of DL it is since the script for adding to a DL in AD won’t work for a DL in a public folder and vice versa.

  4. Good day. This article makes the assumption that your ADSI is tied directly to Exchange. For those with “cloud” or hosted exchange from a third party, this may not be the case. Our company decided to outsource our Exchange email to another company and when they did, it broke all of the links between the mail enabled distribution groups in ADDS and the distribution groups as they appear in the GAL. Going back to manipulate these DLs in Outlook is actually my only option. I am still trying to figure out how to make changes to these DLs in the GAL. I can list the members of any DL in the GAL but I can’t figure out how to effect change on them through VBA in Outlook.

    • Hi, techknowledgist.

      You’re right. That is the assumption. I’m not an Exchange expert, but it’s my understanding that Exchange requires Active Directory. It’s simply a question of who owns the AD and what level of access you have to make changes to it. The script will still function if you have the permissions necessary to make changes to AD, even if that AD belongs to a different organization. If you don’t have the necessary permissions, then the script won’t work. Does your email provider allow your organization to have public distribution lists? If it does, then it should just be a matter of permissions.

  5. Hi Lee,
    Ok, I didn’t know it was a bit more complex than I thought. Please take your time to modify the code. No hurries !!! Only addition was my main concern.

    Thanks.

    Ashwath.

  6. Hi Lee,

    The revision 1 of the code seems to suit my requirement and it works like a breeeeze now 🙂 Kudos to your massive time and effort to write and fine tune the code. Much Appreciated…:) Final question: this code is meant to “ADD” people to the DL. What if I want to remove? Do I just change the “objGroup.Add strUserPath ” statement to “objGroup.Remove strUserPath” and then it would work?

    • Thanks, Ashwath! Glad the code is working for you.

      No, removing a user from a group isn’t quite that simple. If you’d like I can modify the code to handle deletions too. That will require an extra column in the spreadsheet that indicates which action (add or delete) to take for each entry.

  7. Hi Lee,
    Sorry, my work has been really hectic over the last few days; so couldn’t reply. I will test the revision 1 code and let you know. Now, I assume I’ll have to enter the 3 domain names for the user account in the beginning, but for group account, I’ll have to replace “AD_DOMAIN_NAME” with the domain of the group right? (it’s a constant anyways, so no issues)

  8. Hi Lee,
    Hope u had a good weekend 🙂 ok…getting to know the domain name of a user is quite a tedious task, as it is scattered throughout the organization and there is no easy way to retrieve it automatically, other than asking each one of them…but what I do know for sure is that all the users belong to one among the 3 domains that we have…so with that in place, is there a workaround for the code to check the validity of an email address among the 3 domains?

    • Hi, Ashwath.

      I did, thanks. I hope you did too.

      Please see revision 1 which I just added to the post.

  9. Hi Lee,
    I got the code to work a bit more now!!! The script is basically looking for a domain name for both the user and the group (DL). The domain for the group is fixed and does not change. Hence, I modifed the ‘strGroupPath =’ statement to a fixed domain name.; and the ‘strUserPath =’ statement now picks any domain name that is defined by “Const AD_DOMAIN_NAME” in the beginning. So, by this method, I can add all users belonging to the domain mentioned in “Const AD_DOMAIN_NAME” to the DL. But, what about people in other domains? Is there a way for that? I would have to manually change the domain name in “Const AD_DOMAIN_NAME” if, I have to add a user from another domain. So, basically, the script is not looking for my domain; it is looking for the users domain; and if it cannot find the specified email address with the domain name mentioned it returns “User Account Not Found”.

    Any thoughts???

    • How about if I add another column to the spreadsheet and you enter the domain name the person is in in that column?

  10. Hi Lee,
    The domains are based on the region where we work, for e.g. if I belong to the “asia” domain, there are people who also belong to the “europe” or “USA” domain. I am only able to add people who are in the “asia” domain and not people in the other domains. But, all of them are within the organization only on the GAL. Is there a way to check if the e-mail address entered belongs to one among multiple domains and then pick the right domain and add it?

  11. Hi Lee,
    I got the code to work finally, but with some limitations. Here are my observations:
    1. When the code is saved in ‘notepad’ it should be saved with the encoding type as ‘ANSI’. If it is saved as ‘Unicode Text’ then it will always give the invalid character error.
    2.Line#27; excWks.Cells(lngRow,3) + “Group not found” had ‘=’ sign missing; instead ‘+’ appeared. I had changed it.
    3. In the excel file where the group name should be entered in column ‘B’; the actual e-mail address of the group should be entered, merely entering the group name as it appears in the GAL will shoot a “Group Not Found” error.
    4. Important: I am only able to add users who belong to the same domain as me. The majority of users who I have to add belong to a domain other than mine. Is there a way to solve this?

    Appreciate your feedback!!!

    • Hi, Ashwath.

      1. Thanks for that piece of information. Since my Notepad defaults to ANSI I guess I never thought of the Unicode issue.
      2. A typo error on my part. Thanks for pointing it out. I’ve corrected it.
      3. That’s interesting. I’m able to use a group name in my domain. There must be some difference between our domains.
      4. I thought you had confirmed that all the accounts were in your domain. Are you saying that your organization has a top level domain with multiple sub-level domains under it? If not, then how can the entries be “people inside the organization only” yet not be in your domain?
    • I just modified the code in the post to report which failed, the group or the user account. Please download the code again and replace what you have now with the new version. Change the domain name in the new version as you did before and try running it. Afterwards when you look at the workbook you should see either “Group not found” or “User account not found”. If it’s the former, then the code isn’t able to find a group with the name specified in the sheet. If it’s the latter, then the email address isn’t matching an address in any of the AD accounts.

  12. Hi Lee,

    Thanks for the help. Much appreciated 🙂 Few questions: Is the AD account same as Global Address List. I’m not sure if the email address I’m trying to add has an Active Directory Account, but I am sure it is on the Global Address List. That’s why I’m not sure if both are the same. However, I do have the right permissions to add the person’s name to the DL, because I am able to do it manually, if not, I wouldn’t have been able to do that also. Just a thought !!!

    • The Global Address List is based on Active Directory. It is a a subset of information from the accounts in AD. Every entry you see in the GAL has a corresponding entry in AD. Are the names you’re trying to add to the groups the names of people in your organization or are they people outside of your organization?

  13. Hi Lee,
    Thanks for the quick response. Yes, I am very sure about all the fields and I’ve entered them in cells A1 and B1 respectively. I can add the details manually, but not with the script. So, maybe something is skipping somewhere :S

    • There are only three conditions I can think of that would result in “Failed” being written to the spreadsheet.

      1. The script could not find an AD account with the email address specified.
      2. The script could not find a group with the group name specified.
      3. You do not have the necessary permissions to add the account to the group.

        When I gt a minute I’ll modify the script to differentiate between the first two (i.e. it will tell you that it couldn’t find an account with that email address, or that it couldn’t find a group with that name).

  14. Hi Lee,
    I tried using all possible domain name combinations from my end and I finally found one that worked. The script ran and said “Process Complete”. However, if I open the Excel file it says “Failed” in column ‘C’ and the person isn’t added to the DL. I’ve tried with various email address formats “SMTP, X500,X400, etc”…All end up failing. I still cannot get it to add names to the DL successfully 😦 can you help pls?

      • Are the items in the right columns in the spreadsheet (i.e. the email address is in column A and the group name is in column B)?
      • Are you certain that the email addresses are correct?
      • Are you certain that each of the email addresses matches an account in your Active directory?
      • Are you certain that the group names are correct?

      The address must be an SMTP address.

  15. Hi Lee,

    Yes, I’m entering the domain account in line#2 as “xxx\xxx” that I use to login to my PC on a domain network and it’s the same on Active Directory also. Somehow, the operation fails and says “Server Not Operational; Line:89 Character:10″;Source:”Active Directory”. I’ve no clue what’s going on as :S

    • I’ve never seen an AD domain name with a “\” in it, so my guess is that the domain name is wrong and that’s what’s causing the problem. AD domain names are typically things like “company.com”, “company.org”, “department.gov”. Perhaps you could check with your IT department.

  16. Hi Lee,

    I tried changing the filename to something else and now I’m getting another weird error: “The Server is not operational; Line:89 Character:10″;Source:”Active Directory”. I really don’t know what that means. I do have access to the PDL and I can manually add members. Could you also please confirm that the .open() statement used is correct?

  17. I downloaded the code and it still shows the same error 😦 Also, under the Function Get_AD_Object_Path(strQuery), I see .open() or is it supposed to be .open = ()
    Is there something else that is missing? Should i give my script name where it says “Add Users To Groups” in line#3?

    • If it’s the same error on the same line, then I don’t see how that’s possible. Character 6 on line 3 is the space between “Const” and “SCRIPT_NAME”. Are you sure it’s the same error in the same place? No, you don’t need to change the value of SCRIPT_NAME.

  18. Hi Lee,
    Well, I followed the instructions as above and when I try to run the script it shoots an error saying “Invalid Character, Line:3; Character:6” Any idea what’s going on? I named the script with a .vbs extension and saved it as a Unicode Text file under encoding. Is that fine? I also assume I need not mention the script name inside the coding. Is there anything else I’ve to modify in the code?

    Thanks for letting me know!!!

    • Yes, I know what’s wrong. Sometimes when I copy and paste something into WordPress’ editor it changes quotes, ampersands, and a few other characters into their HTML equivalents. I just went through the code and fixed them. Please download the code again.

  19. Hi David Lee,

    Thanks a bunch for taking the extra time and effort to put this code together 🙂 I haven’t tested this code out yet but really like your approach. But, I have a few clarifications before I test it out. What exactly should i find and replace? Do I replace “&quot” and “company.com&quot” everywhere in the script or just the 2nd line?

    Please let me know.

    Thanks a lot!!!

    • Ashwath,

      You’re welcome. The only change you need to make is to replace “company.com” on line #2 with the name of your domain.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s