Using PowerShell to create rules in Outlook

I recently started using Microsoft PowerShell to carry out administrative tasks and it didn’t take long for me to fall in love with it. Being a script fanatic using DOS instead of Windows, VBScript and JScript, Console-apps in favor to Windows apps, regex, etc. it’s easy to see the tremendous power in PowerShell; you can say that it’s the command-prompt on steroids :-) I’m lovin’ it!

The task – Creating an Outlook rule using PowerShell

Ok, do this might seem to be a trivial task, but as always when you think something seems to be easy you run into problems, especially when working with COM-objects/Automation. So, in order to solve my problem I had to do a lot of looking around at the forums on the web.

The script below creates a new rule in Outlook (I’m currently running Outlook 2010) that checks the sender of the message and looks for certain words in the subject field. If the sender is e.g. foo@bar.com (FROMEMAIL) and the subject contain the words ‘Hello’ and ‘World’ (SUBJECTWORDARRAY), the message should be forwarded to fii@bar.com (FORWARDEMAIL) and moved to the MyCustomFolder (MOVETOFOLDER) folder which is a subfolder to the Inbox folder.

##########################################
# Based on PowerShell 2.0
##########################################
function add_outlook_rule
{
    param([string]$RuleName,
          [string]$FromEmail,
          [string]$ForwardEmail,
          [string]$RedirectFolder,
          [string[]]$SubjectWords)
              
    Add-Type $class -ReferencedAssemblies Microsoft.Office.Interop.Outlook
    
    Add-Type -AssemblyName microsoft.office.interop.outlook 
    $olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]
    $olRuleType = "Microsoft.Office.Interop.Outlook.OlRuleType" -as [type]
    $outlook = New-Object -ComObject outlook.application
    $namespace  = $Outlook.GetNameSpace("MAPI")
    $inbox = $namespace.getDefaultFolder($olFolders::olFolderInbox)
    
    $rules = $outlook.session.DefaultStore.GetRules()
    $rule = $rules.Create($RuleName,$olRuleType::OlRuleReceive)

    $SubjectCondition = $rule.Conditions.Subject
    $SubjectCondition.Enabled = $true
    $SubjectCondition.Text = $SubjectWords
     
    $d = [System.__ComObject].InvokeMember(
        "EntryID",
        [System.Reflection.BindingFlags]::GetProperty,
        $null,
        $inbox.Folders.Item($RedirectFolder),
        $null)
    $MoveTarget = $namespace.getFolderFromID($d)
    
    # Uncomment the row below if you want to use the DeletedItems
    # folder as MoveToFolder.
    #$MoveTarget = $namespace.getDefaultFolder(
        $olFolders::olFolderDeletedItems)

    $MoveRuleAction = $rule.Actions.MoveToFolder
    [Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
        "Folder",
        [System.Reflection.BindingFlags]::SetProperty,
        $null,
        $MoveRuleAction,
        $MoveTarget)
    $MoveRuleAction.Enabled = $true

    $FromCondition = $rule.Conditions.From
    $FromCondition.Enabled = $true
    $FromCondition.Recipients.Add($FromEmail)
    $fromCondition.Recipients.ResolveAll()

    $ForwardRuleAction = $rule.Actions.Forward
    $ForwardRuleAction.Recipients.Add($ForwardEmail)
    $ForwardRuleAction.Recipients.ResolveAll()
    $ForwardRuleAction.Enabled = $true

    $rules.Save()
}

##########################################
# SYNTAX: 
##########################################
# add_outlook_rule RULENAME 
#                  FROMEMAIL
#                  FORWARDEMAIL
#                  MOVETOFOLDER
#                  SUBJECTWORDARRAY
##########################################

add_outlook_rule "This is my custom rule" "fii.from@bar.com" "foo.forward@bar.com" "MyCustomFolder" @("foo","bar")

 

And now for the problems I ran into…

Problem number 1

The first problem I ran into was with the criterion that looked for certain words in the Subject field. I found out that the subject field expected an array of type string, but by some strange reason I didn’t get it to work, even tough I Reflected the PIA (Primary Interop Assembly) for Microsoft Outlook and checked exactly what types the class expected and tried to cast the variable in all possible ways available.  After some additional investigation I found out that the string array needed to contain more than one item which means that if you have an array with two or more items the script started to work.

$SubjectCondition = $rule.Conditions.Subject 
$SubjectCondition.Enabled = $true 

# Will not work...
# $a = $(“foo”)

# Works like a charm
$a = $(“foo”,”fii”)

$SubjectCondition.Text = $a

 

Problem number 2

When I tried to set the Folder property on the MoveToFolder object I got an error stating that I was trying to set a property that expected an object that implemented the interface MAPIFolder to a ComObject which apparently doesn’t do that.

This is the error message I received:

Exception setting "Folder": "Cannot convert the "System.__ComObject" value of type "System.__ComObject#{00063006-0000-0000-c000-000000000046}" to type "Microsoft.Office.Interop.Outlook.MAPIFolder"."

No matter how I tried to cast the ComObject to a MAPIFolder the script constantly failed. I tried to get a handle on the desired folder in many different and this part was actually quite easy, but the problem still remained: It was not possible to cast the ComObject to a MAPIFolder. So what now? Give up? Never! :-)

I came up with the idea that maybe the solution would be not to cast the ComObject but instead try to interact with it somehow, and for this we have our .NET friend – Reflection! By using reflection on objects in .NET it is possible to get and set properties on ComObjects and this turned out to be the solution to my problem.

In the first code block below I use the InvokeMember method that is part of reflection for types in .NET to first get a property from a ComObject. There are other ways to get a handle on a folder in Exchange/Outlook, but I wanted to show an alternate way of doing this in this sample. The second code block I set the Folder property on the MoveToFolder action object. This solved my second problem.

$d = [System.__ComObject].InvokeMember(
	"EntryID",
	[System.Reflection.BindingFlags]::GetProperty,
	$null,
	$inbox.Folders.Item($RedirectFolder),
	$null)
$MoveTarget = $namespace.getFolderFromID($d)
	
$MoveRuleAction = $rule.Actions.MoveToFolder
[Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
	"Folder",
	[System.Reflection.BindingFlags]::SetProperty,
	$null,
	$MoveRuleAction,
	$MoveTarget)
$MoveRuleAction.Enabled = $true

 

Problem number 3

When everything started to work (scripts are like that – it’s all or nothing) everything seemed fine until I used the script in a batch where I created many rules on a specific mailbox store. The problem here turned out to be a limit in the definition storage on the Exchange server that only allowed the size of the rule definitions for an account to be 32 kb. In my case I exceeded the limitation and got an error when executing my scripts.

More information

Advertisements

Changing language for PowerPoint presentations

When you want to change the dictionary that is being used for a PowerPoint presentation, you might think that it is just to select all slides and change dictionary or enter some magic dialog where you find some setting for the current presentation, but this is where the problem occur and you realizes that it is not as easy as you might think.

Everything that appear in a PowerPoint slide are considered to be an object. This means that the title of the slide is an object as well as the text area where you have a bulleted list, the additional text areas, etc.. And if you think a little bit further you will soon realize that you need to select each object in each slide in order to change to the correct dictionary. Pheew!

Luckily there is always a way of scripting this, so I created a macro in VBA for PowerPoint that iterates through every slide in the presentation and changes dictionary for all objects found on the slide. The script is quite self-explanatory and you’ll find it below.

