+ Reply to Thread
Results 1 to 2 of 2

Extracting attachments and rename file names

Hybrid View

  1. #1
    Registered User
    Join Date
    02-08-2005
    Location
    Kent, UK
    MS-Off Ver
    2010
    Posts
    69

    Question Extracting attachments and rename file names

    We received about 15 reports from various sources each day to our reporting mailbox. I have created Outlook VBA scripts, which are run from a message rule, to save each report from the emails to the relevant network folder (for different projects). I have a few questions on extracting and renaming the attachments:

    1. How can I extract a file from an attachment with VBA to a specific folder? Example name is "report_20170831070501.zip"

    2. How can I rename the file to remove the last 2 characters? Example name is report_20170831070501.csv - so would like to remove the "01", as in the seconds.

    3. How can I rename the file of another report to remove the timestamp at the end of the name but keep the datestamp? Timestamp in hh-mm-ss format, example name of report is "This Report_2017-08-31-05-41-56.xlsx".

    I normally use Name "this name", "to this name" to rename files and I guess it's something like left(oFile.name,2) to remove the ends but I can't figure out how to put these together to rename and remove the ends.

    If you need any further info from me, just shout.

    Ta.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Extracting attachments and rename file names

    I don't have time to go in to how to download an attachment, but here is a function that will do what you want.
    Function ReNameFile(FileName As String, NumChar As Long) As String
    ' FileName is the file name
    ' Numchar is the number of characters to remove from the end
    
    Dim StrArray() As String
    
    StrArray = Split(FileName, ".")
    
    StrArray(0) = Left(StrArray(0), Len(StrArray(0)) - NumChar)
    
    ReNameFile = Join(StrArray, ".")
    End Function
    
    Sub Test1()
    MsgBox ReNameFile("report_20170831070501.csv", 2)
    End Sub
    
    Sub Test2()
    MsgBox ReNameFile("This Report_2017-08-31-05-41-56.xlsx", 9)
    End Sub
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. Save outlook attachments and rename/append files with identifier from subject line
    By XLnExcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2015, 12:49 PM
  2. [SOLVED] Attachments : Save and Rename using Sublect string or File name string
    By checksamir in forum Outlook Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2014, 01:13 AM
  3. Extracting Attachments from outlook based on subject title /file name and between a date
    By himynameisiain in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2013, 02:14 PM
  4. Extracting information from file names
    By lovi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2012, 07:44 AM
  5. Rename File Names & Repost to Designated Folder
    By Surfmaster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2012, 01:45 PM
  6. Extract and rename batch attachments from saved outlook files on desktop.
    By entice in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2011, 11:49 PM
  7. Extracting File Names and Properties
    By Albacore33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2005, 12:06 PM

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