Pull email ids for a group of people in exchange 2007

amku03
amku03 used Ask the Experts™
on
Exchange 2007
Windows 2003 AD

I have a list of users in a spread sheet with columns as their First Name, Last Name and Full Name. And a Blacnk column for email ids
I need to pull their email IDs  from Exchange / AD.

How can i get this done through powershell?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
$users = get-content C:\users.txt
foreach($user in $users){
get-mailbox $user | select-object  PrimarySmtpAddress
}

Author

Commented:
how can i use the existing xls file?

Commented:
In your file is fullname the users login name? It would be easier to change to a csv and do this.

$users = import-csv C:\users.csv
foreach($user in $users){
get-mailbox $user.fullname | select-object  PrimarySmtpAddress
}
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
I meant to say,

I want ot use the XLS file which has First Name and Last name field for users.

Author

Commented:
I am sorry must have posted my last response at the same time.
In the spread sheet , i have been given users first name and last name and i need to update the email ids for them.
I will try your last suggestion.

Author

Commented:
ok that helped, but its going in loop and giving warning for

WARNING: By default only the first 1000 items are returned. To change the number of items
returned, specify the parameter "-ResultSize". To return all items specify "-ResultSize Unlimited"
 (Note: Returning all items may take a very long time and consume a large amount of memory
depending on the actual number of items). It is not recommended to store the results in a
variable; instead pipe the results to another task or script to perform batch changes.

and is it possible to populate the same csv file with the output for the corresponding users?

I hope i am not asking for too much automation here :)

cheers!

Commented:
Try this, it will not update the original csv but it will create a new one.

$outfile = @()
$users = import-csv C:\users.csv
foreach($user in $users){
$email = get-mailbox $user.fullname "-ResultSize Unlimited | select-object  PrimarySmtpAddress
$outfile += "$(user.firstname),$(user.Lastname),"$($email)"
}
$outfile | out-file c:\usersout.csv

Commented:
mistake in last post

$email = get-mailbox $user.fullname -ResultSize Unlimited | select-object  PrimarySmtpAddress
$outfile += "$(user.firstname),$(user.Lastname),"$($email.PrimarySmtpAddress)"
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
add -resultsize unlimited after the get-mailbox cmdlet like the attached code.
$users = import-csv C:\users.csv
foreach($user in $users){
get-mailbox $user.fullname -resultsize unlimited | select-object  PrimarySmtpAddress
}

Open in new window

Author

Commented:
getting error:

Unexpected token '$(' in expression or statement.
At C:\Users.ps1:5 char:52
+ $outfile += "$(user.firstname),$(user.Lastname),"$($ <<<< email)"

Author

Commented:
sorry still getting some error with teh corrected post:

[PS] C:\>.\users.ps1
Unexpected token '$(' in expression or statement.
At C:\Users.ps1:5 char:52
+ $outfile += "$(user.firstname),$(user.Lastname),"$($ <<<< email.PrimarySmtpAddress)"
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
Pull the " from in front of $($email.PrimarySmtpAddress)

Author

Commented:
Getting error:

[PS] C:\>.\users.ps1
The term 'user.firstname' is not recognized as a cmdlet, function, operable program, or script fil
e. Verify the term and try again.
At line:1 char:14
+ user.firstname <<<<
The term 'user.Lastname' is not recognized as a cmdlet, function, operable program, or script file
. Verify the term and try again.
At line:1 char:13
+ user.Lastname <<<<
The term 'user.firstname' is not recognized as a cmdlet, function, operable program, or script fil
e. Verify the term and try again.
At line:1 char:14
+ user.firstname <<<<
The term 'user.Lastname' is not recognized as a cmdlet, function, operable program, or script file
. Verify the term and try again.
At line:1 char:13
+ user.Lastname <<<<

Commented:
If fullname on your imported file the users login name?

it should not require the -sizelimit it should only return 1 account.

Author

Commented:
As suggested this is what i have in the script file:

$outfile = @()
$users = import-csv C:\users.csv
foreach($user in $users){
$email = get-mailbox $user.fullname -ResultSize Unlimited | select-object  PrimarySmtpAddress
$outfile += "$(user.firstname),$(user.Lastname),$($email.PrimarySmtpAddress)"
}
$outfile | out-file c:\usersout.csv

Commented:
Can you post an example of your xls file

Author

Commented:
this is the sample csv file, renamed as .txt
original file has 3000+ names :)
Users.txt

Author

Commented:
here is a sample from the xls file
Book1.xls

Commented:
I would change the header fields to remove the spaces, it will make it easier to script.
Then since you only have first name and last name I would use the quest ADcmdlets instead of the exchnage cmdlets. So something like this

$outfile = @()
$users = import-csv C:\users.csv
foreach($user in $users){
$email = get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname)(SN=$($user.lastname)))"
$outfile += "$(email.firstname),$(email.Lastname),$($email.PrimarySMTPAddress)"
}
$outfile | out-file c:\usersout.csv

Author

Commented:
so which means i need to insatll quest AD cmdlest too?

Commented:
Do you have any 2008R2 domain controllers?

If you can get the users logon name you can do it from the exchange tools. I will check to see if you can do any type of queries with just first and last name.

But with first and last name I think the Quest ADcmdlets are the easiest to use.

Author

Commented:
got the quest stuff insatlled
now getting error:

[PS] C:\>.\users.ps1
Get-QADUser : The search filter is invalid.
At C:\Users.ps1:4 char:21
+ $email = get-qaduser  <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname)(SN=
$($user.lastname)))"
The term 'email.firstname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term a
nd try again.
At line:1 char:15
+ email.firstname <<<<
The term 'email.Lastname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term an
d try again.
At line:1 char:14
+ email.Lastname <<<<

Author

Commented:
i do not have any 2008 DC

Commented:
Just to make sure there was no line wrap. try this
$outfile = @()
$users = import-csv C:\users.csv
foreach($user in $users){
$email = get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname)(SN=$($user.lastname)))"
$outfile += "$(email.firstname),$(email.Lastname),$($email.PrimarySMTPAddress)"
}
$outfile | out-file c:\usersout.csv

Open in new window

Commented:
Sorry, i think i found the mistake
get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname))(SN=$($user.lastname)))"

Open in new window

Author

Commented:
tried this...its still the same....sorry about that.

Author

Commented:
could you suggest a script which can be used to pull the logon name for these users and based on that we can pull email id.

Commented:
Just tested this one. Make sure your header file does not have a space. This query may find multiple matches so double check the output.

here is what mine looks like

FirstName,LastName
Joe,User
Jane,Doe
John,Doe
add-pssnapin *Auest*
$outfile = @()
$users = import-csv C:\testing123\users.csv
Foreach ($user in $users){
$user.firstname
    $email = get-qaduser -LDAPFilter "(&(objectcategory=person)(objectclass=user)(GivenName=$($user.FirstName))(SN=$($user.LastName)))"
    Foreach ($e in $email){

    $outfile += "$($e.firstname),$($e.Lastname),$($e.PrimarySMTPAddress)" 
    }
    }
     
$outfile | out-file c:\usersout.csv

Open in new window

Author

Commented:
sorry still error...

Commented:
What is the error.

Author

Commented:
Get-QADUser : The search filter is invalid.
At C:\Users.ps1:4 char:21
+ $email = get-qaduser  <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname))(SN
=$($user.lastname)))"
The term 'email.firstname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term a
nd try again.
At line:1 char:15
+ email.firstname <<<<
The term 'email.Lastname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term an
d try again.
At line:1 char:14
+ email.Lastname <<<<
Get-QADUser : The search filter is invalid.
At C:\Users.ps1:4 char:21
+ $email = get-qaduser  <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname))(SN
=$($user.lastname)))"
The term 'email.firstname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term a
nd try again.
At line:1 char:15
+ email.firstname <<<<
The term 'email.Lastname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term an
d try again.
At line:1 char:14
+ email.Lastname <<<<
Get-QADUser : The search filter is invalid.
At C:\Users.ps1:4 char:21
+ $email = get-qaduser  <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname))(SN
=$($user.lastname)))"
The term 'email.firstname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term a
nd try again.
At line:1 char:15
+ email.firstname <<<<
The term 'email.Lastname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term an
d try again.
At line:1 char:14
+ email.Lastname <<<<
Get-QADUser : The search filter is invalid.
At C:\Users.ps1:4 char:21
+ $email = get-qaduser  <<<< -LDAPFilter "(&(objectcategory=person)(objectclass=user)(givenname=$($users.firstname))(SN
=$($user.lastname)))"
The term 'email.firstname' is not recognized as a cmdlet, function, operable program, or script file. Verify the term a
nd try again.

Commented:
Can you verify you are testing with the last one I posted.

