Results 1 to 9 of 9
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Running File | Send To (Excel 2002)

    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
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running File | Send To (Excel 2002)

    Have a look at http://www.rondebruin.nl

    Wolf

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running File | Send To (Excel 2002)

    Try

    Application.Dialogs(xlDialogSendMail).Show

    if you want to display the send mail dialog.

  4. #4
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Running File | Send To (Excel 2002)

    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....
    You know it's time to diet when you push away from the table and the table moves.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running File | Send To (Excel 2002)

    Does this do what you want?

    Application.CommandBars.FindControl(ID:=3738).Exec ute

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

  6. #6
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Running File | Send To (Excel 2002)

    Hans, this is it. You found the right id! Thank you so much. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    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.
    You know it's time to diet when you push away from the table and the table moves.

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Running File | Send To (Excel 2002)

    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!
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running File | Send To (Excel 2002)

    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.

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Running File | Send To (Excel 2002)

    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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •