+ Reply to Thread
Results 1 to 20 of 20

Outlook VBA save file as with a new name

  1. #1
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Outlook VBA save file as with a new name

    Currently macro saves a file with its existing name into a folder ABC. Wish to change the file name to current date. Macro is run every day so in the folder ABC there should be a new file saved every day with date as it's name.

    Current VBA code:
    "Sub X (item As Outlook.MailItem)

    Dim SaveFolder As String
    Dim Y As Outlook.Attachment


    SaveFolder = "D:\ABC"

    For Each Y In item.Attachments

    Y.SaveAsFile SaveFolder & "\" & Y.DisplayName
    Set Y= Nothing
    Next

    End Sub"

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    To just save the attachment with today's date:
    Please Login or Register  to view this content.
    Or if you want the current name appended with today's date:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    Thanks ORoos it works! But in addition I would like it to save it as a .csv extension, how can i do that?

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Try:

    Y.SaveAsFile SaveFolder & "\" & Y.DisplayName & Format(Now(), "dd-mm-yyyy"), FileFormat:= xlCSV

    You may get prompts. To ignore them, add: Application.DisplayAlerts = False right before you do the SaveAs and add
    Application.DisplayAlerts = True right afterward. That suppresses the overwrite message and automatically saves over the old file.

  5. #5
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    Thanks again! Testing the new solution. Another issue, one email contains 2 files, how to stop them from overwriting one another? Files saved from the same email should be saved as "09-06-2020 (1)" and so on.

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    You could:
    save the files with the filename and date (assuming the file names are different) see reply #2 (simplest solution)
    save the date and time format including seconds (this will reduce the risk of overwriting)
    use a loop to check if the file already exits, then use a counter if file exists an incremental number as suggested by yourself.
    Give me a few minutes on that...

  7. #7
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Below is a bit of code I use.
    There are a number of variables you will need to declare.
    I also have different barts of the file path, name and extension broken down in blocks as I have a number of variables on how the file is the be saved (different names for different attachments..). You will need to adjust them for your specific requirements.
    Trust this helps.



    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    ORoos take your time, it is not time critical. I highly appreciate your help!

    The thing is that once these files have been saved to the folder they will be read by a macro in excel workbook. The attachments of the email contain time series data for single days which will all be extracted to one workbook where a continuous time series will be created. So the files in the folder must be different and with names that are predictable for the macro to be able to open them (such as dates as the original name contains the exact time when the 3rd party who generates this file - this is unpredictable).

  9. #9
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Maybe we crossed here. Let me know how you go with replay #7.

  10. #10
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    Okay so the .csv extension saving does not work.
    Attachment 681680
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Try:
    Y.SaveAsFile SaveFolder & "\" & Format(Now(), "dd-mm-yyyy") & ".csv"

  12. #12
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    Something doesn't work... Attachment 681694

  13. #13
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    This is the VBA error. Attachment 681695

  14. #14
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    So the .csv thing works but the DisplayAlert thing does not work somewhy.

  15. #15
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    What file format is the original email attachment that you want to save as .csv

    What do you mean with the DisplayAlert does not work someway. What do you expect and what happen?

    If you try to save an excel file with multiple sheets, or formatting etc. as a .csv file, you get an alert that you may lose some if the data or formatting etc.. IF you add the command, DisplayAlert = False, we basically ignore this and save it as .csv anyway.
    You may don't need them. I just run my code and extracted a couple of .xlsm files from an email and saved them as .csv without any problem. When opening the .csv files I them got a message that the file and extension don't match, but the file is opening and all .xslm functionalities are still there, including a simple macro which is still running fine.

  16. #16
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    The excel file saves as a .csv perfectly. The problem is that if there are 2 emails sent on the same day then there already is a .csv file in D:\ABC. The file will be saved as "9-6-2020 (corrupted copy)" or something like that. I need it to save as "9-6-2020 (1)" when there already is a file with the same name.

  17. #17
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    Let's clarify again:
    Every day I receive 2 emails with .csv attachments with random names. They are data with hourly time series for the same day. I need to save the .csv files with new names: first attachment (current date "dd-mm-yyyy") and the second attachment the same name but with (1) "dd-mm-yyyy (1)". Files go to the same folder from where a excel macro opens them and imports all time series to form one continuous time series.

    Currently I have a working excel macro and outlook macro that is capable of saving the file with a new name "dd-mm-yyyy".

    I now need to add the (1) to the second file so it would not overwrite/create a corrupted copy of the first attachment.

    Outlook VBA code:

    Sub X (item As Outlook.MailItem)

    Dim SaveFolder As String
    Dim Y As Outlook.Attachment


    SaveFolder = "D:\ABC"
    For Each Y In item.Attachments
    Y.SaveAsFile SaveFolder & "\" & Format(Now(), "dd-mm-yyyy"), FileFormat:=6


    Set Y = Nothing
    Next

    End Sub

  18. #18
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Did you have a look at reply #7

    There is my code that does exactly that, appending numbers to the file name to prevent overwriting.

  19. #19
    Registered User
    Join Date
    06-08-2020
    Location
    Estonia
    MS-Off Ver
    Office 365 Business
    Posts
    11

    Re: Outlook VBA save file as with a new name

    YES!!!! Thank you ORoos your #7 reply helped me and it works! :D Now I can proceed to writing the excel vba code.

  20. #20
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    371

    Re: Outlook VBA save file as with a new name

    Great to hear. If your query is answered, please mark this thread as Solved.
    Cheers..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2018, 01:48 PM
  2. [SOLVED] Save attachments to a file outside of Outlook
    By cmorten82 in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2018, 08:06 PM
  3. [SOLVED] Macro to save a Workbook and then e-mail that file from Outlook
    By Toby019# in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2017, 09:46 PM
  4. Save outlook attachment as file using email subject as file name w/o invalid characters
    By kristinlarmer in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 10-16-2015, 05:07 PM
  5. [SOLVED] Save Outlook Attachements based on Date & file/Subject name
    By Naveed Raza in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2015, 11:35 PM
  6. NEEDED HELP: Need to save a file from outlook
    By Sandy008 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2012, 02:18 PM
  7. Save outlook message after it is sent as file in a folder and link to it
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-18-2012, 05:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1