Avatar of Bhaskar CP
Bhaskar CPFlag for India

asked on 

Secondary email address in AD

I need to export the secondary email address of all users in AD. I need it to be displayed in a acceptable format.
below are the tabs i need

First Name Last name Initials sAMAccountName displayName Email ID Secondary Email ID

Active Directory

Avatar of undefined
Last Comment
Bhaskar CP
Avatar of Robin Human
Robin Human
Flag of South Africa image

Your best bet would be to run a scrip for this;
see this thread and tweak it to add the extra mail addresses
Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


Well... i was able to get all the values even earlier but not secondaryemail (proxy addresses). i use the ADFIND by joeware.net.
      the problem is that the attribute proxyAddresses returns a lot of junk along with the additional email address. it takes ages to modify and get the core information.
Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


adfind -default -nodn -bit -csv -tdcs -binenc -f "&(objectcategory=person)(objectclass=user)(useraccountcontrol=*)(!(useraccountcontrol:AND:=2))(|(sAMAccountName=U*)(sAMAccountName=V*))(|(mail=*simonandschuster.com)(mail=*simonsays.com)(mail=*howardpublishing.com)(mail=*simonandschuster.ca))" cn givenName Initials sAMAccountName displayName mail mailNickName Department TelephoneNumber proxyAddresses> 123.csv

i use the above script.
Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


for example it returns values like this

"X500:/o=Howard Publishing/ou=First Administrative Group/cn=Recipients/cn=UHiltBe;X500:/o=Schuster/ou=First Administrative Group/cn=Recipients/cn=UHiltBe;x400:c=US\;a= \;p=Schuster\;o=Exchange\;s=DisabledMail\;;X400:c=US\;a= \;p=Schuster\;o=Exchange\;s=Hilton\;g=johnny\;;X500:/o=Viacom/ou=Schuster/cn=Recipients/cn=UHiltBe;smtp:johnny.Hilton@relay.Simonandschuster.com;smtp:johnny_Hilton@Simonandschuster.com;smtp:johnny.hilton@simonsays.com;smtp:johnny.hilton@simonandschuster.ca;SMTP:johnny.Hilton@Simonandschuster.com"
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Considering that there are multiple secondary SMTP addresses... how do you want them to be displayed?

Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


like in columns
First Name Last name Initials sAMAccountName displayName Email ID Secemail1 secemail2 secemail3

A max of 3 secondary addresses will do for me to live with..
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Heh probably easier to give you all of them if that's okay.

Fancy giving this one a try? VbScript, so save as .vbs. It'll save the output as out.csv.


Const REPORT_FILE = "out.csv"
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFileSystem.OpenTextFile(REPORT_FILE, 2, True, 0)
objFile.WriteLine "First Name,Last name,Initials,sAMAccountName,displayName,Email ID,Secemail"
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Dim objCommand : Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT givenName, sn, initials, sAMAccountName, " &_
  "displayName, mail, proxyAddresses " &_
  "FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") &_
  "' WHERE objectClass='user' AND objectCategory='person' AND proxyAddresses='*'"
Set objRootDSE = Nothing
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False
Dim objRecordSet : Set objRecordSet = objCommand.Execute
Dim strGivenName, strSN, strInitials, strSamAccountName, strDisplayName
Dim strMail
Dim arrProxyAddresses, arrTemp()
Dim i
While Not objRecordSet.EOF
  On Error Resume Next
  strGivenName = "" : strGivenName = objRecordSet.Fields("givenName").Value
  strSN = "" : strSN = objRecordSet.Fields("sn").Value
  strInitials = "" : strInitials = objRecordSet.Fields("initials").Value
  strSamAccountName = "" : strSamAccountName = objRecordSet.Fields("sAMAccountName").Value
  strDisplayName = "" : strDisplayName = objRecordSet.Fields("displayName").Value
  strMail = "" : strMail = objRecordSet.Fields("mail")
  arrProxyAddresses = objRecordSet.Fields("proxyAddresses").Value
  On Error Goto 0
  i = 0
  For Each strAddress in arrProxyAddresses
    If InStr(strAddress, "smtp:") > 0 Then
      ReDim Preserve arrTemp(i)
      arrTemp(i) = Replace(strAddress, "smtp:", "")
      i = i + 1
    End If
  objFile.WriteLine strGivenName & "," & strSN & "," & strInitials & "," & strSamAccountName &_
    "," & strDisplayName & "," & strMail & "," & Join(arrTemp, ",")
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing

Open in new window

Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


I made on script myself which i feel best suits me now.. Really thank you for your help..

Dim ObjWb 
Dim ObjExcel 
Dim x, zz 
Set objRoot = GetObject("LDAP://RootDSE") 
strDNC = objRoot.Get("DefaultNamingContext") 
Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the sns Domain
Call ExcelSetup("Sheet1") ' Sub to make one Excel Document 
x = 1 
Call enummembers(objDomain) 
Sub enumMembers(objDomain) 
On Error Resume Next 
Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's 
For Each objMember In objDomain ' go through the collection 
If ObjMember.Class = "user" Then ' if not User object, move on. 
x = x +1 ' counter used to increment the cells in Excel 
  objwb.Cells(x, 1).Value = objMember.Class 
  ' I set AD properties to variable as exceptions for ids without address can be removed coz so may address we dont have in AD. 
