1

I need to send group emails. The email addresses are stored in an excel sheet in different columns. How do I merge all of the email addresses into one cell so that I can easily send group emails. The CONCATENATE function works but is their a way I can do it easily involving around 300 cells?

2

Edit: I just noticed your email addresses are in "different columns". If they're in a single row instead of a single column, then you can use this same technique, but obviously use "row 2" instead of "column B".

Say your email addresses are in column A:

    A
   --------------------
1  alice@example.com
2  bob@example.com
3  charlie@example.com
4  dave@example.com

Set cell B1 to =A1:

    A                    B
   -------------------- ---------------------
1  alice@example.com    alice@example.com
2  bob@example.com
3  charlie@example.com
4  dave@example.com

Now set cell B2 to =B1 & ";" & A2:

    A                    B
   -------------------- ---------------------
1  alice@example.com    alice@example.com
2  bob@example.com      alice@example.com;bob@example.com
3  charlie@example.com
4  dave@example.com

Now fill down cell B2:

    A                    B
   -------------------- ---------------------
1  alice@example.com    alice@example.com
2  bob@example.com      alice@example.com;bob@example.com
3  charlie@example.com  alice@example.com;bob@example.com;charlie@example.com
4  dave@example.com     alice@example.com;bob@example.com;charlie@example.com;dave@example.com

In other words, every cell in column B is "whatever is in the cell above, plus a semicolon and the next email address". The last cell includes every email address.

| improve this answer | |
  • 2
    If they're in different columns but in only one row, you can also "Copy > Paste Special > Transpose" to get them in a single column. – Jerry Apr 23 '13 at 7:54
1

Alternatively if the addresses are in a single row but in different columns, you can:

  • copy the emails (the entire row)
  • paste them in notepad
  • select a tab character
  • copy it
  • hit Ctrl + H to get the find/replace function
  • paste in the tab character in the find and replace with your email address delimiter you're looking for (comma , or semicolon ;)

It will take a little time but it does the job as well! (except it's no more in excel but spares you long the 'middle calculation' excel has to do)

| improve this answer | |
  • easiest way so far... – tumchaaditya Apr 23 '13 at 13:07
1

Regardless of whether the email addresses are organised in rows and/or columns: Just select and copy them.

Then go to Outlook and paste them into the To: or CC: field. Outlook will recognize the cell delimiter as a delimiter between email addresses and will take care of the semicolon separator.

The same applies if you are using VBA to create the email. Just copy and paste. No need to concatenate the cells beforehand.

| improve this answer | |
  • not working...not with outlook 2010... – tumchaaditya Apr 23 '13 at 13:06
  • @tumchaaditya -- works perfectly fine in my Office 2010 environment with Excel 2010 and Outlook 2010. I use it all the time. Check your setup and service packs. – teylyn Apr 23 '13 at 13:37
0

On Google Sheet you can use

=CONCATENATE(TRANSPOSE(A2:A),", ")

Where A is the column with emails (A1 for header).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.