ActiveSheet.SaveAs

[Guest]

Hi Champs,

I have written the following code:

Sub Mail_ActiveSheet()
Dim strDate As String
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveSheet.SaveAs " KPI Certificate " & ActiveSheet.Range("D8").Value _
& " " & strDate & ".xls"

ActiveWorkbook.SendMail "(e-mail address removed)", _
"KPI Certificate / North /" &
ActiveSheet.Range("D8").Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub


the code generates a new Workbook and copies the current sheet into new
Excel Workbook which is exactly what I want but at the same time it copies my
Macros in new Workbook too. Is it possible that i can set cell range to be
copied to the new workbook and then Saveas this sheet for email purpose.
Secondly, can I send new work book to more than one person.

I wish you all Happy Xmas and Thank you so much for the kind help.

 

[Tom Ogilvy]

Create a new workbook and copy your data to the new workbook, then send
that.

or do
ActiveSheet.copy if there is not code in the sheet module.

You can use an array of addresses with sendmail

Activeworkbook.Sendmail Array("(e-mail address removed)",[email protected])

as an example.

 

[Guest]

Thank you Sir,
I have resolved the multi recepients email issue. But could not overcome the
other problem. Will you please give me exact lines which I have to add in my
excel macro. I want to copy the A1:G50, save it into a new excel workbook
and email to 5 recepients.
Warm Greetings.

 

[Tom Ogilvy]

Dim rng as Range, sh as Worksheet
Set rng = Activesheet.Range("A1:G50")
Workbooks.Add Template:=xlWBATWorksheet
set sh = ActiveSheet
rng.copy
sh.Range("A1").PasteSpecial xlValues
sh.Range("A1").PasteSpecial xlFormats
sh.Parent.Sendmail . . .
sh.Parent.Close SaveChanges:=False

 

[Guest]

Issue is resolved.
Thanks for your replies.
Regards,
Junaid

 

[Guest]

Sir,
my last words were misleading.... infact I resolved the issue with your help
not at my own. Thanks again.