Richard Siddaway's Blog

The original version of this function only did one pass at deleting and had to iterate through all of the folders to find the Deleted Items folder.  This time we use the collection of folders we created using the get-mailfolders function to go directly to the Deleted Items folders

function clear-deletedmail {            
            
$outlook = New-Object -ComObject Outlook.Application            
            
get-mailitemcount -deleted            
            
$folders | where {$_.Path -like "*deleted*"} |            
foreach {             
  $mailfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
  if ($mailfolder.Items.Count -gt 0){            
    do {            
      foreach ($item in $mailfolder.Items){$item.Delete()}            
    } while ($mailfolder.Items.Count -gt 0)            
  }               
}            
get-mailitemcount -deleted            
}

The function starts by creating an Outlook object. The get-mailitemcount function has been revised to provide just the deleted or junk folders (I’ll post about the revisions next)

The folders collection is filtered to select just the Deleted Items folders (I’m assuming the word deleted hasn’t been used in any other folder names).  Each of the Deleted Items folders is accessed using the GetFoderFromID function using the EntryID and StoreID saved in the objects in the folder. If the number of items in the folder is not zero we create a do loop to control the deletion. This will keep calling the foreach loop that performs the deletion until everything is deleted (remember our original function need several passes to delete all the items in the folder)

A final call to get-mailitemcount shows the position after all of the items are deleted.

The functions we’ve seen so far have involved iterating through the whole set of Outlook folders. That’s a lot of folders (I have 4 email accounts with lost of folders). The trick is to do this just once and then use the GetFolderFromID method at the Outlook.Session level to access individual mailboxes

The problem is that this method uses the EntryID and the StoreID for the folder.  Examples are shown below

StoreID : 0000000038A1BB1005E5101AA1BB08002B2A56C200006D736E636F6E2E646C6C
0000000000000000E92FEB759650448683B87DE522AA494800433A5C55736572735C526963
686172645C417070446174615C4C6F63616C5C4D6963726F736F66745C4F75746C6F6F6B5C
726963686172645F73696464617761795F686F746D61696C2E6F737400

EntryID : 000000000A9D24A8D4E32445825D043ADB203A6F0100D9539C2261A6BB45B9DA
B62C7081B3C101002100FFFF0000

Path    : \\emailaddress@hotmail.com\Deleted Items

Now I for one don’t want to be typing in the EntryID never mind the StoreID – chances of getting that right range from zero to about … zero

What we can do convert all of our Outlook functions (the two so far and the others I’ve got planned) into a module. In the psm1 file I can put a line that says

$folders = get-mailfolders

That creates a collection of objects containing the path, storeid and entryid for all of the folders. We can then search through that collection much faster than the outlook folders.

We create the collection like this

function get-mailfolders {            
$outlookfolders = @()            
$outlook = New-Object -ComObject Outlook.Application            
foreach ($folder in $outlook.Session.Folders){            
              
  foreach($mailfolder in $folder.Folders ) {            
   $olkf = New-Object PSObject -Property @{            
    Path = $($mailfolder.FullFolderPath)            
    EntryID = $($mailfolder.EntryID)            
    StoreID = $($mailfolder.StoreID)            
   }            
               
   $outlookfolders += $olkf            
               
  }             
}            
$outlookfolders            
}

Loop through the folders as we’ve already seen and create an object that has the path, storeid and entryid. Add the object to the collection and output the collection at the end of the function.

We’ll see how to use this collection in the revised clear-deletedmail function

Continuing our look at scripting against Outlook & the hotmail connector

Many of the emails I get end up being read once and deleted. This leaves a ton of stuff in the deleted items folders. Periodically I’ll clean these up. This is one way how it can be accomplished.

function clear-deletedmail {            
            
$outlook = New-Object -ComObject Outlook.Application            
foreach ($folder in $outlook.Session.Folders){            
              
  foreach($mailfolder in $folder.Folders ) {            
    if ($mailfolder.Name -eq "Deleted Items" -and $mailfolder.Items.Count -gt 0){            
      foreach ($item in $mailfolder.Items){$item.Delete()}            
    }               
  }             
}            
}

All I’m doing is looping through my top level folders (email accounts) and for each of them finding a folder called Deleted Items.  If it has any content I will delete it.  Notice that the deletions have to occur on the item not at the folder level.

