Changing Filename created when emailling

    I'm using the code below to automatically select a single worksheet and start my email programme. Is it book to get Excel to give a name to the newly created file instead of Book2, Book3 etc? (I'd like it to be called "Quotation" each time so that this appears in the Subject field of the email). I'm very new to VB in Excel, so appreciate your advice.


    Thanks for your help


    Sub Email()
    '
    Dim SBookName
    ActiveSheet.Copy
    Application.Dialogs(xlDialogSendMail).Show
    ActiveWorkbook.Close savechanges:=False
    '
    End Sub

    prior to generating your mail dialog box use


    Code
    ActiveWorkbook.SaveAs "C:\myfilename.xls"


    Depending on what you are doing in this routine you might want to have a look at the SendMail Method in VBA (useful for a simple mailing routine where no message body is required - say just a subject)


    eg


    Code
    ActiveWorkbook.SaveAs "C:\myfilename.xls"
ActiveWorkbook.SendMail Recipients:"[email protected]", Subject="Your Subject"
ActiveWorkbook.Close


    If this is a repeated task from within another module in another file you may want to turn off warnings about saving over an existing file (if you always save the new file with the same name) by using


    Application.DisplayAlerts = False


    prior to saving the file


    and then restoring the alerts once the file has been closed


    Application.DisplayAlerts = True

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login