Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coding required to rename file then send (2003)

    Is is possible to write some VBA put it in macro so that it can rename an excel file to todays date and then send the file automatically?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Coding required to rename file then send (03)

    Are you planning to run this code inside Outlook? If so, you might want to copy the Excel file to a temp folder, then rename it, then attach it to the message, then send the message. I think you have a separate message about the timing on this, as I don't know how to do scheduling inside Office applications.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Coding required to rename file then send (200

    Depending on what you use now as a file name, it may be simplest to copy the file to one of the same name appended by the date and time (for your record of when it was sent) and send the copy. That is easy to do in Excel, or (if you use Outlook, which doesn't have file manipulation capabilities within its Object Model) by VB using FileCopy.
    -John ... I float in liquid gardens
    UTC -7±DS

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding required to rename file then send (200

    Hello,

    I have not got a clue as to how to go about doing this?
    The filename will always need to have the date at the end of it when it is sent. i.e test26/11/07 or test261107
    If you could send me some code to test this so that file is renamed with date onto it then sent automatically that would be great.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Coding required to rename file then send (200

    <P ID="edit" class=small>(Edited by JohnBF on 26-Nov-07 08:10. )</P>> If you could send me some code to test this so that file is renamed with date onto it then sent automatically that would be great.

    You still have not yet advised whether you want to run this code from Excel & Access or Outlook, and Loungers do not know what your file paths are or pretty much anything else about what you are doing. You will need to learn how to use any code examples you get to make them work.

    To copy a file and append the date, the code would be something like:

    Dim strFileName as String
    Dim strFolder as String ' must end with ""
    FileCopy strFolder & strFileName, strFolder & Left(strFileName, Len(strFileName) - 4) & Format(Date, "yyyy-mm-dd") & Right(strFileName, 4)
    -John ... I float in liquid gardens
    UTC -7±DS

  6. #6
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding required to rename file then send (200

    My aplogies for not be specific, l am not to sure as to which would be best from outlook or Excel.
    If l need to rename the excel file then send via outlook every day at a specified time automatically, maybe outlook is best.
    Would the coding work in outlook?

    The file will always be in the area :

    G:AccountsReadspaceP9 SLA's

    If the file is called IDND.xls, is that “strfilename” value, also is strfolder “G:AccountsReadspaceP9 SLA's” as this is where file is located?

    Dim strFileName as String
    Dim strFolder as String ' must end with "/"
    FileCopy strFolder & strFileName, strFolder & strFileName & Format(Date,"yyyy-mm-dd")

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Coding required to rename file then send (200

    Your request can't be done purely by an Outlook VBA procedure - see <!mskb=239087>Microsoft Knowledge Base Article 239087<!/mskb> and the outlookcode.com report schedule page. It would actually be better done from Excel, which is a whole different tack.

    Using the MSKB approach the following changes to the Form code sample should work:

    Sub Item_PropertyChange(ByVal Name)
    Const strFolderLoc As String = "G:AccountsReadspaceP9 SLA's"
    Const strFileName As String = "IDND.xls"
    Dim strNewFileName As String

    strNewFileName = Left(strFileName, Len(strFileName) - 4) & Format(Date, "yyyy-mm-dd") & Right(strFileName, 4)
    FileCopy strFolder & strFileName, strFolder & strNewFileName

    Select Case Name
    Case "Status"
    If Item.Status = 2 Then '2 = Completed
    Set NewItem = Application.CreateItem(0)
    NewItem.To = "myemailaddress@myisp.com"
    NewItem.Recipients.ResolveAll
    NewItem.Subject = "This is the message subject text"
    NewItem.Body = "This is text that will appear in the body of the message."
    NewItem.Attachments.Add strFolder & strNewFileName, olByValue
    NewItem.Display
    End If
    End Select
    End Sub

    Using the outlookcode.com approach, change the Form code to:

    Function Item_Open()
    Const strFolderLoc As String = "G:AccountsReadspaceP9 SLA's"
    Const strFileName As String = "IDND.xls"
    Dim strNewFileName As String

    strNewFileName = Left(strFileName, Len(strFileName) - 4) & Format(Date, "yyyy-mm-dd") & Right(strFileName, 4)
    FileCopy strFolder & strFileName, strFolder & strNewFileName

    If Item.Size = 0 Then
    Item.Attachments.Add strFolder & strNewFileName, olByValue
    Item.Send
    End If
    End Function

    These code changes are untested - post back the details if the code errors out.
    -John ... I float in liquid gardens
    UTC -7±DS

  8. #8
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding required to rename file then send (200

    Hello,

    Many thanks for that, l will put into Excel

    lf wanted to send this file at a specified time what coding would l need to add?
    Once l have that coding l will then try out and get back to you.

Posting Permissions

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