Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Rename Excel file from Outlook

  1. #1
    New Member
    Join Date
    Dec 2017
    Location
    West Yorkshire, England
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Rename Excel file from Outlook

    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.

    Code:
    Sub SaveAttachmentsToDisk(itm As Outlook.MailItem)Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim att As Object
    saveFolder = \\server\share  ' 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 "".
    Code:
    objAtt.SaveAsFile saveFolder & "" & Format(Now, "dd-mm-yy-hh-mm-ss") & ".xlsx"
    Last edited by wallen1605; Feb 12th, 2018 at 08:07 AM.

  2. #2
    Board Regular
    Join Date
    Jun 2017
    Posts
    104
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rename Excel file from Outlook

    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!

  3. #3
    New Member
    Join Date
    Dec 2017
    Location
    West Yorkshire, England
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rename Excel file from Outlook

    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

  4. #4
    Board Regular
    Join Date
    Jun 2017
    Posts
    104
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rename Excel file from Outlook

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •