Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ihave tried recording this but it does not record, so I am stuck.

    I am trying to:

    In existing open w/b, select sheet "Returns", move or copy, to new book, create a copy.
    File > send to mail recipient (using outlook, not as attachment)
    Fill in from, to, cc, introduction fields and send.
    Close the workbook.

    Why does the recorder not record this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='781319' date='23-Jun-2009 22:29']Why does the recorder not record this?[/quote]
    Because there is no support for this in Excel VBA. The only way to send using Excel VBA is the SendMail method, and this sends the workbook as an attachment.

    To send a sheet as the body of an e-mail, you need to use Automation to control Outlook from Excel. See Mail worksheet in the body by Ron de Bruin.

  3. #3

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, Great link, Thanks!

    I'm sending a "grid" of 10x10 cells which I would expect to be a tiny 20ish kb email. At least it is if I use the attachment method.

    If I send in the bodyof an email:

    Manually - 1mb file.

    Using Ron's code - 5mb file.

    Any idea's as to the cause of the BLOAT in both scenario's?

    I have not tried printing, but I notice that the email has endless blank space below the data (on both), even though Ron's code set's the used range.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Apparently Excel sees a larger used range than you expect. Press Ctrl+End to see where the used range ends. If necessary, delete superfluous rows and/or columns, then save and reopen the workbook.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='781324' date='23-Jun-2009 21:49']........the SendMail method, and this sends the workbook as an attachment.[/quote]

    My scenario has evolved somewhat, and I now need to use this method, could I ask for further assistance. I need to send the active sheet to a new wb, and then send as an attachment. thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Example Code for sending mail from Excel - lots of examples for different scenarios.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is an excellent resource! I'll need to spend some time hunting around Ron's site.

    Thanks again

Posting Permissions

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