The contents of the folders can be tested using the function we saw earlier

get-mailitemcount | where {$_.Folder –like “Deleted*”}

A couple of notes on this function:

1. Its inefficient iterating through all of the folders
2. It doesn’t necessarily delete all of the items in one pass

The fix to this is use the get-mailitemcount function to test if we need to delete and keep calling the function until the folder is empty.  There is a way to access the folder directly that I will be testing next

On my home laptop I use Live Mail to aggregate my hotmail accounts. On my travelling netbook I decided to try the Outlook Connector

http://office.microsoft.com/en-us/outlook-help/microsoft-office-outlook-hotmail-connector-overview-HA010222518.aspx

This add in enables you to access hotmail accounts from Outlook & because I’m using Outlook 2010 I can have multiple mailboxes open at once.

This leads to some interesting PowerShell opportunities as having multiple mailboxes open means that some of the standard approaches won’t work – most scripts you see assume that you are connecting to an Exchange mailbox and that there is only one of them.

As an example consider getting a list of all of the mail folders and the number of items in each

function get-mailitemcount {            
Add-Type -Assembly Microsoft.Office.Interop.Outlook            
$olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]            
            
$outlook = New-Object -ComObject Outlook.Application            
foreach ($folder in $outlook.Session.Folders){            
              
  foreach($mailfolder in $folder.Folders ) {            
               
    New-Object -TypeName PSObject -Property @{            
      Mailbox = $($folder.Name)            
      Folder = $($mailfolder.Name)            
      ItemCount = $($mailfolder.Items.Count)            
    } | select Mailbox, Folder, ItemCount            
  }             
}            
}

The first two lines add the interop assembly and create an object representing the default folders. If these are used directly all we get is the folders for the default (first) mailbox.

Create an object for the outlook application and iterate through the folders in the session – these are the individual mailboxes.

For each of those create an object and set the properties. The select ensures the object properties display in the order we need.

I used foreach instead of foreach-object so that I could use a variable rather than $_ as it would have confusing with nested foreachs

There is quite a bit to play with here and some very interesting PowerShell issues.

The rescheduled UG session (via Live Meeting) on using Office products with PowerShell is tomorrow. Details from

http://msmvps.com/blogs/richardsiddaway/archive/2011/06/21/rescheduled-ug-meeting.aspx

One sad casualty of my move to Windows 7 and Office 2010 is the OneNote PowerShell provider. http://bdewey.com/2007/07/18/onenote-powershell-provider/

It doesn’t work vey well in this scenario. It will read oneNote pages but it doesn’t want to create, modify pages or content.

Shame but this is one piece of functionality I will have to drop

If anyone comes across an update to the provider or an alternative I appreciate a comment being left.

The OneNote API is not pretty as it means working directly with XML – yuck.

While preparing for this months UK user group talk on PowerShell and Office I decided I wanted to include a look at working with the OpenXML format for Office documents that was introduced in Office 2007. These are the .docx and .xlsx versions of Word and Excel respectively.

When writing PowerShell in Practice (www.manning.com/siddaway) I included a small section on working with Word and Excel. This contained a bit on using OpenXML.

I’ve rebuilt my system to Windows 7 since those days so needed to re-install the OpenXML support. I went searching and found it on codeplex – http://powertools.codeplex.com/

The latest release is 1.1.1

Codeplex contains the source code. You will need Visual Studio and a copy of the OpenXML SDK to compile it. The link given on codeplex for the SDK is wrong. You need to use these links

Version 1

http://download.microsoft.com/download/2/7/F/27FF6744-D970-4FFB-90B8-5226B2B82E0A/OpenXMLSDK.msi

Version 2

http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=5124

Pre-compiled binaries

http://staffdotnet.com/services/powertools.html

There is a problem in that version 1 of the SDK won’t install on Windows 7 and if you want to use the pre-compiled binaries instead of compiling your own they are created with version 1. This causes errors when SDK 2.0 is used on Windows 7 (with Office 2010).

A couple of code changes are needed if you want to compile the code against version 2 of the SDK – see http://powertools.codeplex.com/workitem/5057 for the code changes

This seems to work OK and a way to load the snapin as a module was given here http://msmvps.com/blogs/richardsiddaway/archive/2011/06/19/snapins-to-modules.aspx

I’ll be doing some more testing and will report any issues. So far all of my old openxml scripts just work.

If anyone wants a copy of the binaries just leave a comment and I’ll post them

Apologies for rescheduling with no notice but my broadband router failed tonight.

When: Thursday, Jun 30, 2011 7:30 PM (BST)

Where: Live Meeting
*~*~*~*~*~*~*~*~*~*

Re-scheduled – using PowerShell to automate Office including Word, Excel, OneNote, Visio and Access

Notes

Richard Siddaway has invited you to attend an online meeting using Live Meeting.
Join the meeting.
Audio Information
Computer Audio
To use computer audio, you need speakers and microphone, or a headset.
First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.
Troubleshooting
Unable to join the meeting? Follow these steps:

1. Copy this address and paste it into your web browser:
   https://www.livemeeting.com/cc/usergroups/join
2. Copy and paste the required information:
   Meeting ID: WNGBC8
   Entry Code: k&w2kpF8d
   Location: https://www.livemeeting.com/cc/usergroups

If you still cannot enter the meeting, contact support

Notice
Microsoft Office Live Meeting can be used to record meetings. By participating in this meeting, you agree that your communications may be monitored or recorded at any time during the meeting.

The PowerShell UK UG meets tomorrow (Tuesday 21 June) at 7.30pm BST

Topic: PowerShell and Office

see http://msmvps.com/blogs/richardsiddaway/archive/2011/06/12/powershell-user-group-21-june-2011.aspx for Live Meeting details

In PowerShell v1 we had snapins to extend and add functionality. In PowerShell v2 modules were added. Modules give us more flexibility and control.

I’ve had to re-install a couple of snapins recently as part of the preparation for the presentation to the user group. I’d not put them on to my laptop when I rebuilt it as Windows 7.

The PowerShell provider for OneNote comes as an MSI that installs the snapin and registers it with Powershell – can’t do much with that – easier to leave as is.

The OpenXML snapin is available as source code or the binaries can be down loaded which consist of a dll and a help file.

To get the snapin loaded either use the supplied batch file to register the dll or create a module manifest file

#
# Module manifest for module ‘OpenXml’
#
# Generated by: Richard
#
# Generated on: 18/06/2011
#

@{

# Script module or binary module file associated with this manifest
ModuleToProcess = ‘OpenXml.PowerTools.dll’

# Version number of this module.
ModuleVersion = ‘1.0’

# ID used to uniquely identify this module
GUID = ‘0275db55-3bf5-4eec-aecd-1a5cd1764c32’

# Author of this module
Author = ‘Richard’

# Company or vendor of this module
CompanyName = ‘Macdui’

# Copyright statement for this module
Copyright = ‘Macdui’

# Description of the functionality provided by this module
Description = ‘Loads OpenXML snapin’

# Minimum version of the Windows PowerShell engine required by this module
PowerShellVersion = ”

# Name of the Windows PowerShell host required by this module
PowerShellHostName = ”

# Minimum version of the Windows PowerShell host required by this module
PowerShellHostVersion = ”

# Minimum version of the .NET Framework required by this module
DotNetFrameworkVersion = ”

# Minimum version of the common language runtime (CLR) required by this module
CLRVersion = ”

# Processor architecture (None, X86, Amd64, IA64) required by this module
ProcessorArchitecture = ”

# Modules that must be imported into the global environment prior to importing this module
RequiredModules = @()

# Assemblies that must be loaded prior to importing this module
RequiredAssemblies = @()

# Script files (.ps1) that are run in the caller’s environment prior to importing this module
ScriptsToProcess = @()

# Type files (.ps1xml) to be loaded when importing this module
TypesToProcess = @()

# Format files (.ps1xml) to be loaded when importing this module
FormatsToProcess = @()

# Modules to import as nested modules of the module specified in ModuleToProcess
NestedModules = @()

# Functions to export from this module
FunctionsToExport = ‘*’

# Cmdlets to export from this module
CmdletsToExport = ‘*’

# Variables to export from this module
VariablesToExport = ‘*’

# Aliases to export from this module
AliasesToExport = ‘*’

# List of all modules packaged with this module
ModuleList = @()

# List of all files packaged with this module
FileList = @()

# Private data to pass to the module specified in ModuleToProcess
PrivateData = ”

}

Use new-ModuleManifest to create the file and save it with as a .psd1 file. 

Its now available as a module and can be used as such.