We help IT Professionals succeed at work.

VBscript to list all mail enabled active directory groups with their members

Medium Priority
1,490 Views
Last Modified: 2012-06-21
I am looking for a vbscript that will list all active directory groups that are mail enabled with the members of each group.  You would think this would be easy to find, but I've been googling for a couple days and havent found exactly what I need.

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi there, as a start, see if this provides you with all of the mail enabled group names. It just checks whether an email address has been set or not.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
cmd.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
cmd.Properties("Page Size") = 1000
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = cmd.Execute
strResults = "Mail enabled groups:"
While Not objRS.EOF
	strResults = strResults & VbCrLf & objRS.Fields("cn").Value
	objRS.MoveNext
Loop
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.txt", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.txt"

Open in new window

Author

Commented:
Hi, thanks for the script, but I'm getting the following errors:

(22, 1) Microsoft VBScript compilation error: 'loop' without 'do'
I changed line 22 from Loop to Wend, then I'm getting the following:

(12, 1) Microsoft VBScript runtime error: Object required: 'cmd'

Thanks for any help.

David
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Oh sorry....that's what I get for not testing.....try this.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
objCommand.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 300
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = objCommand.Execute
strResults = "Mail enabled groups:"
While Not objRS.EOF
	strResults = strResults & VbCrLf & objRS.Fields("cn").Value
	objRS.MoveNext
Wend
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.txt", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.txt"

Open in new window

Author

Commented:
Thank you, that works perfectly.  Now, how can I add the display name for each member of each group.  I would like the output to appear something like this:

Human Resources          John Smith, Pete Wright, Sally Jones
Payroll                             Denise Smith, Celia Smith
...
...

Thanks,
David

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Do you want the output in CSV or Text format?

Rob.

Author

Commented:
CSV would be great.  Thank you.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Hi, see if this output is OK for you.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
objCommand.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 300
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = objCommand.Execute
strResults = """Group Name"",""Members"""
While Not objRS.EOF
	Set objGroup = GetObject(objRS.Fields("adsPath").Value)
	strMembers = ""
	If TypeName(objGroup.Member) = "Empty" Then
		strMembers = ""
	ElseIf TypeName(objGroup.Member) = "String" Then
		strMembers = Mid(Split(objGroup.Member, ",")(0), 4)
	Else
		For Each strMember In objGroup.Member
			If strMembers = "" Then
				strMembers = Mid(Split(strMember, ",")(0), 4)
			Else
				strMembers = strMembers & ";" & Mid(Split(strMember, ",")(0), 4)
			End If
		Next
	End If
	strResults = strResults & VbCrLf & """" & objGroup.cn & """,""" & strMembers & """"
	objRS.MoveNext
Wend
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.csv", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.csv"

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you Rob, this is exactly what I needed.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem.  Thanks for the grade.

Regards,

Rob.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.