Writing a rule in excel... I think

[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!!

 

[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.

 

[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

 

[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

 

[Pete_UK]

Sorry, it was klswvu who gave you the formula.

Pete

 

[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