Solved

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

Posted on 2008-10-26
38
342 Views
Last Modified: 2012-05-05
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
Comment
Question by:bsharath
  • 22
  • 11
  • 5
38 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22807266
Hi, Sharath.

Yes, it's possible, but I'll need to know which notes box you are talking about.  
0
 
LVL 11

Author Comment

by:bsharath
ID: 22807483
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
 
LVL 11

Author Comment

by:bsharath
ID: 22807490
Attached image
ScreenShot003.bmp
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 76

Expert Comment

by:David Lee
ID: 22807524
Ok, definitely doable.  I'm curious though, why do you want the address in the notes?  It's already visible.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22807531
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22811446
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
 
LVL 11

Author Comment

by:bsharath
ID: 22811474
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
 
LVL 11

Author Comment

by:bsharath
ID: 22811515
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22811621
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
 
LVL 11

Author Comment

by:bsharath
ID: 22811667
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22811894
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
 
LVL 11

Author Comment

by:bsharath
ID: 22812034
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22812087
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
 
LVL 11

Author Comment

by:bsharath
ID: 22812112
Thats exactly what i wanted ...
Thank U....
Shall check and get back...
0
 
LVL 11

Author Comment

by:bsharath
ID: 22820555
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22820654
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
 
LVL 11

Author Comment

by:bsharath
ID: 22820756
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
 
LVL 11

Author Comment

by:bsharath
ID: 22820757
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22820800
Oops.  My fault.  I inserted the comment line in the wrong place.  Move the comment on line #6 up a line.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22820892
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22820941
Line 8 shouldn't be blank.  Delete it.  Lines 7 and 9 are really one line that wraps to two lines.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22832476
Sorry still get the error.
Can you post in the full code...Please...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22832771
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
 
LVL 11

Author Comment

by:bsharath
ID: 22837718
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
 
LVL 11

Author Comment

by:bsharath
ID: 22837719
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
 
LVL 14

Expert Comment

by:rejoinder
ID: 22844775
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
 
LVL 76

Expert Comment

by:David Lee
ID: 22845969
Good catch, rejoinder.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22847292
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847293
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
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847686
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847694
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847695
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
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847707
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847774
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847775
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
 
LVL 14

Accepted Solution

by:
rejoinder earned 500 total points
ID: 22847932
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
 
LVL 11

Author Comment

by:bsharath
ID: 22847940
Thank you both for such great help...
Worked perfect by removing the duplicates and adding just one... :-))
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847951
Again, I am sorry about the mistake and I am glad it worked out in the end.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question