PowerShell and Outlook – Part 2

In Part 1 we covered some basic Outlook terminology and managed to connect to our Inbox and the Items collection it contains. Unfortunately we couldn’t get the first message in the Inbox by the methods we would expect because the Items collection is not stored in a date order (or in fact any particular order).

We can in fact sort the Items by a large number of different headings, including a variety of dates. The one we’re looking for if we want the most recently arrived message is ‘ReceivedTime’ (as before see MSDN for all of the headings). So we should be able to sort by executing:

$InBox.Items.Sort("[ReceivedTime]",$true)

Once again $InBox.Items[1] doesn’t give us the most recent item. It turns out that you need to create a variable that points to the Items collection and sort that; executing the Sort method directly on the collection doesn’t give an error, it just doesn’t work. So we should try:

$Items = $Inbox.Items
$Items.Sort("[ReceivedTime]",$true)

Now $Items[1] gives us the most recent message. Whew!

Now we have our message, what can we do with it? A non-exhaustive list includes:

Access properties (sender, subject, size, message body (various formats))

Print the message

$Items[1].PrintOut()

Save the message

$Items[1].SaveAs($path,$type)

Delete the message

$Items[1].Delete()

and to complete the circle… Display the message in a standard Outlook message window.

$Items[1].Display()

Hopefully that’s enough to get you started. Bear in mind that the Items collection we’ve established contains objects which can be sent down the PowerShell pipeline like any other.

In Part 3 we’ll try to create and send a message.

Advertisements
Posted in PowerShell | Leave a comment

PowerShell and Outlook – Part 1

This is the first of (another) series of articles covering PowerShell alongside another technology. In this case it is Microsoft Outlook.

I’ve scripted Outlook using a variety of technologies over the years,  so it only seems right to add PowerShell to the toolbox. Most of the techniques I’ve used in the past have analogues in the PowerShell world.

Outlook Terminology

Outlook is a complex beast, combining Email, Calendaring and Tasks into one application. It can connect to a variety of email sources and has its own set of terminology. See MSDN for full details, but here are some highlights:

Application – This is Outlook itself

Namespace – A way of connecting to email sources. The only supported namespace is MAPI .

Folder – An object containing other objects

Item – Outlook doesn’t have separate objects for email, calendar items, tasks etc. All of these are just Items.

Getting Started

Automating Outlook via PowerShell uses COM objects. First we need to add the support libraries to our session:

Add-Type -AssemblyName "Microsoft.Office.Interop.Outlook"

Next we create an instance of Outlook to automate:

$outlook = New-Object -ComObject Outlook.Application

Now we need a reference to the MAPI namespace:

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

From here we have access to everything in the current Outlook profile.

At this point I feel I ought to make a point about performance. Outlook can be connected to a number of different email sources including IMAP, POP3 and various flavours of Microsoft Exchange. In some instances your Outlook settings and how Outlook interacts with your mail source may result in you seeing fewer messages, calendar items etc. than you were expecting. If my experience is anything to go by check your Outlook settings before you decide that something is wrong with you code.

So, having said all that, what can we do with Outlook now that we are connected to the MAPI namespace? Well let’s have a look at our unread messages.

Let’s connect to our InBox. It could be referred to by many names depending on language settings etc. so Microsoft have made it possible to refer to it via an enumeration:

Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox

So we can get a reference to our InBox by using:

$inbox = $namespace.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

To find the number of unread message we access the UnReadItemCount property.

$inbox.UnReadItemCount

Because Outlook presents us with a proper COM object, we can use Get-Member to view the Properties and Methods of the InBox. There are some obvious and not so obvious entries in the list. The key one for us at the moment is the Items property, which unsurprisingly gives us access to the Items in the InBox. Let’s look at the first message in the InBox:

$inbox.Items[1]

Oops, that didn’t work. How about (assuming you have 100 messages):

$inbox.Items[100]

Nope. It seems that Items are not automatically stored in a sorted way. A little research on MSDN turns up the following statement:

The index for the Items collection starts at 1, and the items in the Items collection object are not guaranteed to be in any particular order.

Ok – so that gives us something to look at for part 2!

Posted in PowerShell, Uncategorized | Leave a comment

Back Again…

