I am using Outlook and Excel 2010. Everyday I get an email with an excel attachment, I need to save this excel attachment into a folder. I would like to automate this process. I have a rule that sends this email into a folder every day. The name of the outlook folder the email is in is called Daily Net Adds Legacy. I would like the email to be saved in a folder called: \\cable\ncd-shared\DIV-FIN-Business-Analytics-Shared\Adhoc\Tom\DOR\Daily_Net_Adds_Legacy\. Currently I am getting a compile error on the line in Blue. Any help is appreciated.
Code:
Public Sub Dwnld_Legacy()
On Error GoTo SaveAttachmentsToFolder_err
' Declare variables
Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim strFileName As String
Dim i As Integer
Dim varResponse As VbMsgBoxResult
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubFolder = Inbox.Folders("Daily Net Adds Legacy") ' Enter correct subfolder
'name.
i = 0
' Check subfolder for messages and exit of none found
If SubFolder.Items.Count = 0 Then
MsgBox "There are no messages in the Daily Net Adds Legacy folder.", _
vbInformation, _
"Nothing Found"
Exit Sub
End If
' Check each message for attachments
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
' Check filename of each attachment and save if it has "xls" extension
If Right(Atmt.FileName, 3) = "xls" Then
' This path must exist! Change folder name as necessary.
strFileName = "\\cable\ncd-shared\DIV-FIN-Business-Analytics-Shared\Adhoc\Tom\DOR\Daily_Net_Adds_Legacy\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & _
Atmt.FileName
[Blue] Atmt.SaveAsFile strFileName [/Blue]
i = i + 1
End If
Next Atmt
Next Item
' Show summary message
If i > 0 Then
varResponse = MsgBox("I found " & i & " attached files." _
& vbCrLf & "I have saved them into the Daily_Net_Adds_Legacy folder." _
& vbCrLf & vbCrLf & "Would you like to view the files now?" _
, vbQuestion + vbYesNo, "Finished!")
' Open Windows Explorer to display saved files if user chooses
If varResponse = vbYes Then
Shell "Explorer.exe /e,\\cable\ncd-shared\DIV-FIN-Business-Analytics-Shared\Adhoc\Tom\DOR\Daily_Net_Adds_Legacy\", vbNormalFocus
End If
Else
MsgBox "I didn't find any attached files in your mail.", _
vbInformation, "Finished!"
End If
' Clear memory
SaveAttachmentsToFolder_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
Exit Sub
' Handle Errors
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