You can take advantage of Powershell capabilities, along with the PowerTools for OpenXml cmdlets in order to send bulk mail containing Open Xml Documents to a group of people. The following sample shows how to use the Export-OpenXmlSpreadsheet Open Xml Power Tools cmdlet and the .NET send email built-in capability to send either periodical or ad-hoc (alerts, maybe!) server statistics to a group of users by email.

Given the object-oriented nature of PowerShell and the ease of connection against .Net libraries, you can work with the prebuilt email classes that come along with the .Net framework in order to construct an email message (in this sample including a spreadsheet containing system information created by using the Export-OpenXmlSpreadsheet Open Xml Power Tools cmdlet) and send it to a list of addresses. Here is the script code:

# Passes an email address array to the script as parameter

param(

      [string[]] $Emails = $(throw "Specify email addresses")

)

# Sets the path to temporarily store the Spreadsheet

$CPUInformationPath = $env:temp+"\CPU.xlsx"

# Creates the Spreadsheet

Get-Process | Sort-Object -Property CPU -Descending | Select-Object -Property Name, CPU -First 10 | Export-OpenXmlSpreadsheet -OutputPath $CPUInformationPath -Chart -ChartType Column -ColumnsToChart Name,CPU -HeaderColumn Name

# Configures the mail client

$SmtpClient = New-Object System.Net.Mail.SmtpClient

$SmtpClient.Host = "mail.staffdotnet.com"

# Creates and configures the mail message

$msg = New-Object System.Net.Mail.MailMessage

$msg.From = "notifications@staffdotnet.com"

$msg.Subject = "CPU report"

