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

Topic

New Reply

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?

Reply | Quote

Replies

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:

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 

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

Sub SendMail()

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

What should I be trying?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote