Hey, Scripting Guy! The Pointy Headed Boss (PHB) just got back from some kind of stupid management summit, and he has decided he needs to track our work activities more closely. As a result, we now have to give him a weekly progress report. Seems to me that, if he would pay attention, he would know what we do, but then that would require effort on his part.

Here is the problem, I send hundreds of e-mail messages a week to customers. In fact, this is one of my primary duties. The PHB has no idea of the magnitude of my weekly correspondence, and so it is imperative that I correctly report this number to him. Last week I spent 30 minutes in my Sent Items folder trying to count all those messages. The problem is that, unlike Windows Explorer, I cannot simply select the messages from this week and have Office Outlook tell me how many there are. So I am stuck scrolling up and down, and hoping I do not lose my place (which I do every time the phone rings or the PHB comes by to show he cares). I looked around, but did not see an example of what I need on the Script Center. Can you help me? I am a customer service rep, not a computer guru. Also, if it is not too much trouble, could you ask the Scripting Wife if she would be willing to share her chili recipe?
– PD

Hi PD,

That is one of the good things about working for Microsoft. I have never seen a PHB here. I think we have excellent management, but because I have not always worked for Microsoft, I have had experience with both the nefarious and the infamous types of PHB, so I can empathize.

To find out how many e-mail messages you sent in a week, you can use the CountSentEmailForWeek.ps1 script. Be sure you have Windows PowerShell installed and configured to allow scripts to run. Here is a link that talks about running Windows PowerShell scripts.

CountSentEmailForWeek.ps1

