SSIS Script Task, Extracting attachments from Outlook 2007 in VB.NET

 Introduction

In my experience there are a number of customers that send daily/ monthly data, via email and are unwilling to change this. I wanted to automate this, so that I did not need to manually put a file in a certain location whenever I recieve this mail.

I looked online for a solution and some were available but nothing was free. and the free stuff didn't support smtp. So when i came accross the reference code I modified it for my script task...

What the Code does

SSIS Script task code to download Outlook attachment recieved from user test@test.com and save them to local or network file location c:\Test and move email to a completed folder in outlook called test.

Requirements

1. SQL server 2008 or 2012
2. Visual Studio Premium (Need MS SDK that comes with VS 2010 Premium)
3. Outlook (Tested on 2007 and 2010)

Restrictions

1. Fails when it sees meeting invites (only tested this with outlook 2007)
2. In production, had to use AD account with primary mailbox instead of service account.
3. Requires outlook installed on server

Using the code

1. Install outlook on machine with SSIS and setup outlook mail.
2. Create Script Task, Script Language: Microsoft Visual Basic 2008
3. Add Microsoft Outlook Reference

VS 2008
Edit Script Task-> Data Sources -> Add New Data Source-> Object-> Next-> Add Reference-> COM-> Microsoft Outlook 12.0 Object Library

VS 2010
Edit Script Task-> Project -> Add Reference... -> COM-> Microsoft Outlook 12.0 Object Library

4. Replace Script Scode with below- Edit test@tester.com and c:\test\
5. Run and hopefully all will work well.

Imports System
Imports System.Data
Imports System.Reflection
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim oApp As Outlook.Application = New Outlook.Application()     ' Create Outlook application.
        Dim oNS As Outlook.NameSpace = oApp.GetNamespace("mapi")        ' Get Mapi NameSpace.
        Dim Attachment As Outlook.Attachment
        Dim FileName As String
        'oNS.Logon('oNS.Logon("Username", "Password", False, True) ' TODO:
        ' Get Messages collection of Inbox
        Dim oInbox As Outlook.MAPIFolder = oNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
        Dim oItems As Outlook.Items = oInbox.Items
        '*** CHANGE OUTLOOK DESTINATION FOLDER 
        Dim oDestFolder As Outlook.MAPIFolder = oInbox.Folders("test") 
        Console.WriteLine("Total : " & oItems.Count)
        ' Get unread e-mail messages.
        'oItems = oItems.Restrict("[Unread] = true")
        Console.WriteLine("Total Email : " & oItems.Count)
        ' Loop each unread message.
        Dim oMsg As Outlook.MailItem
        Dim i As Integer
        Dim j As Integer
        i = 0
        j = 1
        For i = 1 To oItems.Count
            oMsg = oItems.Item(i)
            'MessageBox.Show(oMsg.SenderName, "Sender Name")
            'MessageBox.Show(oMsg.Attachments.Count, "# of Attachments")
            '*** ADD YOUR SENDER HERE (use @ not A)
            If (oMsg.SenderName = "testAtest.com") Then 
                For Each Attachment In oMsg.Attachments
                    MessageBox.Show(Attachment.FileName)
                    '*** CHANGE YOUR DIRECTORY HERE
                    FileName = "c:\Test\" & Attachment.FileName 
                    Attachment.SaveAsFile(FileName)
                    j = j + 1
                Next
                oMsg.Move(oDestFolder)
            End If
            i = i + 1
        Next
        ' Log off.
        oNS.Logoff()
        ' Clean up.
        oApp = Nothing
        oNS = Nothing
        oItems = Nothing
        oMsg = Nothing
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class