SamAccountName = ObjMember.samAccountName 
Cn = ObjMember.CN 
FirstName = objMember.GivenName 
LastName = objMember.sn 
initials = objMember.initials 
Descrip = objMember.description 
Office = objMember.physicalDeliveryOfficeName 
Telephone = objMember.telephonenumber 
EmailAddr = objMember.mail 
WebPage = objMember.wwwHomePage 
Addr1 = objMember.streetAddress 
City = objMember.l 
State = objMember.st 
ZipCode = objMember.postalCode 
Title = ObjMember.Title 
Department = objMember.Department 
zz = 1 ' Counter for array of 2ndary email addresses 
For each email in ObjMember.proxyAddresses 
   If Left (email,5) = "SMTP:" Then 
Primary = Mid (email,6) ' if SMTP is all caps, then it's the Primary 
   ElseIf Left (email,5) = "smtp:" Then
       'If Right(email,27) <> "@relay.Simonandschuster.com" Then
 If UCase(Right(email,21)) = "@SIMONANDSCHUSTER.COM" Then
      Secondary(zz) = Mid (email,6) ' load the list of 2ndary SMTP emails into Array. 
      zz = zz + 1 
      End If
   End If 
' Write the values to Excel, using the X counter to increment the rows. 
objwb.Cells(x, 2).Value = SamAccountName 
objwb.Cells(x, 3).Value = CN 
objwb.Cells(x, 4).Value = FirstName 
objwb.Cells(x, 5).Value = LastName 
objwb.Cells(x, 6).Value = Initials 
objwb.Cells(x, 7).Value = Descrip 
objwb.Cells(x, 8).Value = Office 
objwb.Cells(x, 9).Value = Telephone 
objwb.Cells(x, 10).Value = EmailAddr
objwb.Cells(x, 11).Value = WebPage 
objwb.Cells(x, 12).Value = Addr1 
objwb.Cells(x, 13).Value = City 
objwb.Cells(x, 14).Value = State 
objwb.Cells(x, 15).Value = ZipCode 
objwb.Cells(x, 16).Value = Title 
objwb.Cells(x, 17).Value = Department 
objwb.Cells(x,18).Value = Primary 
' Write out the Array for the 2ndary email addresses. 
For ll = 1 To 20 
objwb.Cells(x,26+ll).Value = Secondary(ll) 
' Blank out Variables in case the next object doesn't have a value for the property 
SamAccountName = "-" 
Cn = "-" 
FirstName = "-" 
LastName = "-" 
initials = "-" 
Descrip = "-" 
Office = "-" 
Telephone = "-" 
EmailAddr = "-" 
WebPage = "-" 
Addr1 = "-" 
City = "-" 
State = "-" 
ZipCode = "-" 
Title = "-" 
Department = "-" 
Primary = "-" 
For ll = 1 To 20 
Secondary(ll) = "" 
  End If 
  ' If the AD enumeration runs into an OU object, call the Sub again to itinerate 
  If objMember.Class = "organizationalUnit" or OBjMember.Class = "container" Then 
      enumMembers (objMember) 
  End If 
End Sub 
Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds Column heads to the 1st row 
Set objExcel = CreateObject("Excel.Application") 
Set objwb = objExcel.Workbooks.Add 
Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName) 
Objwb.Name = "Active Directory Users" ' name the sheet 
objExcel.Visible = True 
objwb.Cells(1, 2).Value = "SamAccountName" 
objwb.Cells(1, 3).Value = "CN" 
objwb.Cells(1, 4).Value = "FirstName" 
objwb.Cells(1, 5).Value = "LastName" 
objwb.Cells(1, 6).Value = "Initials" 
objwb.Cells(1, 7).Value = "Descrip" 
objwb.Cells(1, 8).Value = "Office" 
objwb.Cells(1, 9).Value = "Telephone" 
objwb.Cells(1, 10).Value = "Email" 
objwb.Cells(1, 11).Value = "WebPage" 
objwb.Cells(1, 12).Value = "Addr1" 
objwb.Cells(1, 13).Value = "City" 
objwb.Cells(1, 14).Value = "State" 
objwb.Cells(1, 15).Value = "ZipCode" 
objwb.Cells(1, 16).Value = "Title" 
objwb.Cells(1, 17).Value = "Department" 
objwb.Cells(1, 26).Value = "Primary SMTP" 
End Sub 
MsgBox "Done" ' show that script is complete 

Open in new window

Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


Thanks a lot for the help...
Avatar of Bhaskar CP
Bhaskar CP
Flag of India image


i happen to use my own solution
Active Directory
Active Directory

Active Directory (AD) is a Microsoft brand for identity-related capabilities. In the on-premises world, Windows Server AD provides a set of identity capabilities and services, and is hugely popular (88% of Fortune 1000 and 95% of enterprises use AD). This topic includes all things Active Directory including DNS, Group Policy, DFS, troubleshooting, ADFS, and all other topics under the Microsoft AD and identity umbrella.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo