Use command button to send email

S

Slugger

Hello-
How do I incorporate the following Ron Debruin code to be triggered with a
command button on a user form?

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!
 
B

Bob Phillips

How odd. Which Excel version do you have? I am running on 2000. It failed on
the SendMail command, and so I assumed that was the problem the OP
encountered.


Bob
 
T

Tom Ogilvy

Does it run when not executed from a userform? Sounds like your mail
program isn't registered to work with Sendmail.

the original poster didn't state that they were having any problem with the
code. Simply that they wanted to execute it from a commandbutton on a
userform. If it was working for them before, it should work with what I
suggested.

xl2003, but I wouln't expect that to be a consideration for this code.
 
B

Bob Phillips

Tom Ogilvy said:
Does it run when not executed from a userform? Sounds like your mail
program isn't registered to work with Sendmail.

Although it is n ot something that I ever do, I can send mail from Excel
using Sen dTo. Is SendMail something different (I had assumed not)?
the original poster didn't state that they were having any problem with the
code. Simply that they wanted to execute it from a commandbutton on a
userform.

No, but it is such a simple next step, I thought the OP had taken it.

xl2003, but I wouln't expect that to be a consideration for this code.

Nor I, but all considerations.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top