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;}

Advertisements

8 thoughts on “Save and Open Outlook Attachments using Powershell

  1. Certainly I like your web site, however you need to test the spelling on several of your posts. Many of them are rife with spelling issues and I find it very bothersome to tell you. On the other hand I will certainly come again again!

  2. I was recommended this website through my cousin. I’m not certain whether or not this submit is written via him as nobody else understand such particular about my difficulty. You’re amazing! Thanks!

  3. It’s in reality a great and helpful piece of info. I am satisfied that you simply shared this helpful information with us. Please keep us informed like this. Thanks for sharing.

  4. Not good format code, 😥

    Suggestions:
    Maybe useful donwload embedded images (inline images) too, and attachments, using powershell. From Outlook, and to from MSG files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s