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!
--
Best Regards,

Luke M


"vic1" wrote:

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!