Saving a single worksheet

[Shawn]

I am trying to ise a command button to save a single worksheet from a
workbook to export to outlook and mail it. So far I have the following which
works fine but mails the whole workbook. My code is not good so details are
appreciated.
Private Sub CommandButton1_Click()
'
'' Email Sheet
' Taken from Dustin's timesheet macro
' 1/28/08
'

Dim Fname
Fname = ActiveSheet.Name

Range("A1:N41").Select
Selection.Copy
'Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWindow.DisplayGridlines = False 'added
Application.CutCopyMode = False 'added
Application.StandardFont = "Tahoma"
Application.StandardFontSize = "10"
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Paste
With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.25)
End With
ChDir "C:\"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True
Application.Dialogs(xlDialogSendMail).Show
End Sub

 

[Jim Thomlinson]

Check out this link...

http://www.rondebruin.nl/sendmail.htm

 

[Ron de Bruin]

Hi Shawn

See
http://www.rondebruin.nl/sendmail.htm

 

[Shawn]

ya'll are awesome...I have been working on this all day!

 

[Shawn]

Ron,
I used the script for Mil one sheet in Outlook object model (attachment). I
am trying to apply this macro to a command button in the sheet. The command
button applies:
Private Sub CommandButton1_Click()
Which is expecting and End command. If I delete that nothing happens when I
try the button. What should I do?
Also, if I use display under the lines for OutMail will it stop at the point
where I will have to push the send button for the message to go?
With OutMail
.To = "(e-mail address removed)"
.CC = "Central Lab"
'.BCC = ""
.Subject = "Central Lab Weekly WIG Update"
.Body = "Weekly WIG Update"
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display

 

[Ron de Bruin]

Use only this line in the click event

Call MacroName

Also, if I use display under the lines for OutMail will it stop at the point
where I will have to push the send button for the message to go?

Correct
Download the example workbook from my site and you will see it

 

[Shawn]

Hate to seem stupid but where do I insert Call MacroName and do I replace
Name with something else?

 

[Ron de Bruin]

If the name of the mail macro in your standard module = MailSingleSheet

Then the click event in the sheet module looks like this

Private Sub CommandButton1_Click()
call MailSingleSheet
End Sub

 

[Shawn]

I changed the button from command button to a tool bar button so it would not
show on the emailed page. Now I need to change from ActiveSheet to a
designated sheet or the button may send the wrong sheet. Also I am trying to
set up an additional button that will mail the first three worksheets in the
workbook. I looked at your instructions for mailing multiple sheets and I
don't understand where I substitute in the macro the sheet names that I need
to be mailed. Help again please!

 

[Ron de Bruin]

http://www.rondebruin.nl/mail/folder1/mail3.htm

Read the info above the macro

This sub will send a newly created workbook with just the sheets in the Array.

Sourcewb.Sheets(Array("Sheet1", "Sheet3")).Copy

 

[Shawn]

Sourcewb.Sheets(Array("Index", "MailSheet(s)")).Copy
In the above line do I substitute the name of my pages in "Index" and leave
Mailsheet(s) in. Also do I use the name of my sheets or Sheet1,Sheet2..etc.
I can't seem to get it to work on this one

 

[Ron de Bruin]

You use the names of the sheets you want to send

 

[Shawn]

What is the real difference between the macro for sending multiple pages with
the SendMail method compared to the ones for sending multiples using Outlook
object model. I can't really tell the difference and maybe that is why I
cannot get it to work on my end.

 

[Ron de Bruin]

Above the SendMail macro you can read it

Note: With SendMail it is not possible to
1) Send text in the Body of the mail
2) Use the CC or BCC field
3) Attach other files

Send me the file private and tell me which sheets you want to mail

 

[Shawn]

I have sent it to the email address on your website! Thanks again!

 

[Ron de Bruin]

I send you the file back with a example