Rename Excel file from Outlook

[wallen1605]

Hi Guys

I know technically this is a question for Outlook, however the issue is with the Excel file I am trying to open.

Basically, I have a rule in outlook that checks incoming emails for an attachment and if found, it renames and saves the attachment to a network folder with todays date and time. All the attachments are successfully renaming and saving, however, when I open each of the files, I receive an error stating the file is either corrupt or the file extension is not valid. I have tested the incoming files before renaming and they open fine, it is only when the code renames them that I cannot open them?

My code example is below, please could someone help me out as I have exhausted the web for the correct answer and cannot find it.

Sub SaveAttachmentsToDisk(itm As Outlook.MailItem)Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim att As Object
saveFolder = [URL="file://\\server\share"]\\server\share[/URL]  ' change to your path
For Each objAtt In itm.Attachments
        If itm.Attachments.Count > 0 Then
        
            For Each att In itm.Attachments
            
                If att.FileName Like "*.xlsx" Then
                objAtt.SaveAsFile saveFolder & "" & Format(Now, "dd-mm-yy-hh-mm-ss") & ".xlsx"
    
                itm.UnRead = False
    
                End If
    
            Next att
    
        End If
Next
End Sub

EDIT: Backslash is in my code but not showing in the following line when I post to the forum between the "".

objAtt.SaveAsFile saveFolder & "" & Format(Now, "dd-mm-yy-hh-mm-ss") & ".xlsx"

 

[RobertSF]

Darn, your code works for me. I even tried it both ways, with "\\server\path" and "H:\path," and it worked both ways. I'd suggest trying with other types of files to see if the problem is Outlook or Excel.

While it's not the cause of the problem, I'll point out a logic error in your code. There are two loops through itm.Attachments, one with objAtt and the other with att. Also, the if statement between the two loops will always be true. You just need the inner loop, and I think (didn't try) you can get away without the if test because if the item has no attachments, the loop will fall through.

Good luck. Anything to do with VBA can be maddening!

 

[wallen1605]

Many thanks for your answer, it appears the double loop was the issue, because it was actually saving the 'temp' file (file to be renamed) and then saving it before the actual file is saved, so it was the two loops causing the issue. I know this because the save file was 1kb in size when the actual file should have been 13kb in the test I did.

Modified the code with only 1 loop, and the issue is now resolved.

Thanks for your help, another lesson for me in VBA, always double check my loops

 

[RobertSF]

You're welcome, and that's fantastic! I'm glad the problem was solved.