sendmail macro problem in Excel

[vic1]

I've spent hours on the Tip on sending emails from Excel.
They're brilliant - but I'm a total newbie to VBA and what I need is a
combination of two of his options - and, try as I might, I cannot combine
them successfully!
I need to email a range from within Excel to an email address contained
within one of the cell values.

I've successfully got one macro which will send to the right address - but
only with a 'fixed file path' attachment, so it doesn't send a copy of the
current page, only a previously saved pdf. Which bits of the code for sending
a range do I need to copy into my existing macro, and where? I've tried the
sendmail add-in - but it doesn't automatically fill in the relevant email
address.

I'm sure this is simple to someone who knows what they're doing! Please can
you help me out?

 

[Ron de Bruin]

Hi vic1

Click on the Tip link on each example page

 

[vic1]

Thanks. That helps. I'm almost there apart from one thing - in spite of
inserting the correct cells in the range, my email attachment is returning a
copy of the whole worksheet, not just the selected cells.

What am I doing wrong?

 

[Ron de Bruin]

Use

http://www.rondebruin.nl/mail/folder1/mail4.htm

Or
http://www.rondebruin.nl/mail/folder2/mail4.htm

 

[vic1]

Thankyou.

I really sorry I am making such a mess of it - but when I use the 'Active
Sheet' macro, it is almost perfect, but I don't want to send the whole sheet.
As soon as I use 'Range' or 'Selection' either in the body or as an
attachment, the macros run through without error - but they just don't
produce an email! The window flickers a couple of times as the macro is
running, but then goes back to exactly where it was before I started.

I've been struggling with this for the last 20 hours, what on earth am I
doing wrong?

 

[Ron de Bruin]

Download a example workbook with the code and test there

http://www.rondebruin.nl/sendmail.htm
See the red links

 

[vic1]

Thanks for all your suggestions, but, for the moment I've given up! I've just
deleted all the extra stuff on the worksheet which I don't want to send, so
that I can send the whole sheet.

The example database works perfectly (as it has done all day when I've tried
it). Am I correct in thinking that I can't include CC or BCC if I use
sendmail? As I need BCC I am using the Outlook-specific variation.

What would the reason be for the email not appearing, even though there are
no errors (I get this even when I have copied and pasted your example,
without making any changes at all)?

 

[Ron de Bruin]

Wih SendMail CC and BCC is not possible

Do you also see no mail when you use the Outlook example workbook from my site ?

 

[vic1]

Using the example spreadsheet, I get a mail, exactly as it should be - but
when I use the same code on my own spreadsheet, there is no email - but no
errors either. If I use the Active sheet code, the email is there.

 

[Ron de Bruin]

Send me your workbook private
I will look at it then for you

 

[vic1]

Thank you very much indeed for this kind offer. However, the redesign of the
workbook so that I could send the whole sheet actually works better in this
instance, so I didn't send it - but I do appreciate your help!

There is though, one other related question you can probably answer if you
don't mind:

I have a different workbook in which I have set up a macro to send a
selection (it works this time!), however I would like the email sent to an
email address which is contained within the selection. I am using your code
with adjustments for the body text and to send to an address in a particular
cell - but this means that every time I change the selection I have to
remember to go into Visual Basic to change the cell reference - otherwise the
email for that particular selection is not sent to the relevant person.

Is there a piece of code I can insert which will send the email to an
address which is within the current selection, rather than a fixed range of
cells? (The address would always be in the same position, relative to the
start of the currently selected section, if that helps).

If it helps you to know, my table contains a list of teachers for each of
several different schools, together with the subjects they teach etc. I need
to send an email to each school with a workbook containing ONLY their own
teachers, for them to update - hence each selection of teachers needs a
different email address.

Very many thanks in advance!