save excel attachments sent by email

[bonovox1]

Hi, im trying to write a macro that will save files from a specific email address into a specific folder. I will be recieving files from 5 differnt mail addresses on a daily basis and i want to be avle to save them to a date stamped folder.

 

[Andrew Poulsom]

Is that an Excel question or an Outlook question? Maybe this will help you:

http://www.fontstuff.com/outlook/oltut01.htm

 

[bonovox1]

thanks...could be very useful

 

[bonovox1]

the macro will not pick up files with the .xlsx extension for some reason. any ideas?



Sub SaveAttachmentsToFolder()
' This Outlook macro checks a named subfolder in the Outlook Inbox
' (here the "Sales Reports" folder) for messages with attached
' files of a specific type (here file with an "xls" extension)
' and saves them to disk. Saved files are timestamped. The user
' can choose to view the saved files in Windows Explorer.
' NOTE: make sure the specified subfolder and save folder exist
' before running the macro.
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 FileName As String
Dim i As Integer
Dim varResponse As VbMsgBoxResult
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubFolder = Inbox.Folders("sample") ' 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 sample 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.
FileName = "C:\Email Attachments\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item


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) = "csv" Then
' This path must exist! Change folder name as necessary.
FileName = "C:\Email Attachments\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item


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) = "xlsx" Then
' This path must exist! Change folder name as necessary.
FileName = "C:\Email Attachments\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
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 C:\Email Attachments 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,C:\Email Attachments", 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

 

[Andrew Poulsom]

That's because you are looking for xls:

If Right(Atmt.FileName, 3) = "xls" Or Right(Atmt.FileName, 4) = "xlsx" Then

 

[bonovox1]

it still doesn't pick up the xlsx file?


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" Or Right(Atmt.FileName, 3) = "xlsx" Then
' This path must exist! Change folder name as necessary.
FileName = "C:\Email Attachments\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item

 

[Andrew Poulsom]

I wrote:

If Right(Atmt.FileName, 3) = "xls" Or Right(Atmt.FileName, 4) = "xlsx" Then

 

[bonovox1]

andrew...thanks for your help on this. I have one last problem regarding these files that i now have saved. These documents all have different file names but have the same data in them. i need some way of naming each file with the email that they sent it with. so if company x sent file1, i would name it companyx. Would this be possible at all?

 

[Andrew Poulsom]

Just change this to suit:

FileName = "C:\Email Attachments\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName

 

[bonovox1]

andrew i recieve 10 different files with the same file names. i put these in a folder and i am then able to run the macro to save these all at :\Email Attachments. i recieve each of these files from different companys.

so the emails i receive from the following type of address; x@companyx.com and so on. I want to name the repective file that i down load from this to filex_companyx. Is that possible by tweaking my macro?