Excel Macros including Outlook

  • Thread starter Thread starter Tim P.
  • Start date Start date
T

Tim P.

I was wondering if there was a way in Excel to batch save
a group of files that are emailed daily through Outlook,
to a previously assigned generic name.

For example, we get reports emailed to us every day (in
Outlook) that replace the same file from the previous
day. Instead of opening each individual file and doing
a "save as", is there a way to run a macro to auto-save
those files to their pre-determined name? Thank you for
your help.
 
Tim,

The macro below requires a reference to MS Outlook, and will save all .xls
attachments in the folder C:\Excel, with the default name of
"Attachment.xls".

HTH,
Bernie
MS Excel MVP

Sub SaveAttachment()
Dim oMapi As NameSpace
Dim oInbox As MAPIFolder
Dim oMail As MailItem
Dim oAtt As Attachment
Dim myAnswer As VbMsgBoxResult
Dim iFor As Integer

Set oMapi = GetNamespace("MAPI")
Set oInbox = oMapi.GetDefaultFolder(olFolderInbox)
For iFor = 1 To oInbox.Items.Count
If oInbox.Items(iFor).Class = olMail And _
oInbox.Items(iFor).Attachments.Count <> 0 Then
For Each oAtt In oInbox.Items(iFor).Attachments
If Right(oAtt, 4) = ".xls" Then
'Uncomment this line to use the attachment's filename
' oAtt.SaveAsFile "C:\Excel\" & oAtt.FileName
oAtt.SaveAsFile "C:\Excel\Attachment.xls"
End If
Next oAtt
End If
Next iFor
End Sub
 
Back
Top