There has been some domestic reorganisation going on at the Labs recently that has prevented me from posting articles. Hopefully we can now get back to a more regular schedule. Next up, the wonderful world of automating Microsoft Outlook.

 

Posted in Uncategorized | Leave a comment

Today with PowerShell – IoT Edition

Today I spent some time setting up a Raspberry Pi 3. I decided to ditch the more usual Linux based OSes and go for Windows 10 IoT Core. It took a little longer to get a working SD card than I’d hoped, but this was the end result:

PS C:\Users\User> Enter-PSSession -ComputerName pi-win-01 -Credential (Get-Credential)
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
[pi-win-01]: PS C:\Data\Users\Administrator\Documents>

 

There has been a lot of focus on PowerShell on Linux recently, so here’s another platform to consider: Windows IoT on ARM. Given the pared-down nature of the OS there’s a reduced set of cmdlets and modules available, but most of those you’d expect are there. Crucially remoting works.

 

Posted in PowerShell | Leave a comment

MMC, CSV and PowerShell

This is just a quick tip. Many GUI administrative tools use the Microsoft Management Console framework. If you want to use data from one of these tools with PowerShell there’s an easy way via our old friend CSV. With a container-type object selected in the left hand window you can right-click and choose Export List. This will allow you to save the detail in the right hand window as a comma (or tab) separated text file that you can access using Import-CSV. Unfortunately this doesn’t appear to be available with every MMC based tool, but many that I come across have this useful facility.

 

Posted in PowerShell | Leave a comment

Building GUIs Using WPF and PowerShell – Part 1

PowerShell is first and foremost a Shell – a command line environment. The latest Insider builds of Windows 10 now have it as the default Shell. Those of us who have been along for the ride for the last 10 years had maybe hoped this would happen sooner, but it’s definitely coming. PowerShell was conceived as an answer to the over-use of GUIs in system management.

Having said that, there are many situations where GUIs are useful. PowerShell caters to those situations through its deep relationship with the .NET Framework. As usual with PowerShell there’s more than one way to do it.

Many scripting languages like Perl and Python offer access to GUI elements. You build a Form object, assign various controls, all in code. It can be laborious to do anything substantial. PowerShell offers that same approach using the Windows Forms class library and if you’re familiar with GTK+ and other similar technologies this could be the way for you.

There is another way. The Windows Presentation Foundation class library (henceforth WPF) is also available in the .NET Framework. WPF uses XAML (Extensible Application Mark up Language) to describe graphical elements. You can use visual design tools to create your GUI and then import it into your PowerShell solution. Microsoft Visual Studio products (including commercial, Community and Express versions, but not VS Code) include visual XAML editors that allow drag and drop design.

For the purposes of this series I will step through the process of designing a GUI for a PowerShell script that gathers some system information. Disclaimer: this script will concentrate on the GUI elements at the expense of functionality and error checking.

The PowerShell script:

$System = Get-WMIObject -Class Win32_ComputerSystem

Obviously we can output any of the $System object’s properties to the console, but for the purposes of the article we’ll build a WPF GUI to display them (we could also use Out-GridView).

