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 "[email protected]", _
"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.

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("[email protected]",[email protected])

as an example.

--
Regards,
Tom Ogilvy

"Telecommm" <[email protected]> wrote in message
news:[email protected]...
> 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 "[email protected]", _
> "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.

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 Array("[email protected]",[email protected])
>
> as an example.
>
> --
> Regards,
> Tom Ogilvy
>
> "Telecommm" <[email protected]> wrote in message
> news:[email protected]...
> > 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 "[email protected]", _
> > "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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 Array("[email protected]",[email protected])
> >
> > as an example.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Telecommm" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 "[email protected]", _
> > > "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.
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Array("[email protected]",[email protected])
> > >
> > > as an example.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Telecommm" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 "[email protected]", _
> > > > "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.
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Array("[email protected]",[email protected])
> > > >
> > > > as an example.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Telecommm" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 "[email protected]", _
> > > > > "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.
> > > >
> > > >
> > > >
> >
> >
> >