Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Query the email address of each user in an OU and add the Email address of the user to the Notes box.

Hi,

Query the email address of each user in an OU and add the Email address of the user to the Notes box.
Like
Email Address : Sharath@plc.com

Can a script query the Users in active directory and do this.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 22
  • 11
  • 5
1 Solution
 
David LeeCommented:
Hi, Sharath.

Yes, it's possible, but I'll need to know which notes box you are talking about.  
0
 
bsharathAuthor Commented:
In Active directory each user has a Notes box.
Username > Properties > Phones. Notes
In outlook when we see a users properties we can see the entered data in there
0
 
bsharathAuthor Commented:
Attached image
ScreenShot003.bmp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
David LeeCommented:
Ok, definitely doable.  I'm curious though, why do you want the address in the notes?  It's already visible.
0
 
bsharathAuthor Commented:
But not selectable. when i do some quick entries to my excel. I need to get the email addreses. But there is no place in the GAL that i get them being able to select.

Is there a way to select?
0
 
David LeeCommented:
Going through all of those clicks to get that info seems cumbersome.  Would you like a simpler way of doing this?  How about entering the person's name, account name, or something like that and having their email address copied directly to the clipboard so you can just paste it into whatever you're doing?
0
 
bsharathAuthor Commented:
Ok then can this be done as i have both the Ntlogin and Email address in a txt file or excel.
If in txt file like
NTlogin;Email address
Or an excel
Colum A                 Colum B
Ntlogin                  Email address
Can a script query each Ntlogin and paste the email address to the Notes box as this.

Email address : Sharath@plc.com
0
 
bsharathAuthor Commented:
Can this same script be used to update the email addresse in colum "O" to the notes as " Email Address : mail id"
http://www.experts-exchange.com/Software/Server_Software/File_Servers/Active_Directory/Q_23738740.html
0
 
David LeeCommented:
I'm confused.  I asked why you'd want the address in the Notes field and you replied, "I need to get the email addreses. But there is no place in the GAL that i get them being able to select."

Based on that satetment, I concluded that you must want them in the Notes field so you can copy and paste them into Excel (or whatever).  By my count it takes at least four mouse clicks to get to the Notes field in Outlook.  So, I proposed a means of doing this that would save all those clicks and perform half the work, copying the address to the clipboard, for you and your response is that you still want to copy the address to the Notes field.  I must not be understanding the goal properly.  Why would you want the address in the Notes field if you can get it copied straight to the clipboard without ever having to go to the Notes field and do that manually?  
0
 
bsharathAuthor Commented:
Sorry to get you confused.
There are 2 reasons.
This is required for many employers say managers who fill in there excel with all the employer details would love to have it in Notes so they can go there and get the mail id copied.I cannot give everyone a script to do this... :-(
Second is i have Hta's that query the notes and get the details and even scripts in bulk query fetck the data in the notes into excel when i query by user name/Location/machine Name thats in the Notes already.

So thats why i though if it can be updated the way other details get updated in the above link i posted that would solve my issue.

0
 
David LeeCommented:
Ok, I understand.  This should do it.  Copy and paste the code into Notepad.  Edit the LDAP string, then save the file with a .vbs extension.  Double-click to run it.  
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 
'Edit the LDAP string on the following line to point to the OU you want to run against'
FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        Set objUser = GetObject(adoRS.Fields("ADsPath"))
        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")
        objUser.SetInfo
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
bsharathAuthor Commented:
Do i need to give it a file that has NTlogin & Email address or will this take it automatically.

Will i have a header like
Email address : and the mail ID
0
 
David LeeCommented:
Ok, I'm confused. again.  The question was "Query the email address of each user in an OU and add the Email address of the user to the Notes box."  Everything is done in AD.  The code reads all the users in that OU in AD, gets their mail address. then copies the email address in the form

    Email address: someone@company.com

to the Notes field in AD.  Where does a file come into play?
0
 
bsharathAuthor Commented:
Thats exactly what i wanted ...
Thank U....
Shall check and get back...
0
 
bsharathAuthor Commented:
How can i get this from
servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com
Any place on the DC this is mentioned...
0
 
David LeeCommented:
I can't answer the question because I don't know anything about your Active Directory structure.  All I can say is that you need to edit that string to point to the OU you want the script to run against.  Assuming the following values

Company Name: PLC
Server Name: Alpha
Target OU: Users

the string would be

    alpha.plc.com/ou=Users,dc=plc,dc=com
0
 
bsharathAuthor Commented:
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      5
Char:      64
Error:      Unterminated string constant
Code:      800A0409
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
0
 
bsharathAuthor Commented:
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      5
Char:      64
Error:      Unterminated string constant
Code:      800A0409
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
0
 
David LeeCommented:
Oops.  My fault.  I inserted the comment line in the wrong place.  Move the comment on line #6 up a line.
0
 
bsharathAuthor Commented:
As this
Still get the error...
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
'Edit the LDAP string on the following line to point to the OU you want to run against'
 
Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 
 
FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        Set objUser = GetObject(adoRS.Fields("ADsPath"))
        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")
        objUser.SetInfo
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
David LeeCommented:
Line 8 shouldn't be blank.  Delete it.  Lines 7 and 9 are really one line that wraps to two lines.
0
 
bsharathAuthor Commented:
Sorry still get the error.
Can you post in the full code...Please...
0
 
David LeeCommented:
Here you go.
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
'Edit the LDAP string on the following line to point to the OU you want to run against'
Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 
FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        Set objUser = GetObject(adoRS.Fields("ADsPath"))
        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")
        objUser.SetInfo
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
bsharathAuthor Commented:
I get this now...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      11
Char:      9
Error:      The requested operation did not satisfy one or more constraints associated with the class of the object.
Code:      80072014
Source:       (null)

---------------------------
OK  
---------------------------
0
 
bsharathAuthor Commented:
I get this now...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      11
Char:      9
Error:      The requested operation did not satisfy one or more constraints associated with the class of the object.
Code:      80072014
Source:       (null)

---------------------------
OK  
---------------------------
0
 
rejoinderCommented:
I have modified the script to point to the "info" field which in Outlook looks like the "Notes" field.  This version will bypass users with no email address and will preserve the existing data that is currently in the "info" box.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html
 
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
'Edit the LDAP string on the following line to point to the OU you want to run against'
Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then
            'Do nothing - the user has no email address
        else
            strAddNewLine = ""
            if IsNull(adoRs.Fields("info")) or adoRs.Fields("info") = "" then
                strAddNewLine = vbCRLF
            end if
            Set objUser = GetObject(adoRS.Fields("ADsPath"))
            objUser.Put "Info", adoRs.Fields("info") & strAddNewLine & "Email address: " & adoRs.Fields("mail")
            objUser.SetInfo
        end if
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
David LeeCommented:
Good catch, rejoinder.
0
 
bsharathAuthor Commented:
Thank you Rejoinder works perfect....
But it gets mixes with the other data in the Notes.
Can it be placed to the last available row.

As i have 3 rows of data in the Notes already.
Can this be the 4th Row...
0
 
bsharathAuthor Commented:
Thank you Rejoinder works perfect....
But it gets mixes with the other data in the Notes.
Can it be placed to the last available row.

As i have 3 rows of data in the Notes already.
Can this be the 4th Row...
0
 
rejoinderCommented:
Hmmm, I am not sure how to fix that.  When I look at the code it should append the email text after what is already there.  I had no idea that it would end up in the middle or beginning.  I am not sure what is doing that.
0
 
bsharathAuthor Commented:
Ok the notes has data as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah

Now what is happening is the email address is getting into as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah Email address : Blah

Actually it has to be as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah
Email address : Blah
0
 
bsharathAuthor Commented:
Ok the notes has data as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah

Now what is happening is the email address is getting into as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah Email address : Blah

Actually it has to be as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah
Email address : Blah
0
 
rejoinderCommented:
I had a case of backwards logic.  This will look better.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html
 
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
'Edit the LDAP string on the following line to point to the OU you want to run against'
Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then
            'Do nothing - the user has no email address
        else
            strAddNewLine = vbCRLF
            if IsNull(adoRs.Fields("info")) or adoRs.Fields("info") = "" then
                strAddNewLine = ""
            end if
            Set objUser = GetObject(adoRS.Fields("ADsPath"))
            objUser.Put "Info", adoRs.Fields("info") & strAddNewLine & "Email address: " & adoRs.Fields("mail")
            objUser.SetInfo
        end if
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
bsharathAuthor Commented:
Thank U worked fine... But now need to remove the other one which got placed... :-(

Now i have 2 Email addresses...
Can the script clear anything in notes and place just this email address.
So again i can run the other scripts to place the serial no,machine name etc....

0
 
bsharathAuthor Commented:
Thank U worked fine... But now need to remove the other one which got placed... :-(

Now i have 2 Email addresses...
Can the script clear anything in notes and place just this email address.
So again i can run the other scripts to place the serial no,machine name etc....

0
 
rejoinderCommented:
I am so sorry about that mistake.
This version will now clear up the mistake from earlier within the notes field and will put the email address on the last line, perserving what is already there.

As a precaution, you can move a few users into a seperate OU and test the script against them.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html
 
Dim adoCon, adoRS, objUser
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Provider = "ADsDSOObject"
adoCon.Open "ADSI"
'Edit the LDAP string on the following line to point to the OU you want to run against'
Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")
If Not adoRS.EOF Then
    Do While Not adors.EOF
        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then
            'Do nothing - the user has no email address
        else
            strValue = ""
            arrData  = split(adoRs.Fields("info"),vbCRLF)
            for n = lBound(arrData) to uBound(arrData)
                intPosition = InStr(arrData(n),"Email address:")
                if InStr(arrData(n),"Email address:") > 2 then
                    strTemp = mid(arrData(n),1,intPosition-1)
                    arrData(n) = strTemp
                end if
                strSearchText = "Email address: " & adoRs.Fields("mail")
                if arrData(n) <> "" AND arrData(n) <> strSearchText then
                    strValue = strValue & arrData(n) & vbCRLF
                end if
            next
            strValue = strValue & strSearchText
            Set objUser = GetObject(adoRS.Fields("ADsPath"))
            objUser.Put "Info", strValue
            objUser.SetInfo
        end if
        adoRS.MoveNext
    Loop
End If
adors.Close
Set adoRS = Nothing
adoCon.Close
Set adoCon = Nothing
WScript.Echo "All done!"

Open in new window

0
 
bsharathAuthor Commented:
Thank you both for such great help...
Worked perfect by removing the duplicates and adding just one... :-))
0
 
rejoinderCommented:
Again, I am sorry about the mistake and I am glad it worked out in the end.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 22
  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now