ActiveSheet.SaveAs
 

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 ", _
"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.

ActiveSheet.SaveAs
 

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 )

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" wrote in message
...
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 ", _
"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.

ActiveSheet.SaveAs
 

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" wrote:

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 )

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" wrote in message
...
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 ", _
"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.

ActiveSheet.SaveAs
 

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

--
Regards,
Tom Ogilvy


"Telecommm" wrote in message
...
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" wrote:

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 )

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" wrote in message
...
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 ", _
"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.

ActiveSheet.SaveAs
 

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Telecommm" wrote in message
...
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" wrote:

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 )

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" wrote in message
...
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 ", _
"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.

ActiveSheet.SaveAs
 

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

"Telecommm" wrote:

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Telecommm" wrote in message
...
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" wrote:

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 )

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" wrote in message
...
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 ", _
"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.