This code takes a specific file named SearchResults.xlsx attached to an email then saves it to my hard drive. It sometimes works. I think (I don't know for sure), the issue is with the "kill" portion. Since the file comes everyday (same name), the macro deletes the old to add the new. But it appear, sometimes the old one gets deleted, and the new one is NOT added. In fact MANY times this has happened, and I manually save the file. Then I later (a few hours) go to refresh a Power Query Report that uses the file as a data source, and the file is GONE! Since "kill" deletes the file, I automatically thought of that as the issue. Is it possible to replace that portion of the code that would not KILL, but would replace/overwrite the file instead. This would ensure I always have a file in that folder, at the very least it could be yesterday's.
Option Explicit
Sub Extract_MAP_SeachResultsReport()
Dim OutlookOpened As Boolean
Dim outApp As Outlook.Application
Dim outNs As Outlook.NameSpace
Dim outFolder As Outlook.MAPIFolder
Dim outAttachment As Outlook.Attachment
Dim outItem As Object
Dim saveFolder As String
Dim outMailItem As Outlook.MailItem
Dim inputDate As String, subjectFilter As String
' Delete the previous file
If Dir("C:Usersbarnes22MAP AgileSearchResults.xlsx") <> "" Then
Kill "C:Usersbarnes22MAP AgileSearchResults.xlsx"
End If
' THIS IS WHERE YOU WANT TO SAVE THE ATTACHMENT TO
saveFolder = "C:Usersbarnes22MAP Agile"
If Right(saveFolder, 1) <> "" Then saveFolder = saveFolder & ""
' THIS IS WHERE YOU PLACE THE EMAIL SUBJECT FOR THE CODE TO FIND
' NOTE: If this code is "commented out", ALL attachments in Inbox will be downloaded
subjectFilter = ("Globally Governed QMS Document Report")
OutlookOpened = False
On Error Resume Next
Set outApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set outApp = New Outlook.Application
OutlookOpened = True
End If
On Error GoTo 0
If outApp Is Nothing Then
MsgBox "Cannot start Outlook.", vbExclamation
Exit Sub
End If
Set outNs = outApp.GetNamespace("MAPI")
Set outFolder = outNs.GetDefaultFolder(olFolderInbox)
If Not outFolder Is Nothing Then
For Each outItem In outFolder.Items
If outItem.Class = Outlook.OlObjectClass.olMail Then
Set outMailItem = outItem
If InStr(1, outMailItem.Subject, subjectFilter) > 0 Then 'removed the quotes around subjectFilter
For Each outAttachment In outMailItem.Attachments
outAttachment.SaveAsFile saveFolder & outAttachment.FileName
Set outAttachment = Nothing
Next
End If
End If
Next
End If
If OutlookOpened Then outApp.Quit
Set outApp = Nothing
End Sub
You can't automatically overwrite it. You have to delete the old one then save the new one. I would suggest moving the kill line to just before the saveas so that you only delete it if you are about to save a new version, and don't use a fixed file name:
For Each outAttachment In outMailItem.Attachments
If dir(saveFolder & outAttachment.FileName) <> "" then kill saveFolder & outAttachment.FileName
outAttachment.SaveAsFile saveFolder & outAttachment.FileName
Next
Velouria,
There is a new file everyday. The same filename is used, as the source file is part of a large template that refreshes with Power Query. There are multiple users, so there is a need to keep the process simple. Plus the file was a manual pull, and we have gotten it automatically emailed to us daily with the help of someone in the Data Science dept. That is a HUGE help, so there is nothing I can do on this one really?
I can't see anything wrong with the revised code (though I'm surprised you're saving it locally given what you describe) - if the file failed to save for any reason, you should be seeing an error message.
One thing I would point out is that your subject line test is case sensitive as written - could that be an issue? With the old code that could have led to the old file being deleted but no new file ever being saved. But if you manually saved the file, you would have had to then run this code again afterwards for the file to be deleted, unless some other process is at work.
Velouria,
First, the subject line is exact, I copied it into the code. Second, I ran the code manually, and it worked without an issue. Do I need to perhaps need to separate the "kill" portion and have that run at the beginning, or even separately, then run the rest of the code?
My suspicion would be that this code is not the cause of the problem. It could only delete the file when you run it, and it will only delete the file if it's about to replace it.
Velouria,
Thanks so much for your insight, it was worth a try!
