Running File | Send To (Excel 2002)

Topic

New Reply

I am trying to execute the File | Send To | Mail Recipient by using VBA code. I can run the File menu item but using this code:

CommandBars(“Worksheet Menu Bar”).FindControl(ID:=30002).Execute

I

Reply | Quote

Replies

Have a look at http://www.rondebruin.nl[/url%5D

Wolf

Reply | Quote

Hans was able to find the ID for me to use but your link is a real good one. I’ll browse it some more. Thanks!

Reply | Quote

Try

Application.Dialogs(xlDialogSendMail).Show

if you want to display the send mail dialog.

Reply | Quote

Thank you Hans, this works however I would like to use the spreadsheet as the body of the email instead of using the defaulting attachment. I noticed that the xldialogsendmail has arguments to it so I’m trying to figure out what values those arguments would be. Help tells you about the arguments but doesn’t say what values to pass. That might be the key if….

Reply | Quote

Does this do what you want?

Application.CommandBars.FindControl(ID:=3738).Execute

If not, visit Ron de Bruin’s website (Wolf posted a link in his reply)

Reply | Quote

Hans, this is it. You found the right id! Thank you so much.

Since my first post I thought of another question. Does anyone know how I can determine if the mail feature is on so I can turn it off and off so I can turn it on? For certain worksheets I want it on and for others I want it off. There’s probably a setting somewhere that I can interogate. Just a thought.

Reply | Quote

Ha! It’s the ActiveWorkbook.EnvelopeVisible property. To show the “mail feature”, use

ActiveWorkbook.EnvelopeVisible = True

and to hide it, use

ActiveWorkbook.EnvelopeVisible = False

You can also test the current value of ActiveWorkbook.EnvelopeVisible.

Reply | Quote

Hans, this worked perfectly and will make the application easier for the user. The drawback to the user doing it manually is that once it’s turned on for one worksheet it’s turned on for ALL the worksheets. This way he’ll know exact which worksheets to email without having to think or create the email himself! A big thank you for finding that!

Reply | Quote