We help IT Professionals succeed at work.

powershell script to export mailbox user to a csv file

cawasaki
cawasaki asked
on
2,142 Views
Last Modified: 2012-08-13
Hello Exchange Experts,

I need a powershell script to export my user mailbox in a csv file.

I need to export this information:

sn, givenName , display name, department, primary @ mail

the script must ignore and not export ressource mailbox and distribution list.

the script must send the resulted csv file to a specific mail adress.

I apr├ęciate if the name of csv file and the object of email contain the dateof day like export_29-04-1.

thanks for your help.
Comment
Watch Question

Commented:
Get-Mailbox | Select-object GivenName, DisplayName, Department, emailaddresses | export-csv <File name with Location>

Did not try in my Lab, but guess this should work...


-NS

Author

Commented:
hello,

I now it must work,, but i need to export the file and send it by mail...... ignore ressource mailbox and distribution list....

Author

Commented:
i forgot, the scirpt must get the user mailbox from specific OU.

thanks

Author

Commented:
any help plz?
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
This script will do it. You will need to specify an SMTP server and the proper email address of the recipient and an email address to send from. Usually, the sender address can be fictional as long as the domain is in a valid domain.
#function to send email
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$mbx = get-mailbox -resultsize unlimited -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$sn = $mb.sn
	$gn = $mb.givenName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $umb.PrimarySmtpAddress.local
	$psmtpd = $umb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "user@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date"
Send-SMTPmail -to "$sTo" -from "$sFrom" -subject "$sTitle" -body "$sBody" -attachment $out -html

Open in new window

GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Dangit, already found a typo. Use the following instead. I accidentally messed up the PSMTP variable.
#function to send email
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$mbx = get-mailbox -resultsize unlimited -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$sn = $mb.sn
	$gn = $mb.givenName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "user@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date"
Send-SMTPmail -to "$sTo" -from "$sFrom" -subject "$sTitle" -body "$sBody" -attachment $out -html

Open in new window

Author

Commented:
hello GUS,

why  $psmtpl = $mb.PrimarySmtpAddress.local
      $psmtpd = $mb.PrimarySmtpAddress.Domain

?

Author

Commented:
and i need to export this informaiton from specifix OU :)

and whatthis 3bd50098e401463aa228377848493927-1>?????
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
This would be lots easier with PowerShell 2. You can't update to that? http://support.microsoft.com/kb/968930
$File = "C:\export_$(Get-Date -Format 'dd_MM_yyyy').csv"
$OU = "domain.com/somewhere"

Get-Mailbox -OrganizationalUnit $OU -ResultSize Unlimited -RecipientTypeDetails UserMailbox | ForEach-Object {
  $User = Get-User $_.DistinguishedName

  $_ | Select-Object @{n='GivenName';e={ $User.FirstName }}, @{n='sn';e={ $User.LastName }},
    DisplayName, @{n='Department';e={ $User.Department }}, PrimarySmtpAddress
} | Export-Csv $File

# This bit is PowerShell 2
Send-MailMessage -To "you@domain.com" -From "someone@domain.com" -SmtpServer "mail.domain.com" `
  -Subject "User Details" -Attachments $File

Open in new window

Chris
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
One final edit. On the last two lines, change it to:

$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html

The quotes will cause an error if you do not take them out.

Author

Commented:
chris, i will test your solution but the mail subject must contain a message with the DATE

thanks
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
That's easy enough to insert and formatting is no problem, this version just has the default format inserted.
$File = "C:\export_$(Get-Date -Format 'dd_MM_yyyy').csv"
$OU = "domain.com/somewhere"

Get-Mailbox -OrganizationalUnit $OU -ResultSize Unlimited -RecipientTypeDetails UserMailbox | ForEach-Object {
  $User = Get-User $_.DistinguishedName

  $_ | Select-Object @{n='GivenName';e={ $User.FirstName }}, @{n='sn';e={ $User.LastName }},
    DisplayName, @{n='Department';e={ $User.Department }}, PrimarySmtpAddress
} | Export-Csv $File

# This bit is PowerShell 2
Send-MailMessage -To "you@domain.com" -From "someone@domain.com" -SmtpServer "mail.domain.com" `
  -Subject "User Details - $(Get-Date)" -Attachments $File

