SSIS Script Task, Extracting attachments from Outlook 2007 in VB.NET
This is an alternative for "Extracting mails and attachments from Outlook 2003 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
- SQL server 2008 or 2012
- Visual Studio Premium (Need MS SDK that comes with VS 2010 Premium)
- Outlook (Tested on 2007 and 2010)
Restrictions
- Fails when it sees meeting invites (only tested this with outlook 2007)
- In production, had to use AD account with primary mailbox instead of service account.
- Requires outlook installed on server
Using the code
- Install outlook on machine with SSIS and setup outlook mail.
- Create Script Task, Script Language: Microsoft Visual Basic 2008
- Add Microsoft Outlook Reference
- Replace Script Scode with below- Edit test@tester.com and c:\test\
- Run and hopefully all will work well.
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
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