Click here to Skip to main content
12,954,601 members (56,948 online)
Click here to Skip to main content
Add your own
alternative version


12 bookmarked
Posted 24 May 2012

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

, 28 Oct 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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)


About the Author

United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionGetting error Pin
murali dhar28-May-15 7:24
membermurali dhar28-May-15 7:24 
QuestionMessage Automatically Removed Pin
21-Jun-12 23:27
memberMember 375892221-Jun-12 23:27 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170525.1 | Last Updated 28 Oct 2013
Article Copyright 2012 by gusmundo
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid