Automating inbound e-mail.

[Peager]

I have an e-mail sent to me once a day that I need to automate processing for. It has an e-mail attachment that I need to save to a network directory and processing using a SSIS package. I would think with all the automation we do these days that someone has solved this...

 

[robcarr]

hi,

not sure what ssis pacakge is.

But i use this routine through an excel spreadhseet and this saves all emails that have a specific file type to a network drive

Sub GetOutlookFiles()

  On Error GoTo SaveAttachmentsToFolder_err
  Dim ns As Namespace
  Dim Inbox As MAPIFolder
  Dim SubFolder As MAPIFolder
  Dim Item As Object
  Dim Atmt As Attachment
  Dim FileName As String
  Dim i As Integer
  Dim varResponse As VbMsgBoxResult
  Set ns = GetNamespace("MAPI")
  Set Inbox = ns.GetDefaultFolder(olFolderInbox)
  Set SubFolder = Inbox.Folders("Report Files")
  i = 0
  If SubFolder.Items.Count = 0 Then
    MsgBox "There are no messages in the Sales Reports folder.", vbInformation, _
           "Nothing Found"
    Exit Sub
  End If
  For Each Item In SubFolder.Items
    For Each Atmt In Item.Attachments
      If Right(Atmt.FileName, 3) = "xls" Then
              FileName = "U:\Private\Temp\" & Format(Item.ReceivedTime, "dd_mm_YYYY") & Atmt.FileName
        Atmt.SaveAsFile FileName
        i = i + 1
      End If
    Next Atmt
   
  Next Item
  For Each Item In SubFolder.Items
  Item.UnRead = False
  Item.Delete
  Next Item
  For Each Item In SubFolder.Items
  Item.UnRead = False
  Item.Delete
  Next Item
   For Each Item In SubFolder.Items
  Item.UnRead = False
  Item.Delete
  Next Item
  
SaveAttachmentsToFolder_exit:
  Set Atmt = Nothing
  Set Item = Nothing
  Set ns = Nothing
  Exit Sub
SaveAttachmentsToFolder_err:
  MsgBox "An unexpected error has occurred." _
    & vbCrLf & "Please note and report the following information." _
    & vbCrLf & "Macro Name: GetAttachments" _
    & vbCrLf & "Error Number: " & Err.Number _
    & vbCrLf & "Error Description: " & Err.Description _
    , vbCritical, "Error!"
  Resume SaveAttachmentsToFolder_exit
End Sub

the "report files" folder is within the inbox in outlook, I had to set a reference to outlook in excel.

I hope this is what you are looking for.



Hope this is of use, Rob.

 

[Peager]

Rob,

Thanks for sharing your idea. It's one I haven't seen before.

SSIS = SQL Server Intigration Services. (The old new DTS).

Best regards,

Paul