The Admin Rules

We are trying to get our users mailbox sizes down below 200 MB in anticipation of moving the mailboxes to a new email environment hosted by our parent company. The company I work for currently has over 1500 mailboxes with no current storage limits so the task is going to be somewhat daunting to say the least. I have been given the directive to come up with an automated way of sending each user their mailbox statistics via a personalized email on a weekly basis until the move. I decided to use the exchange powershell and Excel 2007 to accomplish this task and below are the two scripts I used to accomplish it.

First, the Exchange 2007 Powershell script:

#create the output file
#$file = new-item -itemtype file mbstats.csv -force

#create object of mailboxes
$colMailboxes = get-mailbox -resultsize unlimited

$strHeaders = “Display Name, Primary SMTP Address, Database, Department, Total Items, Mailbox Size (MB)”
write-output $strHeaders

foreach ($objMailbox in $colMailboxes) {

$mailboxStats = get-mailboxstatistics -identity $objMailbox.alias
if(!$?){
$foreach.movenext()}
else{
$strOutput = $objMailbox.DisplayName + “,” + $objMailbox.PrimarySMTPAddress + “,” + $mailboxStats.DatabaseName + “,” + $objMailbox.office + “,” + $mailboxStats.ItemCount + “,” + $mailboxStats.TotalItemSize.value.toMB()
write-output $strOutput}
}

The above script will export the mailbox stats and put them into a csv file.

Copy and Paste the above code into notepad and save the file with a ps1 extension.

Open the exchange powershell and run the script by typing the name of the ps1 file with | out-file “C:\nameofcsvfile.csv” appended to the end.

For example:

mbstats.ps1 | out-file c:\mbstats.csv

Once the csv file is created, open the csv file with excel, format the file the way you want, and save the file as an excel spreadsheet. (or..write a Macro that does this for you)

Next, the VBA code to loop the file and email people over the 200 MB limit:

Sub SendEmails()

Dim mlNewMessage As MailItem
Dim myOlApp
Dim strBody As String
Dim strFName As String
Dim strItems As String
Dim strMBSize As String

‘Loop through the spreadsheet until the end
Do Until Range(”A2″).Offset(a, 0) = “”

If (Range(”A2″).Offset(a, 4).Value >= 200) Then ‘Checks for mailbox size

strDear = Range(”A2″).Offset(a, 0).Value ‘Start Point
strItems = Range(”A2″).Offset(a, 3).Value ‘Pulls MB Items
strMBSize = Range(”A2″).Offset(a, 4).Value & ” MB” ‘Pulls MB Size

‘Builds Email Body
strBody = “You are being notified because your email storage is currently ” & strMBSize _
& ” which exceeds the new capacity of 200 MB set forth by CompanyName.” & vbCrLf & vbCrLf _
& “Your total mailbox size must be below 200 MB and it is currently above this number.” & vbCrLf & vbCrLf _
& “Please contact the technical support at YourNumber or YourEmailAddress” _
& “if you need help cleaning out or archiving your items.” & vbCrLf & vbCrLf _
& “Thank you,” & vbCrLf & vbCrLf _
& “Your Name” & vbCrLf _ ‘Signature
& “Your Number” & vbCrLf _ ‘Signature
& “Your Email Address” ‘ Signature

strDear = Left(strDear, InStr(1, strDear, ” “, vbTextCompare)) ‘Pulls First Name from Full Name

Set myOlApp = CreateObject(”Outlook.Application”) ‘Create New Outlook Item
Set mlNewMessage = myOlApp.CreateItem(olMailItem) ‘Create New Mail Item

‘Defines Email Body
mlNewMessage.Body = strDear & “,” & vbCrLf & vbCrLf _
& strBody

mlNewMessage.Subject = “Harry Norman mailbox over the size limit” ‘Defines Subject
mlNewMessage.To = Range(”A2″).Offset(a, 1) ‘Defines To

mlNewMessage.Send ‘Sends Email Message

Set myOlApp = Nothing ‘Clears mlOlApp Variable
Set mlNewMessage = Nothing ‘Clears mlOlApp Variable

a = a + 1 ‘Moves to Next Record

Else
a = a + 1 ‘Next Record
End If

Loop

End Sub