I’m going to use Visual Studio Community 2015 to do the visual design (other design tools are available, including KAXAML http://kaxaml.com/).

Once I’ve created a new WPF project I’m presented with a blank WPF form to work with. I have a graphical view in the top half of the window and a textual view of the underlying XAML below that. Changes made in either window are reflected in the other. I drag a TextBox control onto the form and use the graphical window to size and position it, then set a fixed-width font. Once I’m happy with the layout I copy the XAML code onto the clipboard and switch to my PowerShell script. I create a Here String and paste the XAML into it like so:

[xm]$Xaml=@" 
<Window x:Class="SimpleWindow.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:SimpleWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="150" Width="350">
    <Grid>
        <TextBox x:Name="TextBox" FontFamily="Consolas" />
    </Grid>
</Window>
"@

To make everything work correctly I need to remove the text

        x:Class="SimpleWindow.MainWindow"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:SimpleWindow"
        mc:Ignorable="d"

from the XAML, along with the

x:

from the Name attribute of the TextBox.

At the top of my script I add a reference to the WPF class library:


Add-Type -AssemblyName PresentationFramework

After the Here String I add code to load the XAML:


$reader = New-Object System.Xml.XmlNodeReader($Xaml)
$Window = [Windows.Markup.XamlReader]::Load($reader)

Now I have a XAML GUI nearly ready for use. In order to put text into the text box I need a reference to the TextBox object. I can get this using the FindName method of our $Window object:


$TextBox = $Window.FindName("TextBox")

Now I can take whatever properties I want and send them to the text box, then show the $Window object on the screen.

$TextBox.Text = $System | Select-Object Name, Model, Manufacturer | Out-String
$Window.ShowDialog()

simplewindow-01

That’s all there is to it!

In part 2 I’ll look at some of the obvious shortcomings of this simple example.

Posted in PowerShell | Leave a comment

PowerShell and SQL Server – Part 1

I recently watched a video of Mike Robbins’ presentation at the PowerShell and DevOps Global Summit 2016. It’s titled “Building Unconventional SQL Server Tools in PowerShell with Functions and Script Modules“. It makes for interesting viewing. One of Mike’s comments stuck with me; that using the .Net framework was the way he chose to interact with SQL Server because it didn’t rely on any of the SQL Management Objects, nor did it require PowerShell to be installed on the target server.

Like Mike I’ve been dealing with SQL Server since version 6.5 and PowerShell since version 1.0, although I can’t claim as much in-depth SQL knowledge as Mike. In my day job I deal with the whole gamut of SQL Server versions and finding a consistent way of dealing with all of them from a PowerShell perspective has been… interesting.

The .Net approach is great, because as far as I can tell the System.Data.SqlClient class has been available in .Net framework since version 1.1, so it should be available on almost any machine with any version of PowerShell.

In this, the first of a series of articles, I’m going to talk about the basics of accessing SQL Server using PowerShell and the .Net framework . Later articles will address different topics, including inventorying and monitoring SQL Server using this approach.

First the basics.

To get SQL Server connectivity going we need to use 3 .Net objects: An SQLConnection, an SQLCommand and an SQLDataReader. The SQLConnection establishes connectivity with the target server, the SQLCommand defines what task is performed and the SQLDataReader receives the results, if any.

Creating an SQLConnection is easy enough:

$connection = New-Object System.Data.SqlClient.SqlConnection

In order for the object to connect to the server it needs a Connection String. Typically this will contain a Data Source (server and if necessary instance), Initial Catalog (database) and authentication details. For a connection using SQL Server authentication the Connection String might look like:

"Data Source=SQLSERVER;Initial Catalog=Database1;User ID=sqluser;Pwd=4P4$$w0rd"

So to make the connection we set the object’s Connection String and call its Open method :

$connection.ConnectionString = "Data Source=SQLSERVER;Initial Catalog=Database1;User ID=sqluser;Pwd=4P4$$w0rd"
$connection.Open()

Next we need to decide what we want to do with our connection. Typically we’ll want to run a query or other T-SQL. We use the SQLCommand object for this and we’ll set the CommandText property with the T-SQL we want to use and the Connection property with the Connection object we previously created.

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT CustomerName, PostCode FROM Customers"
$command.Connection = $connection

I won’t go in to the details of T-SQL here, but bear in mind that the CommandText property doesn’t do any validation. The first that you know about any typos is when you execute the command. In our example we’re going to use the SQLDataReader object to execute our SQL command.

$reader =  $command.ExecuteReader()

We can now move through the returned data by using the Read method of the SQLDataReader object:

while ($reader.Read())
{
    Write-Output "$($reader['CustomerName']),$($reader['PostCode'])"
}

Once we’re done then we can tidy up by closing the SQLDataReader:

$reader.Close()

This is a good first example but what we haven’t got is an object containing all of our data, like we would have from Import-CSV. We could create a PSCustomObject and map the SQL columns, but there’s an easier way. The .Net framework includes a System.Data.DataTable object class. We can load our SQL data into a DataTable and then treat it like any other object (by piping it to Out-GridView for example):

$datatable = New-Object System.Data.DataTable
$datatable.Load($reader)
$reader.Close()

That about covers it for Part 1.

Building Unconventional SQL Server Tools in PowerShell

Posted in PowerShell | Leave a comment