I am trying to code a button to create an email with the active
sheet as an attachment. Due to some restrictions with the other code
in the workbook, here is what I am wanting it to do.

- Copy Currently active sheet to a NEW work book (Including Sheet
protection cell values, formatiing , vba code (Include code under
“this workbook”, etc)
- Rename the new workbook to the Active sheet name that was copied
over.
- Attach the Workbook to a new out look email. (Without saving
workbook to a file.)
- Close the new workbook with out saving.

Here is the code I have so far, but am stuck as to how to copy active
sheet and rename it.

Sub Button1_Click()
Dim OL As Object ' Outlook Object
Dim EmailItem As Object ' A new mail item (e-mail)
Dim lngLoop As Long
Dim FileName As String ' The name of the file we are attaching
Dim SheetName As String ' Email Subject

Set OL = CreateObject("Outlook.Application") ' New Outlook
application
Set EmailItem = OL.CreateItem(OLMailItem) ' New MailItem

' Shut Down Screen and Events
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

SheetName = ActiveSheet.Name ' Email Subject

'Need Code here to create the new workbook with an
‘exact copy of the active worksheet in it. (Including
‘Sheet protection cell values, formatiing , vba code
‘ (Include code under “this workbook”, etc)
'
' I then need to rename the workbook to the sheet
‘ name copied. (SheetName)

' Load Email
With EmailItem ' with the newly created e-mail
.Subject = SheetName
.Body = SheetName
.Attachments.Add SheetName 'Add New Workbook
.Display ' Load The Email
End With

Set OL = Nothing ' clean down memory
Set EmailItem = Nothing ' clean down memory

‘ Code here to close the New workbook (no Save)

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Try this
http://www.rondebruin.nl/mail/folder2/mail2.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ...
I am trying to code a button to create an email with the active
sheet as an attachment. Due to some restrictions with the other code
in the workbook, here is what I am wanting it to do.

- Copy Currently active sheet to a NEW work book (Including Sheet
protection cell values, formatiing , vba code (Include code under
"this workbook", etc)
- Rename the new workbook to the Active sheet name that was copied
over.
- Attach the Workbook to a new out look email. (Without saving
workbook to a file.)
- Close the new workbook with out saving.

Here is the code I have so far, but am stuck as to how to copy active
sheet and rename it.

Sub Button1_Click()
Dim OL As Object ' Outlook Object
Dim EmailItem As Object ' A new mail item (e-mail)
Dim lngLoop As Long
Dim FileName As String ' The name of the file we are attaching
Dim SheetName As String ' Email Subject

Set OL = CreateObject("Outlook.Application") ' New Outlook
application
Set EmailItem = OL.CreateItem(OLMailItem) ' New MailItem

' Shut Down Screen and Events
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

SheetName = ActiveSheet.Name ' Email Subject

'Need Code here to create the new workbook with an
'exact copy of the active worksheet in it. (Including
'Sheet protection cell values, formatiing , vba code
' (Include code under "this workbook", etc)
'
' I then need to rename the workbook to the sheet
' name copied. (SheetName)

' Load Email
With EmailItem ' with the newly created e-mail
.Subject = SheetName
.Body = SheetName
.Attachments.Add SheetName 'Add New Workbook
.Display ' Load The Email
End With

Set OL = Nothing ' clean down memory
Set EmailItem = Nothing ' clean down memory

' Code here to close the New workbook (no Save)

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

That will not work. as I mentioned in my previous post, I must do
this a certain way. I have some restrictions about what I am able to
do.

The code I posted is how I need to approach this. For example, I
would prefer not to save it to a file and attach it. (The persons who
will be using this have very little control on their computers. (A
very over powering IT department.) If you do not mind, refer back to
my post, and see if you can help fill in the blanks..

Thanks!

On Fri, 20 Oct 2006 23:58:34 +0200, "Ron de Bruin"
wrote:

Try this
http://www.rondebruin.nl/mail/folder2/mail2.htm

would prefer not to save it to a file and attach
If you want to send a file the only way is to attach it to the mail

What you want is to copy the whole workbook and delete
all sheets except the active one and send that file

Am I correct ?

Try my add-in with the workbook Special option
http://www.rondebruin.nl/mail/add-in.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ...

That will not work. as I mentioned in my previous post, I must do
this a certain way. I have some restrictions about what I am able to
do.

The code I posted is how I need to approach this. For example, I
would prefer not to save it to a file and attach it. (The persons who
will be using this have very little control on their computers. (A
very over powering IT department.) If you do not mind, refer back to
my post, and see if you can help fill in the blanks..

Thanks!

On Fri, 20 Oct 2006 23:58:34 +0200, "Ron de Bruin"
wrote:

Try this
http://www.rondebruin.nl/mail/folder2/mail2.htm

I already have code to remove all sheets except the active one;
however, I am not sure to how copy the entire workbook to a new
workbook. (I also will need to change the name of the new work book
before attaching)

And yes I want to attach the new workbook to an email, but not have to
save it to the hard drive. (I have seen simple code a while back to
attach a single sheet to an email with saving it to the hard drive.)

I really appreceiate your effort to understand what I am trying to do
here. On a very tight time line, so any more help you can give would
be greatly apprecited)

On Sat, 21 Oct 2006 14:13:53 +0200, "Ron de Bruin"
wrote:

would prefer not to save it to a file and attach
If you want to send a file the only way is to attach it to the mail

What you want is to copy the whole workbook and delete
all sheets except the active one and send that file

Am I correct ?

Try my add-in with the workbook Special option
http://www.rondebruin.nl/mail/add-in.htm

My Add-in have this option also

You can use SaveCopyAs to create a copy of your workbook
Opn this workbook with code and delete the sheets you want.
Then Save/Close/Mail/kill this workbook


--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ...
I already have code to remove all sheets except the active one;
however, I am not sure to how copy the entire workbook to a new
workbook. (I also will need to change the name of the new work book
before attaching)

And yes I want to attach the new workbook to an email, but not have to
save it to the hard drive. (I have seen simple code a while back to
attach a single sheet to an email with saving it to the hard drive.)

I really appreceiate your effort to understand what I am trying to do
here. On a very tight time line, so any more help you can give would
be greatly apprecited)

On Sat, 21 Oct 2006 14:13:53 +0200, "Ron de Bruin"
wrote:

would prefer not to save it to a file and attach
If you want to send a file the only way is to attach it to the mail

What you want is to copy the whole workbook and delete
all sheets except the active one and send that file

Am I correct ?

Try my add-in with the workbook Special option
http://www.rondebruin.nl/mail/add-in.htm