Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Can someone help me how to code Application.EnvelopeVisible = FALSE if
the user decides not to use the email? Application.EnvelopeVisible = TRUE opens the email headers (To, From, Subject,etc) but Application.EnvelopeVisible = FALSE removes the headers but leaves the space occupied by them. I want to mimic clicking on the email button in the commandbar. How is it done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Perhaps you can assign a boolean variable to a button or to vbYesNo and then
have Dim YNvariable as boolean Application.EvnelopeVisable = YNvariable Just a thought... " wrote: Can someone help me how to code Application.EnvelopeVisible = FALSE if the user decides not to use the email? Application.EnvelopeVisible = TRUE opens the email headers (To, From, Subject,etc) but Application.EnvelopeVisible = FALSE removes the headers but leaves the space occupied by them. I want to mimic clicking on the email button in the commandbar. How is it done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
I not see your problem with
ActiveWorkbook.EnvelopeVisible = False But you can use Application.CommandBars.FindControl(ID:=3738).Exec ute For more Envelope code see http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Can someone help me how to code Application.EnvelopeVisible = FALSE if the user decides not to use the email? Application.EnvelopeVisible = TRUE opens the email headers (To, From, Subject,etc) but Application.EnvelopeVisible = FALSE removes the headers but leaves the space occupied by them. I want to mimic clicking on the email button in the commandbar. How is it done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Its weird.
If the ActiveWorkbook.Close SaveChanges:=False is in the run in the same macro it seems to work okay. But I use it in a different macro and that where is doesnt work properly. Why does it remove the forefront stuff but leaves the background?? Here is a picture of what happens: http://s37.photobucket.com/albums/e5...rent=excel.jpg Cheers. On Jul 8, 5:39*pm, "Ron de Bruin" wrote: I not see your problem with ActiveWorkbook.EnvelopeVisible = False But you can use Application.CommandBars.FindControl(ID:=3738).Exec ute For more Envelope code seehttp://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm wrote in ... Can someone help me how to code Application.EnvelopeVisible = FALSE if the user decides not to use the email? Application.EnvelopeVisible = TRUE opens the email headers (To, From, Subject,etc) but Application.EnvelopeVisible = FALSE removes the headers but leaves the space occupied by them. I want to mimic clicking on the email button in the commandbar. How is it done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Any insight on this problem?
How can I make sure that excel closes the email app properly? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Show us your complete mail code
Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Any insight on this problem? How can I make sure that excel closes the email app properly? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Yes I do.
Here's whats happening. In workbook MAIN in module1 theres a sub that creates a new workbook using a class thanks to DOUG he http://groups.google.com/group/micro...b3867cdc887b82 Add new workbook and copy/paste values and formats. This report is going to be emailed to other people and I dont want to have the formulas or code included (among other reasons to keep the file small). I dont think any of that code has any bearing to the problem, but if you feel otherwise I can post it. (its a mess). At the end of the sub I tried to use: Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = True On Error Resume Next ' With ActiveSheet.MailEnvelope '.Introduction = "This is a sample worksheet." '.Item.To = "E-Mail_Address_Here" ' .Item.Subject = "Tracker, " + ActiveSheet.Range("C2").Value ' .Item.display ' End With As you can see I've been playing with this. If I send an email, or if I close the sheet on the newly created workbook then it works fine, no problems. The problem is when the Excel close button is used on the title bar (the button to close excel) Unfortunately not all people are computer savy and they will click that button to close the sheet. What happens is that the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the THISWORKBOOK of the Main workbook is called and this where I this problem happens. This is the code that I have 'If Report is not open then close workbook If ThisWorkbook.Sheets("Settings").Range("J25").Value = "NONE" Then 'MsgBox "Hello" ActiveWorkbook.Save ActiveWorkbook.Close SaveChanges:=False Else 'Report is open, close sendemail and delete report workbook . 'MsgBox ActiveWorkbook.Name Application.ScreenUpdating = True Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = False 'Workbooks(ThisWorkbook.Sheets("Settings").Range(" J25").Value).EnvelopeVisible = False 'Application.ScreenUpdating = False 'Cancel = True 'Sheets("Settings").Range("J25").Value = "NONE" 'ActiveWorkbook.Close SaveChanges:=False 'Exit Sub End If Do you think you can help me? Cheers On Jul 12, 1:09*pm, "Ron de Bruin" wrote: Show us your complete mail code Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
No time today to look at your problem
Party time today with 200 people( my wife 40e birthday) so no time today. After cleaning up the mess tomorrow I will look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Yes I do. Here's whats happening. In workbook MAIN in module1 theres a sub that creates a new workbook using a class thanks to DOUG he http://groups.google.com/group/micro...b3867cdc887b82 Add new workbook and copy/paste values and formats. This report is going to be emailed to other people and I dont want to have the formulas or code included (among other reasons to keep the file small). I dont think any of that code has any bearing to the problem, but if you feel otherwise I can post it. (its a mess). At the end of the sub I tried to use: Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = True On Error Resume Next ' With ActiveSheet.MailEnvelope '.Introduction = "This is a sample worksheet." '.Item.To = "E-Mail_Address_Here" ' .Item.Subject = "Tracker, " + ActiveSheet.Range("C2").Value ' .Item.display ' End With As you can see I've been playing with this. If I send an email, or if I close the sheet on the newly created workbook then it works fine, no problems. The problem is when the Excel close button is used on the title bar (the button to close excel) Unfortunately not all people are computer savy and they will click that button to close the sheet. What happens is that the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the THISWORKBOOK of the Main workbook is called and this where I this problem happens. This is the code that I have 'If Report is not open then close workbook If ThisWorkbook.Sheets("Settings").Range("J25").Value = "NONE" Then 'MsgBox "Hello" ActiveWorkbook.Save ActiveWorkbook.Close SaveChanges:=False Else 'Report is open, close sendemail and delete report workbook . 'MsgBox ActiveWorkbook.Name Application.ScreenUpdating = True Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = False 'Workbooks(ThisWorkbook.Sheets("Settings").Range(" J25").Value).EnvelopeVisible = False 'Application.ScreenUpdating = False 'Cancel = True 'Sheets("Settings").Range("J25").Value = "NONE" 'ActiveWorkbook.Close SaveChanges:=False 'Exit Sub End If Do you think you can help me? Cheers On Jul 12, 1:09 pm, "Ron de Bruin" wrote: Show us your complete mail code Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Good evening
Copy this in the Thisworkbook module of your MAIN workbook If you close the workbook you create and your workbook MAIN will be active again it remove the mail headers. Private Sub Workbook_Activate() ThisWorkbook.EnvelopeVisible = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... No time today to look at your problem Party time today with 200 people( my wife 40e birthday) so no time today. After cleaning up the mess tomorrow I will look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Yes I do. Here's whats happening. In workbook MAIN in module1 theres a sub that creates a new workbook using a class thanks to DOUG he http://groups.google.com/group/micro...b3867cdc887b82 Add new workbook and copy/paste values and formats. This report is going to be emailed to other people and I dont want to have the formulas or code included (among other reasons to keep the file small). I dont think any of that code has any bearing to the problem, but if you feel otherwise I can post it. (its a mess). At the end of the sub I tried to use: Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = True On Error Resume Next ' With ActiveSheet.MailEnvelope '.Introduction = "This is a sample worksheet." '.Item.To = "E-Mail_Address_Here" ' .Item.Subject = "Tracker, " + ActiveSheet.Range("C2").Value ' .Item.display ' End With As you can see I've been playing with this. If I send an email, or if I close the sheet on the newly created workbook then it works fine, no problems. The problem is when the Excel close button is used on the title bar (the button to close excel) Unfortunately not all people are computer savy and they will click that button to close the sheet. What happens is that the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the THISWORKBOOK of the Main workbook is called and this where I this problem happens. This is the code that I have 'If Report is not open then close workbook If ThisWorkbook.Sheets("Settings").Range("J25").Value = "NONE" Then 'MsgBox "Hello" ActiveWorkbook.Save ActiveWorkbook.Close SaveChanges:=False Else 'Report is open, close sendemail and delete report workbook . 'MsgBox ActiveWorkbook.Name Application.ScreenUpdating = True Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = False 'Workbooks(ThisWorkbook.Sheets("Settings").Range(" J25").Value).EnvelopeVisible = False 'Application.ScreenUpdating = False 'Cancel = True 'Sheets("Settings").Range("J25").Value = "NONE" 'ActiveWorkbook.Close SaveChanges:=False 'Exit Sub End If Do you think you can help me? Cheers On Jul 12, 1:09 pm, "Ron de Bruin" wrote: Show us your complete mail code Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
Doesnt work.
Actually that sub is not even called. The only one that gets called is Workbook_BeforeClose The Sub Workbook_Activate() only gets called when I try to change from the newly opened workbook to the main one, but thats not what I am looking for. This problem only happens when the close Excel button is pressed. Weird stuff with Excel. Btw Ron I hope you had a great party yesterday. Cheers. On Jul 13, 4:21*pm, "Ron de Bruin" wrote: Good evening Copy this in the Thisworkbook module of your MAIN workbook If you close the workbook you create and your workbook MAIN will be active again it remove the mail headers. Private Sub Workbook_Activate() ThisWorkbook.EnvelopeVisible = False End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in ... No time today to look at your problem Party time today with 200 people( my wife 40e birthday) so no time today. After cleaning up the mess tomorrow I will look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in ... Yes I do. Here's whats happening. In workbook MAIN in module1 theres a sub that creates a new workbook using a class thanks to DOUG he http://groups.google.com/group/micro...rogramming/bro... Add new workbook and copy/paste values and formats. This report is going to be emailed to other people and I dont want to have the formulas or code included (among other reasons to keep the file small). I dont think any of that code has any bearing to the problem, but if you feel otherwise I can post it. (its a mess). At the end of the sub I tried to use: Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = True On Error Resume Next ' With ActiveSheet.MailEnvelope * * '.Introduction = "This is a sample worksheet." * * *'.Item.To = "E-Mail_Address_Here" ' * * .Item.Subject = "Tracker, " + ActiveSheet.Range("C2").Value ' * *.Item.display ' *End With As you can see I've been playing with this. If I send an email, or if I close the sheet on the newly created workbook then it works fine, no problems. The problem is when the Excel close button is used on the title bar (the button to close excel) Unfortunately not all people are computer savy and they will click that button to close the sheet. What happens is that the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the THISWORKBOOK of the Main workbook is called and this where I this problem happens. This is the code that I have 'If Report is not open then close workbook If ThisWorkbook.Sheets("Settings").Range("J25").Value = "NONE" Then * 'MsgBox "Hello" * ActiveWorkbook.Save * ActiveWorkbook.Close SaveChanges:=False *Else 'Report is open, close sendemail and delete report workbook . * 'MsgBox ActiveWorkbook.Name * Application.ScreenUpdating = True * Application.EnableEvents = True * Application.CommandBars.FindControl(ID:=3738).Exec ute * 'ActiveWorkbook.EnvelopeVisible = False 'Workbooks(ThisWorkbook.Sheets("Settings").Range(" J25").Value).EnvelopeVisi*ble = False * 'Application.ScreenUpdating = False * 'Cancel = True * 'Sheets("Settings").Range("J25").Value = "NONE" * 'ActiveWorkbook.Close SaveChanges:=False * 'Exit Sub *End If Do you think you can help me? Cheers On Jul 12, 1:09 pm, "Ron de Bruin" wrote: Show us your complete mail code Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
You not need the Workbook_BeforeClose event
If you close the new workbook the workbook Main will be active and will close the mail view with the code in the activate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Doesnt work. Actually that sub is not even called. The only one that gets called is Workbook_BeforeClose The Sub Workbook_Activate() only gets called when I try to change from the newly opened workbook to the main one, but thats not what I am looking for. This problem only happens when the close Excel button is pressed. Weird stuff with Excel. Btw Ron I hope you had a great party yesterday. Cheers. On Jul 13, 4:21 pm, "Ron de Bruin" wrote: Good evening Copy this in the Thisworkbook module of your MAIN workbook If you close the workbook you create and your workbook MAIN will be active again it remove the mail headers. Private Sub Workbook_Activate() ThisWorkbook.EnvelopeVisible = False End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . .. No time today to look at your problem Party time today with 200 people( my wife 40e birthday) so no time today. After cleaning up the mess tomorrow I will look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in ... Yes I do. Here's whats happening. In workbook MAIN in module1 theres a sub that creates a new workbook using a class thanks to DOUG he http://groups.google.com/group/micro...rogramming/bro... Add new workbook and copy/paste values and formats. This report is going to be emailed to other people and I dont want to have the formulas or code included (among other reasons to keep the file small). I dont think any of that code has any bearing to the problem, but if you feel otherwise I can post it. (its a mess). At the end of the sub I tried to use: Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = True On Error Resume Next ' With ActiveSheet.MailEnvelope '.Introduction = "This is a sample worksheet." '.Item.To = "E-Mail_Address_Here" ' .Item.Subject = "Tracker, " + ActiveSheet.Range("C2").Value ' .Item.display ' End With As you can see I've been playing with this. If I send an email, or if I close the sheet on the newly created workbook then it works fine, no problems. The problem is when the Excel close button is used on the title bar (the button to close excel) Unfortunately not all people are computer savy and they will click that button to close the sheet. What happens is that the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the THISWORKBOOK of the Main workbook is called and this where I this problem happens. This is the code that I have 'If Report is not open then close workbook If ThisWorkbook.Sheets("Settings").Range("J25").Value = "NONE" Then 'MsgBox "Hello" ActiveWorkbook.Save ActiveWorkbook.Close SaveChanges:=False Else 'Report is open, close sendemail and delete report workbook . 'MsgBox ActiveWorkbook.Name Application.ScreenUpdating = True Application.EnableEvents = True Application.CommandBars.FindControl(ID:=3738).Exec ute 'ActiveWorkbook.EnvelopeVisible = False 'Workbooks(ThisWorkbook.Sheets("Settings").Range(" J25").Value).EnvelopeVisi*ble = False 'Application.ScreenUpdating = False 'Cancel = True 'Sheets("Settings").Range("J25").Value = "NONE" 'ActiveWorkbook.Close SaveChanges:=False 'Exit Sub End If Do you think you can help me? Cheers On Jul 12, 1:09 pm, "Ron de Bruin" wrote: Show us your complete mail code Btw: do you have the same problem with Application.CommandBars.FindControl(ID:=3738).Exec ute -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
I need the Workbook_BeforeClose event because it is used to save the
workbook before closing it. But even without it, the Activate event is not called. Somehow the workbook main remains the active one, not the newly one using the class to create it. Nonethless the event doesnt really matter, the problem is that envelopeinvisible=false does not work properly. Did you try using it when the close event is called to close excel to see if it disposes the screen properly? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with Application.EnvelopeVisible
I try to test it this evening
But if you use this there is no problem http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... I need the Workbook_BeforeClose event because it is used to save the workbook before closing it. But even without it, the Activate event is not called. Somehow the workbook main remains the active one, not the newly one using the class to create it. Nonethless the event doesnt really matter, the problem is that envelopeinvisible=false does not work properly. Did you try using it when the close event is called to close excel to see if it disposes the screen properly? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difference application.quit & application.close | Excel Programming | |||
Application.GetOpenFilename vs Application.Dialogs(xlDialogsOpen) | Excel Programming | |||
Replace application.RTD property by Application.RTDServers collect | Excel Programming | |||
ActiveWorkbook.EnvelopeVisible = True | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming |