Writing a rule in excel... I think

B

BVZ

So I have a list of over 1500 people in my database and they all have email
addresses. I want to send an email to all these people so I copy and paste
them in to the "to" field in my email. Well it takes them out of excel and
puts them in there just fine but it only has a space between the email
addresses and not a comma or semi-colon. Is there any way to write a rule in
excel that says to put a comma or semi-colon on the end of each email
address?

Please help, I really do not want to sit there and put semi colons inbetween
each email address... That would take FOREVER!!
 
K

klswvu

In a new column enter the formula =A5&";" where A5 is your actual first
record and the column that contains the email address. Then cut and paste to
email and Delete the semi-colon at after the last address.
 
B

BVZ

Some of my fields are blank and some of the fields repeat themselves. How do
I get it to delete the duplicated items and how to I get it so that the
formula doesn't insert only a semi-colon into the fields that are blank to
begin with??

Thanks for the help so far
 
P

Pete_UK

Assuming that the formula that BVZ gave you would go into column E, then you
could amend it like this:

=IF(OR(A5="",A5=A4),E4,E4&A5&";")

When you copy this down it will give you a field which will tag all the
other addresses together, so you just need to copy/paste the final cell into
the "to" field of your email. This assumes that the addresses in column A
are sorted, so it misses out duplicates as well as blanks. You should ensure
that E4 is blank.

Hope this helps.

Pete
 
G

Gord Dibben

You could add a helper column with =A1&";" and copy down.

Copy/paste as values and use that for your address list.

Or.....................

Visit Ron de Bruin's site to learn all you will ever need about emailing
from Excel.

http://www.rondebruin.nl/sendmail.htm

Download the SendMail add-in to make life easy.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top