Save Outlook attachment in network folder and rename to current date and time

Status
Not open for further replies.

wallen1605

Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server 2013
Hi All

I am new to the forum and need help please with a piece of code which will achieve what the title says above.

I currently have the below code in a module called by an outlook rule to run on incoming emails. All incoming attachments are .xlsx attachments and are renamed with the same file extension. The issue I have is that once the vb code renames the file name to the current date and time and saves the file in the network folder, I can no longer open the file and receive the error that the file format or extension may not be correct or the file is corrupt, but the file is fine before renaming.

Also the code below causes confusion on emails which have more than 1 attachment, so ideally I need the code to work through all attachments in the incoming email.

Code as follows: (Many thanks in advance for your help):

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
 
Also the code below causes confusion on emails which have more than 1 attachment, so ideally I need the code to work through all attachments in the incoming email.
In what way does it cause confusion?

Try using
if Right(Att.DisplayName, 5) = ".xlsx" Then
 
Hi Diane, many thanks for your reply.

If there are multiple attachments on one receiving email, the code will create multiple instances of the file, so if there were originally 2 attachments, the code appears to save 4, so in affect doubling up?

I will try your suggestion and post back. Is there any reason the excel files are corrupting when the code renames the file?
 
Where would I be best inserting the code snippet you suggest above please?
 
Sorry diane just realised I need to replace the code line referencing 'Like ".xlsx", with your code.
 
I think this is the problem -
saveFolder = "\\server\share" ' change to your path

Test it using a local path. Saving to network shares has been problematic - it can help if the share is mapped.
 
Sorry diane just realised I need to replace the code line referencing 'Like ".xlsx", with your code.
I don't think that is the problem though - it worked fine using like when i ran it with a local folder path. (I had assumed the confusion was due to it trying to save all of the attachments, not just the xlsx).
 
If there are multiple attachments on one receiving email, the code will create multiple instances of the file, so if there were originally 2 attachments, the code appears to save 4, so in affect doubling up?
Hmmm. I'm not seeing that, at least not with multiple attachments, of which only one is xlsx.

This should properly handle multiple files:
If itm.Attachments.Count > 0 Then
For Each att In itm.Attachments

BTW, the only thing I would change is putting the \ in the save folder path instead of adding it later:
saveFolder = "\\server\share\" ' change to your path

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


Do the files you save to the network ever open ok later?
 
