Code to send email to address within selection in Excel workbook

[vic1]

You recently helped me a lot with vba code to send an email from Excel. -
Thankyou!

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 the
RondeBruin 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!

 

[Luke M]

I am afraid I don't understand completely how your table is setup, but could
you do something simple like search for the @ symbol, and use that cell's
value?

Cells.Find(What:="@", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
EmailAddress = ActiveCell.Value

Just a thought, hope it helps!

 

[Ron de Bruin]

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.

If I understand you correct

Check out this
http://www.rondebruin.nl/mail/folder3/row2.htm

 

[vic1]

Thank you both for your help - they weren't quite what I needed, but they did
enable me to solve the problem myself!

Because I had a selection, not a Range (I didn't want to have to go back
into the code to change it for every school I sent to), Ron's suggestion
wasn't quite right and I wasn't 100% certain how to fit Luke's suggestion
into my code.

However, the solution was even simpler: I just replaced the existing code
"In ThisWorkbook.Sheets("TEACHERS") _
.Range("C2:c17")" with the single word "Source" - and it does just what I
needed!

Thank you both very much!