This variable is not correct $users.firstname))( and I see a few others.
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
Try this one. Should work from the Exchange Shell.
"First Name, Last Name, Email Address" > c:\emails.csv
import-csv c:\users.csv | foreach {$first, $last = $_.firstname, $_.lastname
get-mailbox -identity "$first $last" | foreach {$email = $_.primarysmtpaddress
"$first, $last, $email" >> c:\emails.csv}}

Open in new window

Commented:
acbrown2010:

I was trying to think of a way to use the exchange cmdlets to. But it will all depend on how the display name is set. For example in my test lab I have "LastName, FirstName" so the query will not work. This may work for amku03 though.

amku03

Let us know if you were able to test the one acbrown posted or the last one I posted.
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
You should be able to tweak the "$first $last" section to fit the display name to however it is set in the environment. LastName, FirstName would be "$last, $first"
If you use initials for the names, like F Fontana, you would need to do something like
$first = $first.Substring(0,1)  to chop off all but the first character of the first name, then do
"$first $last"
If you want to get an idea of what your display names are, just do a get-mailbox and it will list all of them out. The format in the first column is the one you want to match.

Author

Commented:
Guys, I will test this and will get back in sometime...

Author

Commented:
I am still unable to get this working.
BUT I AM THANKFUL TO YOU GUYS FOR HANDHOLDING!!

I will try more...

Author

Commented:
display name is like "FirstName LastName"

Commented:
Try this one using the get-mailbox cmdlet again.

One thing you will have to watch out for. The users in your list will need to match the display name exacltly. So for example if you have Robert in your file it must be Robert for the display name not Rob.

$outfile = @()  
$users = import-csv C:\testing123\users.csv  
Foreach ($user in $users){  
    $e = get-mailbox -identity "$($user.FirstName) $($user.LastName)"
    $outfile += "$($user.firstname),$($user.Lastname),$($e.PrimarySMTPAddress)"   
    }  
       
$outfile | out-file c:\usersout.csv

Open in new window

Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
What did you get out of the code I gave you? Any errors?

Author

Commented:
acbrown2010

i am getting this error:
At C:\Users.ps1:3 char:12
+ get-mailbox  <<<< -identity "$first $last" | foreach {$email = $_.primarysmtpaddress
Get-Mailbox : The operation could not be performed because object 'Adalgiza Serna' could not be fo
und on domain controller 'dc.domain.com'

Author

Commented:
i also have got a spread sheet now with their display names....
uploading it for your ref.

Author

Commented:
Last Name      First Name      Full Name
Winstanley      A      A (Michele) Winstanley
Couts      Aaron      Aaron Couts
Price      Aaron      Aaron Price
Vis      Aaron      Aaron Vis
Bejrowski      Abigail      Abigail (Abbie) Bejrowski

Commented:
For acbrown's example can you try this

get-mailbox -identity 'Adalgiza Serna'

How many mailboxes do you have in your env?
The spearsheet you have, is that all of your users?

Author

Commented:
i will try...
as for mailboxes, the spreadsheet has 3070 entries to be updated.

Commented:
You might be better off doing something like this

get-mailbox | select-object Displayname, PrimarySMTPAddress | out-file c:\users.csv

With that many users first and last name is not good to search on. Would you be able to get a unique ID for the users like login name, employeeID?

Author

Commented:
i think if we can get the logon name then it can be easy to pull the data.
now can you suggest a way to get the login names first and then oull email...

I know we can do this by ds query or dsget but not sure of full syntax or script...
the AD setup here is not consistent and thats why i feel we are having difficulty

any suggestion to handle this with a different approach?

Commented:
You should be able to do it with the quest tools
add-pssnapin *Quest*
$outfile = @()
$users = import-csv C:\Users.csv
Foreach($user in $users){
$sam = get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(SN=$($user.LastName))(GivenName=$($user.FirstName)))
foreach ($s in $sam){
$outfile += "$($user.firstname),$($user.Lastname),$($s.email),($($s.samaccountname)"
}
}

Author

Commented:
[PS] C:\>.\users.ps1
Encountered end of line while processing a string token.
At C:\Users.ps1:7 char:84
+ $outfile += "$($user.firstname),$($user.Lastname),$($s.email),($($s.samaccountname)" <<<<

sorry guys for being a pain....

Commented:
No problem.

There was a quote cut off when I pasted it before

($user.FirstName)))
should be

 ($user.FirstName))) "


I have tested this one and I know it works. If this one doesnt work it is the CSV file that is being imported.

Try this
$user = import-csv C:\user.csv

then type
$user

you should get something liek this

FirstName     LastName
-------------       ---------------
John                Doe
Jane                 Doe
add-pssnapin *Quest*
$outfile = @()
$users = import-csv C:\Users.csv
Foreach($user in $users){
$sam = get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(SN=$($user.LastName))(GivenName=$($user.FirstName)))"
foreach ($s in $sam){
$outfile += "$($user.first),$($user.Last),$($s.email),($($s.samaccountname)" 
}
}
$outfile | out-file c:\outuser.csv

Open in new window

Author

Commented:
thanks will try out this morning

Author

Commented:
How do i get rid of the space if that may be causing teh issue?

[PS] C:\>$user