Sub SetLanguageIDEnglishUS()
    Dim slideCount, shapeCount, j, k
    slideCount = ActivePresentation.Slides.Count
    For j = 1 To slideCount
        
        ' Change dictionary for all shape objects
        shapeCount = ActivePresentation.Slides(j).Shapes.Count
        For k = 1 To shapeCount
            If ActivePresentation.Slides(j).Shapes(k).HasTextFrame Then
                ActivePresentation.Slides(j).Shapes(k).TextFrame _
                    .TextRange.LanguageID = msoLanguageIDEnglishUS ' msoLanguageIDSwedish
            End If
        Next k
        
        ' Change dictionary for all notes
        shapeCount = ActivePresentation.Slides(j).NotesPage.Shapes.Count
        For k = 1 To shapeCount
            If ActivePresentation.Slides(j).NotesPage.Shapes(k).HasTextFrame Then
                ActivePresentation.Slides(j).NotesPage.Shapes(k).TextFrame _
                    .TextRange.LanguageID = msoLanguageIDEnglishUS ' msoLanguageIDSwedish
            End If
        Next k
    Next j
End Sub

Create a Word document based on a PowerPoint presentation

Sometimes it can be handy to have a more detailed document with information that belongs to the slideshow displayed on the projector. This document can have e.g. code samples, diagrams and other relevant information that doesn’t fit on the PowerPoint presentation. I needed to do this when writing course material, so I created this script that creates a Word document with the slide as a miniature image so that it is possible to add comments or key points.

This script creates a linked PowerPoint slide object in a Word document. The script also extracts the notes belonging to the slide and adds them to the current page. Each slide in the presentation will generate one page in the Word document and the linked PowerPoint slide will be centered and resized to 9 centimeters.

In order to make this script work, simply just paste this code into the macro editor in your word document.

Sub CreateWordPagesBasedOnPowerPointPresentationLink()

    Dim sImagePath As String
    Dim sImageName As String
    Dim objPPT As PowerPoint.Application
    
    Dim oSlide As Slide '* Slide Object
    On Error GoTo Err_ImageSave
    
    strDocPath = InputBox("Path: ", _
        "Path to PowerPoint presentation", _
        "C:\MyPath\MyPresentation.pptx")
    
    Set objPPT = CreateObject("Powerpoint.application")
    'objPPT.Visible = False
    objPPT.Presentations.Open strDocPath
    
    For Each oSlide In objPPT.ActivePresentation.Slides
        If Not oSlide.SlideShowTransition.Hidden = msoTrue Then
            sImageName = oSlide.Name & ".PNG"
        
            oSlide.Copy
    
            Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
                wdInLine, DisplayAsIcon:=False

            Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
            With Selection.Borders(wdBorderTop)
                .LineStyle = Options.DefaultBorderLineStyle
                .LineWidth = Options.DefaultBorderLineWidth
                .Color = Options.DefaultBorderColor
            End With
            With Selection.Borders(wdBorderLeft)
                .LineStyle = Options.DefaultBorderLineStyle
                .LineWidth = Options.DefaultBorderLineWidth
                .Color = Options.DefaultBorderColor
            End With
            With Selection.Borders(wdBorderBottom)
                .LineStyle = Options.DefaultBorderLineStyle
                .LineWidth = Options.DefaultBorderLineWidth
                .Color = Options.DefaultBorderColor
            End With
            With Selection.Borders(wdBorderRight)
                .LineStyle = Options.DefaultBorderLineStyle
                .LineWidth = Options.DefaultBorderLineWidth
                .Color = Options.DefaultBorderColor
            End With
            
            Selection.MoveRight Unit:=wdCharacter, Count:=1
            Selection.TypeParagraph
            Selection.TypeParagraph
            
            If oSlide.NotesPage.Shapes.Placeholders.Count > 0 Then
            Dim foo As Object
                Selection.TypeText (oSlide.NotesPage.Shapes.Placeholders(2).TextFrame.TextRange.Text)
                Selection.TypeParagraph
            End If
            Selection.InsertBreak Type:=wdPageBreak
        End If
        DoEvents
    Next oSlide
    
    objPPT.Quit
    Set objPPT = Nothing
    
    For Each oShape In ActiveDocument.InlineShapes
        With oShape
            .LockAspectRatio = msoTrue
            .Width = CentimetersToPoints(8.99)
            .Height = CentimetersToPoints(6.74)
            .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        End With
    Next oShape

Err_ImageSave:
        If Err <> 0 Then
            MsgBox Err.Description
        End If

End Sub