Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending mail from Excel (VBA/Excel/2002-3)

    A client has an Excel template that creates a quote for e-mailing to their customer.

    I set up the template to allow the calculations to take place on sheet 1 and the formal quotation (with letterhead, etc.) on sheet 2. Print areas are set for both parts and document creator can elect to print either, both or e-mail just the quotation through a simple form.

    Until recently all went well. Then one of their customers received both (the calculations sheet with margins, etc. and the quote sheet) much to the concern of my client. Nothing has changed in the VBA code but I have noted that with limited testing I can send the correct quote sheet or both (with the file as an attachment).

    The code does not send the e-mail (although that might be the better option) rather it selects the desired print area and then the user selects the File | Send To | Mail Recipient option. Previously I noted that this brought up a dialog box with the option to send either the file as an attachment or just the selection.

    This is not happening now.

    Any suggestions please?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending mail from Excel (VBA/Excel/2002-3)

    Further to my previous post here is the relevant code to describe what is happening.

    Code for the form to select object to print or e-mail:

    <pre>Private Sub cmdOK_Click()
    If optQuoteInt = True Then
    Range("InternalCopy").PrintOut
    Unload Me
    ElseIf optQuoteCust = True Then
    Range("CustomerCopy").PrintOut
    Unload Me
    ElseIf optBoth = True Then
    Range("InternalCopy").PrintOut
    Range("CustomerCopy").PrintOut
    Unload Me
    ElseIf optEMail = True Then
    Call SendMail
    Unload Me
    End If
    End Sub </pre>

    And the code to select the area to be e-mailed.

    <pre>Sub SendMail()

    Sheets("Quote - e-mail version").Select
    Application.Goto Reference:="CustomerCopy" 'print area on customer sheet

    End Sub</pre>


    What should I be trying?

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

    Re: Sending mail from Excel (VBA/Excel/2002-3)

    I get the dialog you mention if I select File | Send To | Mail Recipient... in a workbook that has been saved to disk. In a workbook that has never been saved to disk, the dialog doesn't appear, and only the selection will be sent.
    The menu option File | Send To | Mail Recipient (As Attachment) will always send the entire workbook, I don't get a dialog whether the workbook has been saved to disk or not.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending mail from Excel (VBA/Excel/2002-3)

    Thanks Hans

    I always get the dialog to appear when I try the template from my office. But dialog box doesn't appear at the client location and that is what perplexes me. Why (or what) would cause it not to appear. BTW the dialog (copy below for other readers) appears whether the file has been saved to disk or not.

    Can I programmatically make sure that only the selection is e-mailed and therefore not have to rely on the appearance of the dialog?

    If so, can you suggest the code that would do it?

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

    Re: Sending mail from Excel (VBA/Excel/2002-3)

    See Example Code for sending mail from Excel on Ron de Bruin's website; look for 'Mail the selection or range'. The idea is to save the selection to a temporary workbook.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending mail from Excel (VBA/Excel/2002-3)

    Thanks (as always) Hans

    Sorry for the slow response but I am a one-man band and sometimes I need to keep the wolves at bay with site visits.

    A selection of the code on Ron de Bruin's website set me on the right path.

    I can't imagine that my client will have to worry that the calculations sheet will accidentally be sent with the quote.

Posting Permissions

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