Open in new window

Chris

Author

Commented:
Chris, the script must only export user mailbox, not ressource mailbox or distribution list.

can you add a filter???

thanks
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
3bd50098e401463aa228377848493927-1 is the standard header for an Alert email. All it will do, is if you give the alert flag, is it will associate the alert icon with the message in sharepoint.

As for these two lines:
$psmtpl = $mb.PrimarySmtpAddress.local
$psmtpd = $mb.PrimarySmtpAddress.Domain
If you only specify $md.PrimarySmtpAddress, it will give you some sort of type mismatch. It is an array of data so you have to grab the elements you want, in this case local and domain, and put them together to form the whole email address.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Get-Mailbox doesn't return distribution lists at all. And the RecipientTypeDetails filter I included should drop Resource Mailboxes, provided they're actually set up as such.

Chris
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
This code will work with Powershell 1.0.

Here is the code again, but this time with specific OU:
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "mail.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\scripts\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address`tDatabase"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$OU = "domain.com/somewhere"
$mbx = get-mailbox -resultsize unlimited -OrganizationUnit $OU -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$sn = $mb.sn
	$gn = $mb.givenName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$db = $mb.database
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp`t$db"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "you@domain.com" #replace with the recipients email address
$sFrom = "you@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html

Open in new window

GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Gah, did not mean to leave in my server info. Not sure how to remove it. Here is the code without my stuff in it.
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\scripts\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$OU = "domain.com/somewhere"
$mbx = get-mailbox -resultsize unlimited -OrganizationUnit $OU -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$sn = $mb.sn
	$gn = $mb.givenName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "sender@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
GusGallows, do you mean to continually include (apparently) real e-mail addressing in your examples? I can remove it if not, always troubles me, spam-bait :)

Chris
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Yeah, I immediately send a request to have it removed. I use this code and was sending him exampled from my code, but forgot to alter it to be generic. :(

Author

Commented:
lol Gus you can ask moderator to hide this:)
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Thanks Chris. You rock. :)

Author

Commented:
ok i try the 2 script 15minute :)
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
I know I said it was the final edit, but I was wrong. :P I just remembered that get-mailobox does not return certain attributes, so I have to correct it so that you get what you are looking for. Here is the rerererevised version:
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\scripts\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$OU = "domain.com/somewhere"
$mbx = get-mailbox -resultsize unlimited -OrganizationUnit $OU -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$usr = get-user $mb
	$sn = $usr.sn
	$gn = $usr.givenName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "sender@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html

Open in new window

Author

Commented:
Chris your scirpt work very fine, just i need this if possible:

1-the date is displayed like this: User Details - 04/29/2011, i need it in this form : 29/04/2011

2-i prefer if the scirpt can delete the export csv file after sent it by meil :)

3- it is possible to add a body test in email?

and thanks

now i will test GUS version
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
I am going to cry now. I think dementia is starting to steal away my brain capacity. I forgot that .sn and .givenName are attributes in AD. You have to use [ADSI] with an LDAP connection to get them with those bits of information. However, get-user has a different name for them. In this case, you would substitute .sn with .LastName and .givenName with .FirstName.

Yes, I know, it's spammy, but this next revision is clean. I fully tested it. Had to dust off old code, but it works now. Sorry for all the spam.
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f MMddyyyy-HHmmss)
$out = "C:\scripts\MailboxResults" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$OU = "domain.com/somewhere"
$mbx = get-mailbox -resultsize unlimited -OrganizationUnit $OU -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$usr = get-user $mb
	$sn = $usr.LastName
	$gn = $usr.FirstName
	$dn = $mb.displayName
	$dept = $mb.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "sender@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html

Open in new window

PowerShell Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Change the following to address your date format request:
$date = (Get-Date -f ddMMyyyy)
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Add the following line to the bottom of the script to remove the file after it sends:

Remove-Item $out
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
If you need an example of sending an email with HTML in the body, check out my article at http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/A_5148-Sending-E-mail-from-a-PowerShell-Script.html

Author

Commented:
GUs i try your other version do not work

i try the last now...
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
$dept = $mb.department
should be
$dept = $usr.department

I swear I don't know what is wrong with me today. I think I should tune out for a while. Check in with you all next week.

Author

Commented:
gus:

Error: OrganizationUnit==>ok i have correct it to -OrganizationalUnit

and the export is in txt file not csv fle

and the date is not correct: User mailboxes for 04292011-213634 ???


Author

Commented:
and Gus the departement is not exported in your file
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
Read on cawasaki. I addressed these (except the Organizationalunit one). Also, just as an FYI, I did this in a tab delmited format, as display names usually ahve commas in them and it confuses some readers. You can still open it in excel. You can also change the name to use .csv instead of .txt even though it is tab delmited. It will open fine in Excel.

To reiterate:
Change the following to address your date format request:
$date = (Get-Date -f ddMMyyyy)

Add the following to the end of the script to remove the file once it is sent
Remove-Item $out

and change $dept to the following:
$dept = $usr.department
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
So the script should now look as follows:
function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) 
{
    #replace smtpserver.domain.com with the fqdn of your smtp server
    if ($smtpserver -eq $null) {$smtpserver = "smtpserver.domain.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) 
    {
       	$attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

#create output file
$date = (Get-Date -f ddMMyyyy)
$out = "C:\scripts\MailboxResults_" + $date + ".txt"
$inp = "sn`tgivenName`tdisplayName`tdepartment`tPrimary SMTP Address"
out-file $out -inputobject $inp

#gather a list of all mailboxes that are only type usermailbox
$OU = "domain.com/somewhere"
$mbx = get-mailbox -resultsize unlimited -OrganizationalUnit $OU -filter { ((((IsResource -eq $false) -and (IsShared -eq $false)) -and (IsLinked -eq $false)) -and (RecipientTypeDetails -eq 'UserMailbox')) }
foreach ($mb in $mbx)
{
	$usr = get-user $mb
	$sn = $usr.sn
	$gn = $usr.givenName
	$dn = $mb.displayName
	$dept = $usr.department
	$psmtpl = $mb.PrimarySmtpAddress.local
	$psmtpd = $mb.PrimarySmtpAddress.Domain
	$psmtp = $psmtpl + "@" + $psmtpd
	$inp = "$sn`t$gn`t$dn`t$dept`t$psmtp"
	#Write results to output file
	out-file $out -inputobject $inp -Append
}
#Send file as attachment via email
$sTo = "user@domain.com" #replace with the recipients email address
$sFrom = "sender@domain.com" #replace with an email address with a valid domain.
$sTitle = "User mailboxes for " + $date
$sBody = "Attached is the list of User mailboxes as of " + $date
Send-SMTPmail -to $sTo -from $sFrom -subject $sTitle -body $sBody -attachment $out -html 
Remove-Item $out

Open in new window

Author

Commented:
gus, ok i will test it tomorow if it work i will accept the 2 script solutions, if no, i will accept only Chris solution.

Thanks To Everybody

Author

Commented:
gus after i test your last version, the sn and givenname is not exeported.

i give the point to Chris.

thanks
GusGallowsSupport Escalation Engineer
CERTIFIED EXPERT

Commented:
It's fine. I copied the old script back in on accident. Instead of $usr.sn and $usr.givenName, it should have been $usr.LastName and $usr.FirstName.

Was a bad day for me. Sorry about all the confusion.

Author

Commented:
Ok :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.