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

Please not post in more then one group
See your other thread

--
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

As much as I appreciate the help, the other post did not get me where
I was hoping to go. I posted in two groups to get diffrent opinions,
and many may not read both groups.

On Sat, 21 Oct 2006 00:01:03 +0200, "Ron de Bruin"
wrote:

Please not post in more then one group
See your other thread

See
http://www.cpearson.com/excel/newposte.htm

And answer in your other thread

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



wrote in message ...

As much as I appreciate the help, the other post did not get me where
I was hoping to go. I posted in two groups to get diffrent opinions,
and many may not read both groups.

On Sat, 21 Oct 2006 00:01:03 +0200, "Ron de Bruin"
wrote:

Please not post in more then one group
See your other thread

In which case, please learn the difference between multiposting &
crossposting.
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html
--
David Biddulph

wrote in message
...

As much as I appreciate the help, the other post did not get me where
I was hoping to go. I posted in two groups to get diffrent opinions,
and many may not read both groups.

On Sat, 21 Oct 2006 00:01:03 +0200, "Ron de Bruin"
wrote:

Please not post in more then one group
See your other thread