I'm trying to send out and e-mail to users in my department with their name and PIN code but when I send it(which I already did) they get "Hello , blah blah your PIN code is ." I know the SQL is write because I get the first names printed out, then the PIN Codes printed out then I'll get the e-mail addresses. But its just leaving the $users blank and the $PINK blank in the message.
Someone help me please!

Thanks!

$user = invoke-sql -server 'server' -database 'db' -sql "select pc.First_Name from tblEmailaccounts ea inner join tblUsers us on ea.displayname = us.displayname inner join tblPINCodes pc on ea.displayname = pc.displayname where ea.department like '720%' order by us.displayname, ea.displayname, pc.displayname"
$user|Out-File "C:\Scripts\users.xls"
$UserN = (Get-Content "C:\Scripts\users.xls")[ 3..105]
foreach($users in $UserN){
$users1 = $users.trimend()
}

 $PIN = invoke-sql -server 'server' -database 'db' -sql "select pc.PIN from tblEmailaccounts ea inner join tblUsers us on ea.displayname = us.displayname inner join tblPINCodes pc on ea.displayname = pc.displayname where ea.department like '720%' order by us.displayname, ea.displayname, pc.displayname"
$PIN|Out-File "C:\Scripts\Testing.xls"
$PINC = (Get-Content "C:\Scripts\Testing.xls")[ 3..105]
foreach($PINK in $PINC){
$PINKY = $PINK.trimend()
}

$message = "Hello $users1, a new multi-function copier/printer will be placed in your area soon. Your PIN code is $PINKY and it is to use the copier function and make copies. Please do not give it to anyone else or post it somewhere publicly. If you have any questions please call x7600. Thanks"

$ADemail = invoke-sql -server 'server' -database 'db' -sql "select ea.eMail from tblEmailaccounts ea inner join tblUsers us on ea.displayname = us.displayname inner join tblPINCodes pc on ea.displayname = pc.displayname where ea.department like '720%' order by us.displayname, ea.displayname, pc.displayname"
$ADemail |Out-File "C:\Scripts\Email2.xls"
$ADemailos = (Get-Content "C:\Scripts\Email2.xls")[ 3..105]

foreach ($ADemailo in $ADemailos){

Send-SmtpMail -Subject $eSubject -To $eTo -From $eFrom -SmtpHost $eSMTPServer -Body $message;
}

I'm using PrimalScript for this script. I also took off trimend() finally but I have to do the script manually. For each user and PIN code I have to subtract the number and I have to subtract the number fot the email. It just will not work for me other wise. I've tried a for($u=102;$u -gt 2; $u--) but that didn't work either.
Here's my code now and it just gives me the last value, so like I said I have to keep subtracting the number to get a different user.

$eSMTPServer = "ipaddress"
$eFrom = "emailaddress"
$eSubject = "PIN Code"

$user = invoke-sql -server 'Server' -database 'db' -sql "sqlstatement"
$user|Export-Csv "C:\Scripts\users2.csv"
$UserN = (Get-Content "C:\Scripts\users2.csv")[ 2..101]
foreach($users in $UserN){

$message1 = "Hello $users, a new multi-function copier/printer will be placed in your area soon."
}


$PIN = invoke-sql -server 'Server' -database 'db' -sql "sqlstatement"
$PIN|Export-Csv "C:\Scripts\Testing2.csv"
$PINC = (Get-Content "C:\Scripts\Testing2.csv")[ 2..101]
foreach ($PINK in $PINC){

$message2 = "something"
}


$ADemail = invoke-sql -server 'server' -database 'db' -sql "sqlstatement"
$ADemail |Out-File "C:\Scripts\Email2.xls"
$ADemailos = (Get-Content "C:\Scripts\Email2.xls")[ 102 ]
foreach ($ADemailo in $ADemailos){

$ADemailo
$message = $message1+' '+$message2
$message
Send-SmtpMail -Subject $eSubject -To $ADemailo -From $eFrom -SmtpHost $eSMTPServer -Body $message;

}

Thanks for the reply!

Hi

It'll be much easier if you could merge the two sql statements into one (having each user displayName, pincode and email address in one sql query), loop directly on the results (no need to export to files) and the send the emails.

As a side note, when you export to CSV it is much more safer to import the file via import-csv and then pipe it to foreach instaed of using get-content and hard coding the rows you need (e.g. [ 2..101]).

---
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

I actually tried that, but that didn't work either this is what I did.

Add-PSSnapin PSCX
Add-PSSnapin PowerGadgets

$eSMTPServer = "ip address"
$eFrom = "emailaddress"
$eSubject = "PIN Code"

$Path = "C:\Scripts\FinalOut.csv"

$PIN = invoke-sql -server 'server' -database 'db' -sql "select ea.email, pc.First_name, pc.PIN from tblEmailaccounts ea inner join tblUsers us on ea.displayname = us.displayname inner join tblPINCodes pc on ea.displayname = pc.displayname where us.grouping like '515%' order by us.displayname, ea.displayname, pc.displayname"

#$PIN|Export-Csv $Path
$fn = Import-Csv $Path|select First_name
$PINC = Import-Csv $Path|select PIN

$fnm = $fn.trimend()
$PINK = $PINC.trimend()

foreach($PINS in $PIN){
$message = "Hello $fnm, a new multi-function copier/printer will be placed in your area soon. Your PIN code is $PINK and it is to use the copier function and make copies. The PIN code is not necessary for normal printing, only copying, scanning, and secure printing. Please do not give it to anyone else or post it somewhere publicly. If you have any questions please call x7600. Thanks"
$message
}

Write-Host $PINC
Write-Host $fn 

I didn't add the email because I wanted to see what the output would be. The message gives blanks for the first name and the pin code.
???

I forgot to add that the write-host gives values but the message just gives blanks. I cannot give you the csv because it gives names and e-mail addresses.

It basically looks like this:

Email                         First_Name                 PINCode
---------                 -----------------                -------------
xxxx                              xxxx                               xxxx
xxxx                              xxxx                               xxxx