Save before send

[HeKr]

Hello to all,

I've got the following challange:
I have a excel sheet (called XXXX) which people send to me. I have to collect the data of these sheets in 1 big sheet.

The sending part works ok with the code:

Activeworkbook.sendmail Recipients:= "Me@home.com", _
subject:= "XXXX"

All the files I receive have the same filename (XXXXX) so I can't put them in the same folder on my pc without changing manual the name.
What I like to do is make (with your help) a macro that changes the name of the file in XXXX + date + time (this should be rather unique), send this file with the above code, delete the unique filename and return to my original file called XXXX

I hope this is clear enough to give me a hand

BEst regards

Henk KRuizinga

 

[BrianB]

I think this is what you want. If you do not want to keep the file you can use 'Kill Fname' to delete it. Make sure your original file has been saved.

Sub test()
    Dim Fname As String
    Dim MyDate As String
    '--------------------------------------
    '- current date & time
    MyDate = Format(Now, "dd-mm-yy hh-mm")
    '- file name
    Fname = ActiveWorkbook.Name
    Fname = Left(Fname, Len(Fname) - 4) _
        & MyDate & ".xls"
    '- Save As
    ActiveWorkbook.SaveAs FileName:=Fname
    '- send mail
    ActiveWorkbook.SendMail _
        Recipients:="Me@home.com", Subject:="XXXX"
End Sub