FirstName                                         LastName
---------                                         --------
A                                                 Winstanley
Aaron                                             Couts
Aaron                                             Price
Aaron                                             Vis
Commented:
try this
add-pssnapin *Quest*  
$outfile = @()  
$users = import-csv C:\Users.csv  
Foreach($user in $users){  
$first = $user.Firstname -replace " ",""
$last = $user.Lastname -replace " ",""
$sam = get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(SN=$Last)(GivenName=$First))"  
foreach ($s in $sam){  
$outfile += "$first),$Last),$($s.email),($($s.samaccountname)"   
}  
}  
$outfile | out-file c:\outuser.csv

Open in new window

Author

Commented:
This is whats going on....
The csv has many entries with same first name and last name and the script is populating those with first.last@domain.com
I know its the problem with the way this list has been provided to me.
Because of which the cells have shifted with correct data...doing some manual shifting of cells...

Author

Commented:
this new script is perfect!!! looks promising...

Author

Commented:
as i have mentioned in other post, i have 4 domains.
Is there a way to run this ldap query for Entire Directory so that we can cover all domains?

Commented:
What I think you are going to want to do is this. Run that script in each domain and pull the users UPN. This is a unique attribute for the forest. Once you have that you would be able to use the get-mailbox cmdlet to get primary email from one list. Another option would be to create an array of all your domains and loop through that. In you list do you know what user is in which domain? If not you will probably get some false results with people having the same name.

Author

Commented:
i agree...
the output from the last script has come out well ...
was still wondering if we can add a parameter to include the domain name to search the account information....
that will make it perfect...
Thanks for all the help and time so far!!!

Commented:
You could try something like this. Just modify the existing script to include the following lines

$domains = "Dom1.root.local", "Dom2.root.Local", "Dom3.root.local"
Foreach($dom in $domains){
get-qaduser -service $($dom):389 -LDAPFilter **********************************
}

Author

Commented:
trying it now...

Author

Commented:
how do i get the user loop going, does this looks fine?

$outfile = @()  
$users = import-csv C:\Users.csv
$domains ="Dom1.root.local", "Dom2.root.Local", "Dom3.root.local"
Foreach($dom in $domains)
Foreach($user in $users){  
$first = $user.Firstname -replace " ",""
$last = $user.Lastname -replace " ",""
$sam = get-qaduser -service $($dom):389 -ldapfilter "(&(objectcategory=person)(objectclass=user)(SN=$Last)(GivenName=$First))"  
foreach ($s in $sam){  
$outfile += "$first,$Last,$($s.email),$($s.samaccountname)"  
}  
}  
$outfile | out-file c:\outuser.csv

Commented:
Try this one
$outfile = @()  
$users = import-csv C:\Users.csv 
$domains ="Dom1.root.local", "Dom2.root.Local", "Dom3.root.local"
Foreach($dom in $domains){
	Foreach($user in $users){  
		$first = $user.Firstname -replace " ",""
		$last = $user.Lastname -replace " ",""
		$sam = get-qaduser -service $($dom):389 -ldapfilter "(&(objectcategory=person)(objectclass=user)(SN=$Last)(GivenName=$First))"  
			foreach ($s in $sam){  
				$outfile += "$first,$Last,$($s.email),$($s.samaccountname)"   
			}
	}  
}  
$outfile | out-file c:\outuser.csv

Open in new window

Author

Commented:
yepp... i just did that :)
all my life i tried to run away from programming...looks like not any more ....

:)

Author

Commented:
the loop does not seem to be working ....
I mean ... i fed it a small list of 4 users ...
The output file has those 4 name repeated 4 times (i gues it as to do with 4 domains we have)
but with no account info...i was expecting it to atleast populate samaccountname.
any fresh thoughts?

Commented:
Try to change this

get-qaduser -service $($dom):389

to

get-qaduser -service "$($dom):389"

Author

Commented:
ok trying this now

Author

Commented:
some luck here...
the test csv file had 1 user name from all 4 domain.
The script output did pupulated their email Id, however, it created 3 extra entries for the names like one for each domain...

Commented:
ok, it will always write then name you will need to put a IF statement in

If ($sam -ne $null){
                        foreach ($s in $sam){    
                                $outfile += "$first,$Last,$($s.email),$($s.samaccountname)"    
                        }
}

Author

Commented:
I am back...
the script was running for almost 5+ hours and at the end i got this error:

[PS] C:\>.\users01.ps1
Get-QADUser : Object reference not set to an instance of an object.
At C:\Users01.ps1:8 char:21
+         $sam = get-qaduser  <<<< -service "$($dom):389" -ldapfilter "(&(objectcategory=person)(o
bjectclass=user)(SN=$Last)(GivenName=$First))"

yet to get full data....any suggestions?

Author

Commented:
now i am being provided with same list of users now with teir samacount names.
i am sure that will make it easier to query on...

please suggest how we can change the approach here with a new script (may be)

thanks!

Author

Commented:
Thanks for your time and patience.
Hats Off!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial