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
    ActiveWorkbook.Close savechanges:=False
    End Sub

  • prior to generating your mail dialog box use

    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)


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

    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!