Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
Also I can not use an add-in. Going to be used by 100's of people who
will not have the add-in. On Sat, 21 Oct 2006 17:14:19 +0200, "Ron de Bruin" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 ", _ "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 -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ... 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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with email code (PLEASE)
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 " wrote: Also I can not use an add-in. Going to be used by 100's of people who will not have the add-in. On Sat, 21 Oct 2006 17:14:19 +0200, "Ron de Bruin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email code | Excel Worksheet Functions | |||
Need help with Email Code (PLEASE) | Excel Worksheet Functions | |||
Email VBA Code | Excel Programming | |||
VBA Code for Email | Excel Programming | |||
Email worksheet using VBA Code | Excel Programming |