PDA

View Full Version : Email Individual Worksheet Rather Than Workbook



golf4
03-21-2006, 10:12 PM
Hi, guys -

:hi: :hi: :hi: YEA - YEA - YEA...... know I've been away for a while, but thought I'd hop back in with both feet...... GREAT TO BE BACK!!!!

I have a quick one for ya here. I continue working on my extensive income calculation spreadsheet for work. There is one worksheet that needs to be send directly to our Accounting people that staff continues to forget about. To resolve this, I'd like to set it up where the user uses a command button to email the individual worksheet directly to a specific Accounting person.

I found the code noted below that will send the workbook, but I'm wondering how I can modify it to send only the individual worksheet? I've tried changing "ActiveWorkbook" to "ActiveWorksheet", but no dice.

Option Explicit
Private Sub CommandButton1_Click()
ActiveWorkbook.SendMail jdoe@xxxxxx.org, "My Worksheet"
End Sub

Any help would be great!!!!

Thanks again for all the help.........

Golf

mdmackillop
03-22-2006, 12:50 AM
Hi Golf,
Make a copy of the sheet to a new book


ActiveSheet.copy
ActiveWorkbook.SendMail Recipients:="jdoe@xxxxxx.com", Subject:="My Worksheet"
ActiveWorkbook.Close False

golf4
03-22-2006, 06:15 PM
Hi, Mac (hope that's ok) -

Thanks so much for the help on this one. While it didn't really work for me at home last night, it did work like a charm here at work. :thumb

I do have one additional follow-up question for you on the same subject. I have included below the code I've developed to to print out 3 copies of the worksheet I referred to in my original posting. Yea - I know... I tend to go nuts with the message boxes and wavs, but they work good as reminders for staff. What I was wondering was how I could incorporate your suggested code into mine? I'd probably want to include it after the third copy was printed off, but not sure of the correct syntax for including yours into mine.... :p


Sub PrintFSSWorksheet() 'Print FSS Escrow Worksheet'
Sheet20.Activate
With Sheet20
.Unprotect "led52not"
ActiveSheet.Shapes("object 8.wav").Select 'oh no msg'
Selection.Verb Verb:=xlPrimary
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut

ActiveSheet.Shapes("object 6.wav").Select 'fss reminder msg'
Selection.Verb Verb:=xlPrimary
MyTimer = Timer
Do
Loop While Timer - MyTimer < 4
paperwarning = MsgBox("I HAVE HEARD THE REMINDER MESSAGE AND " _
& "PROMISE, UPON PENALTY OF SOMETHING REALLY BAD HAPPENING, " _
& "THAT I WILL PASS ON ACCOUNTING'S COPY OF THE FSS WORKSHEET " _
& "TO THE APPROPRIATE STAFF PERSON.", vbOKOnly, "REMEMBER TO " _
& "PROPERTLY DISTRIBUTE COPIES OF FSS WORKSHEETS!!!")
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
End Sub

Thanks again for your help on this one.

Cheers.

Golf