Need help with email code (PLEASE)

[bite]

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

 

[Ron de Bruin]

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

 

[fre]

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!

 

[Ron de Bruin]

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

 

[fre]

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)

 

[Ron de Bruin]

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

 

[fre]

That was the whole point of myt orignal post, to show how I needed to
go about this. Do due not have the proper rights on there computers
(Over powering IT) , they can not delete a file from code. Their drive
would be full of temp uneeded files very quickly.

Some how I need to create a copy of the current workbook. (Chage the
name, not sure how), delete uneeded sheets, and then attach it to an
emai,l and then close the copy)

0On Sat, 21 Oct 2006 17:14:19 +0200, "Ron de Bruin"

 

[fre]

Also I can not use an add-in. Going to be used by 100's of people who
will not have the add-in.

 

[Ron de Bruin]

Then the only thing that you can do is delete the sheets in the origenal workbook and send
that (you can't name it) en then close the origenal workbook without saving

Sub Mail_workbook_test()
Dim wb As Workbook

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set wb = ActiveWorkbook

'Delete the first sheet
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = False

'Mail the book
wb.SendMail "(e-mail address removed)", _
"This is the Subject line"

'Close and not Save
wb.Close savechanges:=False

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub


If you run ther code from another workbook or add-in you can open the
origenal workbook when after it close it without saving

 

[Tim]

Not sure if this is what you are looking for, but if you look at my post
above, I believe it is similiar to what you want.

I have a macro button on the work sheet. The user clicks on it, the macro
creates a copy of the active worksheet and attaches it to the email.

As I stated above it's been working great on our Windows 2000 machines but
for some reason, it won't run on our new XP systems.

Tim