Vilnis Vesma's Excel tip: self-mailing workbook

You can get a workbook to email itself to someone by putting a statement like this in a macro:

ActiveWorkbook.SendMail "a.name@mydomain.com"

You can make this the code behind a 'Mail me' button on the workbook. The SendMail method puts an email message, with the workbook attached, in the outbox of your mail client (Outlook, etc). The mail client program needs to be running for the message to be sent.

Here's an idea: put a SendMail statement in the workbook's 'BeforeClose' procedure. Then every time you close it, it will end up as an attachment to an outgoing email, so you'll always have copies you can recover, if disaster strikes, of every version you ever closed.

The SendMail method can accept two other arguments as well as the recipient: a subject (see next example) and a receipt request. It can't put any text in the body of the message; nor can it specify a Cc: or Bcc:. Citing multiple recipients is done by means of a text array. The code would look something like this:

Public Sub MailMe()
    Dim Names()
    Names = Array("name1@mydomain.com", "name2@mydomain.com")
    ActiveWorkbook.SendMail Names(), "My Subject"
End Sub

Notice also here the inclusion of an explicit subject. If you omit it, as in the first example above, the workbook's name is inserted as the message subject.

It's not just the active workbook you can email, but any workbook:

Workbooks("MYWORKBOOK.XLS").SendMail "a.name@mydomain.com"

Just make sure than you include code to check that the subject workbook is open at the time.

V.V. 13 October, 2005