help with Application.EnvelopeVisible

  • Thread starter Thread starter HammerJoe
  • Start date Start date
H

HammerJoe

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?
 
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...
 
Any insight on this problem?
How can I make sure that excel closes the email app properly?
 
Show us your complete mail code

Btw: do you have the same problem with
Application.CommandBars.FindControl(ID:=3738).Execute
 
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 here:
http://groups.google.com/group/micr...a/dcb3867cdc887b82?lnk=st&q=#dcb3867cdc887b82

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).Execute
'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).Execute
'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
 
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


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 here:
http://groups.google.com/group/micr...a/dcb3867cdc887b82?lnk=st&q=#dcb3867cdc887b82

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).Execute
'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).Execute
'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
 
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
 
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.
 
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


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.
 
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?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with ActiveWorkbook.EnvelopeVisible 0
BeforeClose Event 1
ThisWorkbook coding problem... 1
.DisplayFormulaBar = True doesn't display formula bar... 3
Weird error code..."400" 14
Sorting columns horizontally 2
Custom Right Click--VBA? 4
Sort in hidden page crach 8

Back
Top