Automatically Save Attachment Based On EXCEL Value?

[NYCAnalyst]

Hi,

I get a bunch of reports daily in my e-mail. I already have Outlook rules set up to forward them to certain folders if they are sent by certain people / have a certain subject.

What I want to know is a macro (I assume that this will be placed in "ThisOutlookSession") that can automatically save the attachment coming in a certain folder in a certain place, with a different name.

For example, save an attachment as cell B5 of "Sheet1" of the attachment in a certain folder.

Thanks much.

[Demosthine]

Good Evening.

If you check out Thread 7996, it should have almost all of the answers for you. It certainly has the hardest parts of it regarding access the individual mail messages and then the attachments. That's a great place to start your project.

Scott

[NYCAnalyst]

Hi Scott,

Thanks, I set up a script to run whenever an email containing the file gets added to the folder I want, but how do I get the script to save the file based on an excel value?

This is the script I have so far:

[VBA]Public Sub RuleScriptSaveTemp(Item As MailItem)

Dim olAtt As Attachment
Dim i As Integer

If Item.Attachments.Count > 0 Then
For i = 1 To Item.Attachments.Count
Set olAtt = Item.Attachments(i)
olAtt.SaveAsFile "C:\Temp\" & olAtt.Sheets("Sheet1").Range("C2").Value
Next
End If
Set olAtt = Nothing

End Sub
[/VBA]

[Demosthine]

Good Evening.

Once you've reached this point, you will have to save the attachment either to a temporary directory or a static directory. You can open the file through code or use the Excel4Macro called GetData. Ultimately, though, you have to save the file after you retrieve the data with the new name or simply rename it.

For information on opening the workbook through code and getting the data manually, see this thread here.

For information on the GetData Macro, see Knowledge Base Article.

Happy Reading.
Scott

[NYCAnalyst]

Good Evening.

Once you've reached this point, you will have to save the attachment either to a temporary directory or a static directory. You can open the file through code or use the Excel4Macro called GetData. Ultimately, though, you have to save the file after you retrieve the data with the new name or simply rename it.

For information on opening the workbook through code and getting the data manually, see this thread here.

For information on the GetData Macro, see Knowledge Base Article.

Happy Reading.
Scott

Those are excel macros, how can I get that to work in Outlook 07?

Thanks.

[Demosthine]

Good Afternoon.

Sorry it took so long, but I had some other things come up.

So here is some code that I have working as requested. There are a couple of things you'll want to note.

01.) Each attachment is saved to your Temporary Folder that is defined in your Environment Variables.

02.) There is a separate Destination variable that you'll want to change to whatever your actual Save To Folder is. I have it set back to the Temporary Folder for now.

03.) As requested, it takes the value from Cell B5 as the new filename.

04.) After the Save As is complete, it deletes the temporary file.

[VBA]
Option Explicit
Public Sub Save_Attachments()
Dim folInbox As MAPIFolder
Dim folSaved As MAPIFolder
Dim olmMessage As MailItem
Dim olaAttachment As Attachment
Dim strTemp As String
Dim strDestination As String
Dim strFilePath As String
Dim appExcel As Object
Dim wbkAttachment As Object
Set folInbox = Session.GetDefaultFolder(olFolderInbox)
Set folSaved = folInbox.Folders.Item("Saved")
strTemp = Environ("Temp")
strDestination = strTemp
Set appExcel = CreateObject("Excel.Application")
For Each olmMessage In folSaved.Items
If olmMessage.UnRead = True Then
olmMessage.UnRead = False
If olmMessage.Attachments.Count > 0 Then
For Each olaAttachment In olmMessage.Attachments
With olaAttachment
strFilePath = strTemp & "\" & .FileName
.SaveAsFile (strFilePath)
Set wbkAttachment = appExcel.workbooks.Open(strFilePath)
With wbkAttachment
.SaveAs strDestination & "\" & _
.Worksheets("Sheet1").Range("B5").Value
.Close
End With

Kill strFilePath
End With
Next olaAttachment
End If
End If
Next olmMessage
appExcel.Quit
End Sub
[/VBA]

Enjoy.
Scott