Click here to Skip to main content
15,666,313 members
Articles / Web Development / ASP.NET
Posted 24 May 2012


12 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.13/5 (5 votes)
28 Oct 2013CPOL1 min read
This is an alternative for "Extracting mails and attachments from Outlook 2003 in VB.NET".


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 and save them to local or network file location c:\Test and move email to a completed folder in outlook called test.


  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)


  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
  4. 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

  5. Replace Script Scode with below- Edit and c:\test\
  6. 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
        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 = "") Then 
                For Each Attachment In oMsg.Attachments
                    '*** CHANGE YOUR DIRECTORY HERE
                    FileName = "c:\Test\" & Attachment.FileName 
                    j = j + 1
            End If
            i = i + 1
        ' Log off.
        ' Clean up.
        oApp = Nothing
        oNS = Nothing
        oItems = Nothing
        oMsg = Nothing
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionGetting error Pin
murali dhar28-May-15 7:24
murali dhar28-May-15 7:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.