Command Button VBA Problem

[Ryan H ARENA]

I'm attempting to create a command button that will send the current sheet to someone as an email, but when I click the button nothing happens. Here is the code, and I'm not sure if I assigned it properly. Excel 2010. Thanks for the help.

Sub Send_Range()
ActiveSheet.Range("A1:P1000").Select
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "Daily Production Goal spreadsheet"
.Item.To = "aaa.bbb@ccc"
.Item.Subject = "Today's Production Goal"
.Item.Send
End With
End Sub

 

[Fluff]

Hi & welcome to the board.
Firstly, I removed your email address from your post, as Spam Bots routinely troll Public User Forums like these looking for email addresses to Spam.
Secondly what type of command button are you using?

 

[Ryan H ARENA]

Just a heads up that email was a fake I made up. The command button I'm using is an ActiveX Control Button. I just want the user of the spreadsheet to be able to click the button or on a cell or something and then it'll email the current spreadsheet to whoever is specified in the code

 

[Fluff]

On the developer tab select Design mode, then double click the button.The VBE will open up & you'll see something like

Private Sub CommandButton1_Click()

End Sub

Simply copy your code & paste it between those 2 lines

 

[Ryan H ARENA]

It's giving me a compile error that says expected end sub when I paste the code between those lines.

Private Sub CommandButton1_Click()
Sub Send_Range()
 ActiveSheet.Range("A1:P1000").Select
 ActiveWorkbook.EnvelopeVisible = True
 With ActiveSheet.MailEnvelope
 .Introduction = "Daily Production Goal spreadsheet"
 .Item.To = "aaa.bbb@ccc"
 .Item.Subject = "Today's Production Goal"
 .Item.Send
 End With
 End Sub
End Sub

 

[Fluff]

Remove these 2 lines

Private Sub CommandButton1_Click()
[COLOR=#ff0000]Sub Send_Range()[/COLOR]
 ActiveSheet.Range("A1:P1000").Select
 ActiveWorkbook.EnvelopeVisible = True
 With ActiveSheet.MailEnvelope
 .Introduction = "Daily Production Goal spreadsheet"
 .Item.To = "aaa.bbb@ccc"
 .Item.Subject = "Today's Production Goal"
 .Item.Send
 End With
 [COLOR=#ff0000]End Sub[/COLOR]
End Sub

 

[Ryan H ARENA]

It turns that the initial code I had isn't quite what I'm looking for. Is there a way I can attach the current worksheet as an attachment, because I'd like the recipient to be able to open only the current sheet. The way it is now it sends the selected cells as an image and it can't be opened in excel.

 

[Ryan H ARENA]

^^Also thank you for the help

 

[Fluff]

As I use web based email, I cannot help.
The best place to look for all things email is https://www.rondebruin.nl/win/section1.htm