Exchange Mailbox Stats

### Send mailbox statistics script
Add-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.E2010

$FromAddress = "exchange@test.local"
$ToAddress = "test@test.local"
$MessageSubject = "Mailbox Size Report"
$MessageBody = "Attached is the current list of mailbox sizes."
$SendingServer = "mail.test.local"
$script:resultFile = "${env:TEMP}\ExchangeStats.xlsx"

$servers = Get-MailboxServer

$erroractionpreference = "SilentlyContinue"

$objExcel = New-Object -ComObject Excel.Application
#$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false

$objBook = $objExcel.Workbooks.Add()

ForEach ($server in $servers) {
    $objSheet = $objBook.Worksheets.Add()
    $objSheet = $objBook.Worksheets.Item(1)
    $objSheet.Name = $server.Name

    ### get the stats
    $stats = Get-MailboxStatistics -Server $server |
    Sort-Object -Property TotalItemSize -Descending |
    Select-Object -First 50 -Property DisplayName,ItemCount,@{label="TotalItemSize";expression={$_.TotalItemSize.Value.ToMB()}},DeletedItemCount,@{l="TotalDeletedItemSize";e={$_.TotalDeletedItemSize.value.toMB()}},StorageLimitStatus,Database

    $row = 1

    $objSheet.Cells.Item($row,1) = "DisplayName"
    $objSheet.Cells.Item($row,2) = "ItemCount"
    $objSheet.Cells.Item($row,3) = "TotalItemSize(MB)"
    $objSheet.Cells.Item($row,4) = "DeletedItemCount"
    $objSheet.Cells.Item($row,5) = "TotalDeletedItemSize(MB)"
    $objSheet.Cells.Item($row,6) = "StorageLimitStatus"
    $objSheet.Cells.Item($row,7) = "Database"

    $col = 0
    ForEach ($result in $stats) {
        $row++
        Write-Host "$server, $row="
        $objSheet.Cells.Item($row,1) = $result.DisplayName
        $objSheet.Cells.Item($row,2) = $result.ItemCount
        $objSheet.Cells.Item($row,3) = $result.TotalItemSize
        $objSheet.Cells.Item($row,4) = $result.DeletedItemCount
        $objSheet.Cells.Item($row,5) = $result.TotalDeletedItemSize
        $objSheet.Cells.Item($row,6) = $result.StorageLimitStatus
        $objSheet.Cells.Item($row,7) = $result.Database
    }
    $objSheet.UsedRange.EntireColumn.AutoFit()
}

$objBook.SaveAs($resultFile)
$objBook.Close()
$objExcel.Quit()

### Create the mail message and add the statistics text file as an attachment
$SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress, $MessageSubject, $MessageBody
$Attachment = New-Object Net.Mail.Attachment($resultFile)
$SMTPMessage.Attachments.Add($Attachment)

### Send the message
$SMTPClient = New-Object System.Net.Mail.SMTPClient $SendingServer
$SMTPClient.Send($SMTPMessage)

### Release file resource
$Attachment.Dispose()

### Delete the resultFile
Remove-Item $resultFile

### Remove the Exchange snap-in from the current session
Remove-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.E2010