Email macro

[wilkisa]

My user has the following macro:

Sub Email_1st()
' Clear out any old data on Report

Application.Dialogs(xlDialogSendMail).Show _
arg1:=Array("some.user@company.com"), _
arg2:="Certificate of Appreciation"
ActiveWorkbook.Close

Call Print_Certificate
End Sub


He wants to send only the active sheet instead of the whole workbook. Can someone help me modify this code?

Thanks,
Shirlene

 

[VoG]

Maybe you can adapt this http://www.rondebruin.nl/mail/folder1/mail2.htm

 

[wilkisa]

Yes, VOG, thanks. I had already looked at that and it just confuses me even more. Can you guide me?

 

[VoG]

Yes, on reflection it is a bit over complicated for what you want.

Does this work?

Sub Email_1st()
' Clear out any old data on Report
ActiveSheet.Copy
Application.Dialogs(xlDialogSendMail).Show _
arg1:=Array("some.user@company.com"), _
arg2:="Certificate of Appreciation"
ActiveWorkbook.Close False

Call Print_Certificate
End Sub

 

[wilkisa]

Yes, that will give me just the sheet I want. Now I'll try to get it to give me just the print area and change the sheet name from Book1 so if I get stuck, I'll write back.

Thanks so much. Ron's site is a wealth of information but I sometimes get overwhelmed by it.