Okay I think I am getting somewhere. Now including your changes, there are 2 copies of the workbook saving to the network folder, one of them is 1kb in size (this one won`t open, corruption message as before), but the second one opens perfectly and shows the a more normal file size of 13kb.

Is there any way to stop the second corrupt file coming in? Is this possibly the temp file being renamed and is also saving as well as the actual file?
 
Ok... i can repro it with 2 xlxs files. I made a couple of change for testing so i could see what it was doing. its getting the names correct but saving the one file under both names.

Code:
For Each att In itm.Attachments
Debug.Print att.FileName
                If att.FileName Like "*.xlsx" Then

                objAtt.SaveAsFile saveFolder & att.FileName & Format(Now, "dd-mm-yy-hh-mm-ss") & ".xlsx"
  
                itm.UnRead = False
  
                End If
 
Is there any way to stop the second corrupt file coming in? Is this possibly the temp file being renamed and is also saving as well as the actual file?
Yeah, its something like that, but i'm seeing different weirdness too.

There is a KB floating around somewhere that explains it, but i couldn't find it in a quickie search.
 
Yeah this is a strange one. I will keep looking to resolve, otherwise I may have to try the mapped folder route.

Many thanks for your help Diane, I have seen your name all over google searches for outlook help, its great to have help from you. Thanks again
 
Sheesh... I'm blind as a bat. :) its the object names.

Dim objAtt As Outlook.Attachment
For Each objAtt In itm.Attachments

For Each objAtt In itm.Attachments

If itm.Attachments.Count > 0 Then

For Each att In itm.Attachments



change the att's in the code to objAtt
 
this is working here -

Code:
Sub SaveAttachmentsToDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String

saveFolder = "C:\Users\slipstick\Documents\"  ' change to your path
If itm.Attachments.Count > 0 Then
For Each objAtt In itm.Attachments
If Right(objAtt.DisplayName, 5) = ".xlsx" Then
  objAtt.SaveAsFile saveFolder & objAtt.DisplayName & Format(Now, "dd-mm-yy-hh-mm-ss") & ".xlsx"
  
    itm.UnRead = False
End If
Next
End If

End Sub
 
what was happening in your code - the first line ran through each attachment - then got the att count then saved... then went back to the objAtt and reran the code. So 2 attachments would loop 4 times.

For Each objAtt In itm.Attachments

If itm.Attachments.Count > 0 Then

For Each att In itm.Attachments
 
Hi Diane, thank you so much, this worked perfectly, thanks for your time :)
 
Hi Diane,

I am sorry if I need to post a new question on this, but you kindly provided me with the code in the above message thread to save the excel attachment of incoming emails to a network path which works great to this day (used daily), however I need to adapt this outlook code to open this saved excel document, then add a new worksheet before Sheets(1) named 'Email' and then add the email fields of 'From' address, 'Subject' and mail body into cells A1, A2, A3 respectfully and auto fit to portrait A4 width size (210mm) and wrap the text.
I understand the outlook code cannot open the attachment directly, but it can open it once stored on the network share, then insert the email fields mentioned above and save, then close the excel document on the server?
I have looked at many options and examples but I am lost. Please could you help?

Many thanks in advance.
 
are you saving the workbook then editing it? See Save and Open an Attachment using VBA

For adding a sheet and doing the other stuff, record a macro in excel then copy and paste it in outlook's VBA editor, tweaking it to work from outlook.
 
are you saving the workbook then editing it? See Save and Open an Attachment using VBA

For adding a sheet and doing the other stuff, record a macro in excel then copy and paste it in outlook's VBA editor, tweaking it to work from outlook.
Hi Diane, yes the outlook code is save the file, i need to edit the excel file by opening and editing from outlook.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
9 Outlook 2016 How to save an Outlook attachment to a specific folder then delete the email it came from? Using Outlook 1
C How To Open Outlook Attachment in Excel, Modify some Data, then Re-Save It Using Outlook 3
S save attachment using outlook 2007 Outlook VBA and Custom Forms 1
G Save emails as msg file from Outlook Web AddIn (Office JS) Outlook VBA and Custom Forms 0
M Outlook Macro to save as Email with a file name format : Date_Timestamp_Sender initial_Email subject Outlook VBA and Custom Forms 0
C Outlook 365 Copy/Save Emails in Folder Outside Outlook to Show Date Sender Recipient Subject in Header Using Outlook 0
C Outlook (desktop app for Microsoft365) restarts every time I save my VBA? Using Outlook 1
G Save and Rename Outlook Email Attachments Outlook VBA and Custom Forms 0
G VBA to save selected Outlook msg with new name in selected network Windows folder Outlook VBA and Custom Forms 1
D Outlook 2016 64bit, Cannot Save in 'HTML', format Using Outlook 1
S Outlook (2016 32bit; Gmail IMAP) - Save sent message to Outllook Folder Outlook VBA and Custom Forms 0
P Outlook pst file is too huge with POP3. How to save more space? Using Outlook 4
W Outlook Calendar does not save view any longer! Using Outlook 3
W Save and rename outlook email attachments to include domain name & date received Outlook VBA and Custom Forms 4
C Outlook - cannot save subject line changes Using Outlook 2
I Outlook 2016 64bit - on receipt convert emails into PDF and save Outlook VBA and Custom Forms 2
C Auto save outlook attachments when email is received Outlook VBA and Custom Forms 1
C Need VBA code to automatically save message outside outlook and add date Outlook VBA and Custom Forms 1
Diane Poremsky Export (Save) Outlook Contact photos Using Outlook 0
E Outlook 2016 and Numerous Prompts to Save Emails Using Outlook 3
Diane Poremsky Save Outlook Email as a PDF Using Outlook 0
Diane Poremsky Edit and Save Outlook's Read-Only Attachments Using Outlook 0
S using script rule to save attachments on arrival Outlook 2010 Outlook VBA and Custom Forms 9
L Save message from outlook to desktop in 2013 outlook Outlook VBA and Custom Forms 1
Mark Foley Where are Outlook categories save for IMAP? Using Outlook 12
R Outlook 2013 VB rule to auto save attachments with different file types Outlook VBA and Custom Forms 5
C Save outlook attachments and rename/append files with identifier from subject line Outlook VBA and Custom Forms 3
J Auto-Save for Outlook Calendar Entries Using Outlook 5
L Outlook 2007 Macro Save Contact Using Outlook 10
B Outlook 2007 Save Current View,Font,Toolbar Settings Using Outlook 6
E Outlook prompts to save attachments when no changes have been made. Using Outlook 0
F Outlook 2010 custom font style duplicates itself every time I save a task Using Outlook 0
A File - Save Attachments does nothing in Outlook 2003 with Exchange 2010 ... Using Outlook 3
B Outlook 2010 won't save 'current view' with reading pane at 'bottom' Using Outlook 4
O Export (save) Outlook Contact photos Using Outlook 2
E can't save draft email in Outlook 2010 Using Outlook 2
F CAN'T SAVE OUTLOOK 2010 E-MAIL TEMPLATE (.oft) Using Outlook 1
S Save Attachement from outlook Using Outlook 2
Rupert Dragwater Can't save font type in Outlook 2010 Using Outlook 3
O closing outlook prompts to save all opened attachments Using Outlook 2
S Outlook 2010: Cannot save forms templates to folder list Using Outlook 4
M Outlook 2007 - Save outgoing attachments to network drive on sending? Using Outlook 1
D outlook to save copy of hotmail? Using Outlook 3
E Strange save request when closing Outlook 2007 Outlook VBA and Custom Forms 4
E Strange save request when closing Outlook 2007 Outlook VBA and Custom Forms 4
H Where does Outlook 2007 save the default reminder settings? Outlook VBA and Custom Forms 1
E Outlook 365 Save Selected Email Message as .msg File - oMail.Delete not working when SEARCH Outlook VBA and Custom Forms 0
E Save Selected Email Message as .msg File - digitally sign email doesn't works Outlook VBA and Custom Forms 1
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1
D VBA Macro to Print and Save email to network location Outlook VBA and Custom Forms 1

Similar threads

Back
Top