• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2077
  • Last Modified:

powershell script to export mailbox user to a csv file

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.
0
cawasaki
Asked:
cawasaki
  • 17
  • 16
  • 5
  • +1
1 Solution
 
SujitNCommented:
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
0
 
cawasakiAuthor 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....
0
 
cawasakiAuthor Commented:
i forgot, the scirpt must get the user mailbox from specific OU.

thanks
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
cawasakiAuthor Commented:
any help plz?
0
 
GusGallowsCommented:
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

0
 
GusGallowsCommented:
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

0
 
cawasakiAuthor Commented:
hello GUS,

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

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

and whatthis 3bd50098e401463aa228377848493927-1>?????
0
 
Chris DentPowerShell DeveloperCommented:
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
0
 
GusGallowsCommented:
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.
0
 
cawasakiAuthor Commented:
chris, i will test your solution but the mail subject must contain a message with the DATE

thanks
0
 
Chris DentPowerShell DeveloperCommented:
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
0
 
cawasakiAuthor Commented:
Chris, the script must only export user mailbox, not ressource mailbox or distribution list.

can you add a filter???

thanks
0
 
GusGallowsCommented:
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.
0
 
Chris DentPowerShell DeveloperCommented:
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
0
 
GusGallowsCommented:
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

0
 
GusGallowsCommented:
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

0
 
Chris DentPowerShell DeveloperCommented:
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
0
 
GusGallowsCommented:
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. :(
0
 
cawasakiAuthor Commented:
lol Gus you can ask moderator to hide this:)
0
 
GusGallowsCommented:
Thanks Chris. You rock. :)
0
 
cawasakiAuthor Commented:
ok i try the 2 script 15minute :)
0
 
GusGallowsCommented:
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

0
 
cawasakiAuthor 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
0
 
GusGallowsCommented:
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

0
 
Chris DentPowerShell DeveloperCommented:
Here you go :)
$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 -Format 'dd/MM/yyyy')" -Attachments $File -Body "Please see attachment."

# Delete the file
Remove-Item $File

Open in new window

Body is a bit simple above, it can do complex if you wish, and can do HTML if you add -BodyAsHtml to the list of parameters for Send-MailMessage.

Chris
0
 
GusGallowsCommented:
Change the following to address your date format request:
$date = (Get-Date -f ddMMyyyy)
0
 
GusGallowsCommented:
Add the following line to the bottom of the script to remove the file after it sends:

Remove-Item $out
0
 
GusGallowsCommented:
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
0
 
cawasakiAuthor Commented:
GUs i try your other version do not work

i try the last now...
0
 
GusGallowsCommented:
$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.
0
 
cawasakiAuthor 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 ???


0
 
cawasakiAuthor Commented:
and Gus the departement is not exported in your file
0
 
GusGallowsCommented:
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
0
 
GusGallowsCommented:
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

0
 
cawasakiAuthor 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
0
 
cawasakiAuthor Commented:
gus after i test your last version, the sn and givenname is not exeported.

i give the point to Chris.

thanks
0
 
GusGallowsCommented:
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.
0
 
cawasakiAuthor Commented:
Ok :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 16
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now