Save and Open Outlook Attachments using Powershell

So there I was, minding my own business, when a Project came my way. One of the things the Project manager wanted was a survey to be mailed to all the Business Unit Contacts. The survey was an Excel Spreadsheet with about 10 questions on it. Easy enough, you say. He then wanted the answers collected in a master spreadsheet for his perusal.  As he was far too busy to collect these himself, especially when he realized that there was going to be over 300 of them, I was tasked with this part of the project. Bare in mind that all the surveys were comming back as email attachments and they all had the same name. Naturally, I wrote a script that would do the following:

1. Open Outlook and let me select the Folder that I was collecting the surveys in.
(Got to love those Outlook rules!)

2. Save the *.xlsx attachments in my c:temp directory.

3. Open my master spreadsheet and the survey response, copy the answers and paste them into a new column in the Master.

Here is the script I came up with:

# ==============================================================================================
#
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
#
# NAME:
#
# AUTHOR:  The OldDog

# DATE  : 8/10/2010
#
# COMMENT:
#
# ==============================================================================================
# <-- Script ------------------------->
# This part gets the file attachments

$o = New-Object -comobject outlook.application
$n = $o.GetNamespace("MAPI")
$f = $n.PickFolder()
$filepath = "c:\temp"
$f.Items| foreach {
&nbsp;&nbsp;&nbsp; $SendName = $_.SenderName
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $_.attachments|foreach {
&nbsp;&nbsp;&nbsp; Write-Host $_.filename
&nbsp;&nbsp;&nbsp; $a = $_.filename
&nbsp;&nbsp;&nbsp; If ($a.Contains("xlsx")) {
&nbsp;&nbsp;&nbsp; $_.saveasfile((Join-Path $filepath "$SendName.xlsx"))
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp; }
}

# This part opens Excel and does the copy/paste magic.

$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\ProjectMaster.xlsx")
dir C:temp*.xlsx |
ForEach-Object {
$FileName = $_.name
$n = $FileName.tostring()
$FullName =&nbsp; "c:\temp\$n"
$wb1 = $xl.Workbooks.Open($FullName)
$ws = $wb1.Worksheets.Item(1)&nbsp;&nbsp;&nbsp;&nbsp;
$ws.Activate
$ws.Range("C5:C23").Select()
$ws.Range("C5:C23").Copy()

$wb.Activate()
$ws1 = $wb.Worksheets.Item("Survey Says")&nbsp;&nbsp;&nbsp;
$mainRng = $ws1.UsedRange.Cells
$ColCount = $mainRng.Columns.Count
$xCol = $ColCount
$xCol = $xCol + 1
Write-Host $xCol
$ws1.Cells.Item(1,$xCol) = $n
$ws1.Cells.Item(2,$xCol).Select()
$ws1.Cells.Item(2,$xCol).PasteSpecial(-4163)
$wb1.Close()
}
#<------ End Script ---------------------------------->
# For finding Attachments in Outlook
# This works as well
$o = New-Object -comobject outlook.application
$n = $o.GetNamespace("MAPI")
$f = $n.PickFolder()

&nbsp;foreach ($item in $f.items)
&nbsp;{
&nbsp;$sender = $item.SenderName
&nbsp;foreach($attach in $item.Attachments)
&nbsp;{&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if($attach.filename.contains("xlsx"))
&nbsp;{
&nbsp;write-host "$sender sent $($attach.filename)"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;&nbsp;&nbsp;&nbsp; }
&nbsp;}