Use command button to send email
 

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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

That didn't work for me on a form, presumably because the form has focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

In contrast, it worked flawlessly for me and the form did have the focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

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


"Tom Ogilvy" wrote in message
...
In contrast, it worked flawlessly for me and the form did have the focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has
focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

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.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
In contrast, it worked flawlessly for me and the form did have the
focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has
focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

Hi Tom/Bob

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

Possible problems are
http://www.rondebruin.nl/mail/problems.htm

Number 2 I think


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
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.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
In contrast, it worked flawlessly for me and the form did have the
focus.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
That didn't work for me on a form, presumably because the form has
focus?

Bob


"Tom Ogilvy" wrote in message
...
Private Sub Commandbutton1_click()
Mail_ActiveSheet
End Sub

--
Regards,
Tom Ogilvy


"Slugger" wrote in message
...
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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks!

Use command button to send email
 

"Tom Ogilvy" wrote in message
...
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.