$dte = Get-date
$thisWeek = $dte.AddDays(-7)
$i = $j = $null
[Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Outlook") |
out-null
$olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace("MAPI")
$folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)
$items = $folder.items
$items |
ForEach-Object {
 Write-Progress -activity "Inspecting $($items.count) email messages"  -Status "Processing ..." `
-PercentComplete ($i/$items.count*100)
 if($_.ReceivedTime -ge $thisWeek) { $j++}
$i++
}
"Total email sent this week is $j"

The first thing we need to do is to get the current date. To do this, we can use the Get-Date cmdlet. This is the same as the VBScript Date function, except that the Get-Date cmdlet returns a System.DateTime object instead of merely a string. We store the DateTime object in the $dte variable:

$dte = Get-date

Because we have a DateTime object, it means that there are methods and properties associated with it. We can use the Get-Member cmdlet to explore this information. In this command, we limit the data returned to only methods:

PS C:\> $dte = Get-Date
PS C:\> $dte | Get-Member –membertype method
   TypeName: System.DateTime
Name                 MemberType     Definition
----                 ----------     ----------
Add                  Method         System.DateTime Add(TimeSpan value)
AddDays              Method         System.DateTime AddDays(Double value)
AddHours             Method         System.DateTime AddHours(Double value)
AddMilliseconds      Method         System.DateTime AddMilliseconds(Double value)
AddMinutes           Method         System.DateTime AddMinutes(Double value)
AddMonths            Method         System.DateTime AddMonths(Int32 months)
AddSeconds           Method         System.DateTime AddSeconds(Double value)
AddTicks             Method         System.DateTime AddTicks(Int64 value)
AddYears             Method         System.DateTime AddYears(Int32 value)
CompareTo            Method         System.Int32 CompareTo(Object value), System.Int32 CompareTo(DateTime value)
Equals               Method         System.Boolean Equals(Object value), System.Boolean Equals(DateTime value)
GetDateTimeFormats   Method         System.String[] GetDateTimeFormats(), System.String[] GetDateTimeFormats(IFormat...
GetHashCode          Method         System.Int32 GetHashCode()
GetType              Method         System.Type GetType()
GetTypeCode          Method         System.TypeCode GetTypeCode()
get_Date             Method         System.DateTime get_Date()
get_Day              Method         System.Int32 get_Day()
get_DayOfWeek        Method         System.DayOfWeek get_DayOfWeek()
get_DayOfYear        Method         System.Int32 get_DayOfYear()
get_Hour             Method         System.Int32 get_Hour()
get_Kind             Method         System.DateTimeKind get_Kind()
get_Millisecond      Method         System.Int32 get_Millisecond()
get_Minute           Method         System.Int32 get_Minute()
get_Month            Method         System.Int32 get_Month()
get_Second           Method         System.Int32 get_Second()
get_Ticks            Method         System.Int64 get_Ticks()
get_TimeOfDay        Method         System.TimeSpan get_TimeOfDay()
get_Year             Method         System.Int32 get_Year()
IsDaylightSavingTime Method         System.Boolean IsDaylightSavingTime()
Subtract             Method         System.TimeSpan Subtract(DateTime value), System.DateTime Subtract(TimeSpan value)
ToBinary             Method         System.Int64 ToBinary()
ToFileTime           Method         System.Int64 ToFileTime()
ToFileTimeUtc        Method         System.Int64 ToFileTimeUtc()
ToLocalTime          Method         System.DateTime ToLocalTime()
ToLongDateString     Method         System.String ToLongDateString()
ToLongTimeString     Method         System.String ToLongTimeString()
ToOADate             Method         System.Double ToOADate()
ToShortDateString    Method         System.String ToShortDateString()
ToShortTimeString    Method         System.String ToShortTimeString()
ToString             Method         System.String ToString(), System.String ToString(String format), System.String T...
ToUniversalTime      Method         System.DateTime ToUniversalTime()

After we have the DateTime object stored in the $dte variable, we can subtract seven days from it. This will represent a date that is a week from the current date. We use the AddDays method to add a negative seven days to the date. This is how we subtract dates and is shown here:

$thisWeek = $dte.AddDays(-7)

Now we need to initialize a couple of counter variables. We use a shortcut method that declares the variables and sets their value to null:

$i = $j = $null

After initializing the variables, we need to load the Microsoft.Office.Interop.Outlook interop assembly. This was discussed in a fair amount of detail yesterday:

[Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Outlook") |
out-null

When we have loaded the assembly, we can create the enumeration. This was also discussed fairly well yesterday, and is shown here:

$olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]

Then we need to create an instance of the Outlook.Application object. The Outlook.Aplication object is the main object that allows us to work with Office Outlook. To create it, we use the New-Object cmdlet and use the comobject parameter. We store the returned object in the $outlook variable as seen here:

$outlook = new-object -comobject outlook.application

After we have created an instance of the Outlook.Application object, we can use the GetNameSpace method. There is only one value allowed for the GetNameSpace method—MAPI. This line of code will always be the same when working with Office Outlook folders:

$namespace = $outlook.GetNameSpace("MAPI")

Then we need to connect to one of the default Office Outlook folders. To connect to a particular folder, we use the GetDefaultFolder method from the NameSpace object. We give it the OlDefaultFolders enumeration of olFoldersSentMail to refer to the Sent items folder. This is seen here:

$folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)

We now want to obtain a collection of items from the Sent e-mail folder. We obtain this collection of e-mail items by querying the items property. This is not a method, even though the word item is often used as a method. (Of course, the word is items and not item, but, hey, you never know. The good thing about Windows PowerShell is we always know if we are using a method or a property, because all method calls use a set of parentheses, and properties do not.) This is seen here:

$items = $folder.items

When we have the collection of items from the Sent e-mail folder, we pipeline the collection to the ForEach-Object cmdlet. We could have stored the collection into a variable, the way we would have needed to do with VBScript. But that would have been much less efficient.

$items | 

When we get to the other side of the pipeline character “|”, we send the $items to the ForEach-Object cmdlet. This cmdlet acts in a similar fashion as the ForEach statement. The big difference is we can use it in the middle of a pipeline to work with individual items as they come across the pipeline:

Foreach-Object { 

You can use the Write-Progress cmdlet to provide progress feedback. The Write-Progress cmdlet will display a progress bar such as the one seen in the image just below. Use of this cmdlet is a best practice when providing feedback to the user. This is because it automatically tracks the progress across the screen and allows the user to estimate if they have time to go make a pot of Darjeeling tea, which is what I did while running this script (yes, Jit, I still drink Darjeeling).

There are three parameters you need to specify when using the Write-Progress cmdlet. The first is the activity you are doing. This shows up on the first line. The next is the status message, which is used to let the user know what you are currently working on. The last is the percentcomplete parameter. There are other ways of using this cmdlet that are detailed in the online Help, but I find myself using these three parameters at least 99 percent of the time:

 Write-Progress -activity "Inspecting $($items.count) email messages"  -Status "Processing ..." `
-PercentComplete ($i/$items.count*100)

Next we need to examine each piece of e-mail in the collection. We are only interested in an e-mail message if it was sent within the past week. We use one of our counter variables that we earlier initialized to keep track of the number of e-mail messages we sent this week. Because we are receiving the e-mail via the pipeline, we need to use the $_ automatic variable to inspect each e-mail message as it comes along the pipeline. The –ge is the greater than or equal operator. To increment the $j counter variable, we use the ++ operator, which means to add one to the value of the $j variable. This is the same as the a = a+1 syntax of VBScript fame. The $j variable keeps track of all the e-mail messages we found that match our filter. The $i variable tracks all the e-mail messages that are processed and is used by the Write-Progress cmdlet. This section of the code is seen here:

 if($_.ReceivedTime -ge $thisWeek) { $j++}
$i++
}

Now we are ready to display the total number of messages. We use a simple expanding string as seen here. The cool thing about expanding strings (double quotation marks) is that the value of the $j variable is expanded and allows us to avoid concatenation:

"Total email sent this week is $j"

The output from the script is seen here:

Well, PD, that is all for the CountSentEmailForWeek.ps1 script. If it saves you even 30 minutes a week, that is 26 hours a year. Make sure you include this script in your PHB report. If you use an hourly rate of $50.00 USD per hour, the script saves $1,300.00 USD per year. PHBs love that bottom line stuff, as well as return on investment (ROI) using scripts.

I asked the Scripting Wife about her chili recipe. She said she just throws whatever is left in the refrigerator into the pot. Today’s chili had mushrooms, peppers, roast beef, navy beans, pinto beans, tomatoes, and various spices. I’m salivating. See you tomorrow as Office Outlook Week continues.

Ed Wilson and Craig Liebendorfer, Scripting Guys