$msg.Body = (@"

CPU report

For $(Get-Content env:COMPUTERNAME) at $((Get-Date).ToString("yyyy-MM-dd hh:mm:ss tt"))

"@)

# Attaches the Spreadsheet to the mail message

$attachment = New-Object System.Net.Mail.Attachment($CPUInformationPath)

$msg.Attachments.Add($attachment)

# Sets the email addresses

foreach($email in $Emails)

{

       $msg.To.Add($email)

}

# Sends the email

$SMTPClient.Send($msg)

And… that’s it! A very simple script which allows you to send CPU usage statistics to a group of users!

You can call this script with email addresses directly at the command line,

> .\sendReport.ps1 'address1@staffdotnet.com','address2@staffdotnet.com'

or you can feed it with a list from a text document

> .\sendReport.ps1 @(Get-Content emailAddresses.txt)

Get your PowerTools for Open XML binaries HERE!

You will need:

1   OpenXml SDK installer

1   Windows PowerShell installer

1   PowerTools for OpenXml binaries package

Your computer must have the .Net Framework 3.5 previously installed.

First, install both OpenXml SDK and Windows PowerShell packages.

Then unzip the PowerTools for OpenXml binaries package into a location you like (we strongly recomend unzipping to the Program Files folder).

Next, double-click on the register.bat file at the unzipped folder and press any key when asked.

Now you can test the installation by opening Windows PowerShell and typing this:

Get-PSSnapin -registered

the PowerTools entry should be displayed there.

Last, enable the package by typing

Add-PSSnapin OpenXml.PowerTools

Serve and enjoy!

Now you can play around with the provided cmdlets. Don't forget you can get the list of cmdlets published by typing

Get-Command -PSSnapin OpenXml.PowerTools

Getting more from OpenXml Power Tools: plain easy!

Part 2 - A little deeper

Creating workflows

By using the pipelining data exchange of PowerShell you can create cmdlet workflows. With a workflow you can perform a sequence of operations on documents, all within a single PowerShell command line.

Check this example out:

Set-OpenXmlWatermark -Path file.docx -WatermarkText "Confidential" -DiagonalOrientation | Add-OpenXmlPicture -PicturePath pic.jpg -InsertionPoint "//w:r[0]" | Add-OpenXmlDigitalSignature -Certificate personal.pfx

Almost all of the OpenXml Power Tools return a document as output. Character '|' (pipe) takes the output of a cmdlet and feeds the next one with that output, so notice that only the first cmdlet call (Set-OpenXmlWatermark) receives a file name. After Set-OpenXmlWatermark returns its output, it is passed to the next one (Add-OpenXmlPicture) to apply a new operation against the returned document.

That way you can chain processes against documents to perform richer and more useful operations

Creating Scripts

You can also take advantage of PowerShell object-oriented nature to create scripts that handle files in a more flexible way.

Check this script fragment out:

$doc = Get-OpenXmlDocument file.docx

$doc = Set-OpenXmlWatermark -Documents $doc -WatermarkText "Confidential" -DiagonalOrientation

$doc.Pictures.Insert("pic.jpg","//w:r[0]")

$doc.DigitalSignatures.Insert("personal.pfx")

$doc.Close()

This example is equivalent to the code from the last topic. There are some minor considerations to be aware of:

• Get-OpenXmlDocument allows you to load a document to memory for later usage
• You can still call cmdlets in the usual way by using the -Documents parameter with the object references
• OpenXml documents have some properties and methods which can be used directly from a script or PowerShell environment
• Explicit closure of documents is needed in order to release document locks and commit changes

Using OpenXml Power Tools: plain easy!

Part 1 - The Basics

Which cmdlets are available?

Execute this:

Get-Command -PSSnapin OpenXml.PowerTools

or type

*-OpenXml

and then hit the [tab] key repeatedly to cycle through all OpenXml Power Tools cmdlets available

How do I use each?

If you have no idea what a specific cmdlet does, read the help for a given cmdlet by calling

Get-Help [cmdlet]

to get basic help, or execute

Get-Help [cmdlet] -Full

to get more detailed information about inputs, outputs, parameters and examples.

Also, you can get some guidance about parameters for a cmdlet by calling the cmdlet without parameters. This allows you to specify mandatory parameters one by one and, optionally, get contextual help for each parameter. When calling a cmdlet with no parameters specified you will be requested to enter each parameter in different lines; at this point you can type !?[enter] to get a brief description of the expected parameter

> Export-OpenXmlWordprocessing

Supply values for the following parameters:

(Type !? for Help.)

Text: !?

Text to insert in the new Wordprocessing document

Text: some testing text

...

(PowerShell messages are shown in gray color)

Calling single cmdlets

The easiest scenario in which cmdlets are used is when performing a single operation on a document (creation, modification, query). This usage method is as simple as calling the cmdlet name and its parameters, like this:

Set-OpenXmlBackground -Path file.docx -BackgroundImage pic.jpg -SuppressBackups

Notice that some parameters must be specified as -[parameter name] [parameter value], but there is also switch parameters, which don't require a value. In the example above you can infer what is the expected result: file.docx will have the image pic.jpg as background. As this operation alters the document, a backup is automatically generated unless you use the switch parameter -SuppressBackups

If you don't know what are expected paramenters for a cmdlet, you can type -[tab] to get the next available parameter

Functional programming concepts together with modern object oriented paradigm are the latest fashion today, due to the power of mixing both formerly thought completely different worlds.

The current approach has been bringing minor ideas from functional paradigm (lambda expressions, Linq, inmutable elements...) to a model based on object oriented paradigm... until now!

So, WTF (want to findoutwhat) is F#???

> (R/ yeah, stop talking and show me the code!)

Ok, check this out:

let rec factor n index top =
    if (index <= top) then
        if (index*top = n) then
             if (index = top) then
                 index :: factor n (index + 1) (top - 1)
             else
                index :: factor n (index + 1) (top - 1) @ [top]
        else
             if (index * top > n) then
                factor n index (top - 1)
            else
                factor n (index + 1) top
     else
         []

let factors n = factor n 1 n;;

let result = (read_int () |> factors)
do print_any result

Maybe you already realized what is happening here; it’s quite simple:

• Define (let) a recursive (rec) function named factor, with parameters n, index, and top
• There is a stop condition when both index and top reach a central value. When this point is reached the program will not add any factor, but an empty list [] instead.
• If current values are factors of the number, they are added to the factor list. List construct operator :: allows to prepend values to a list; @ operator allows to concatenate lists.
• User input is retrieved by using library functions as read_int, whose results will be passed by pipe to the factors function.
• List output is performed by using the print_any library function

Clearly this is not the most efficient algorithm ever created, but certainly works and shows some usual techniques used in functional programming languages (list constructions, recursion, auxiliary functions, …)