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?

0

4 Answers 4

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  [email protected]
2  [email protected]
3  [email protected]
4  [email protected]

Set cell B1 to =A1:

    A                    B
   -------------------- ---------------------
1  [email protected]    [email protected]
2  [email protected]
3  [email protected]
4  [email protected]

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

    A                    B
   -------------------- ---------------------
1  [email protected]    [email protected]
2  [email protected]      [email protected];[email protected]
3  [email protected]
4  [email protected]

Now fill down cell B2:

    A                    B
   -------------------- ---------------------
1  [email protected]    [email protected]
2  [email protected]      [email protected];[email protected]
3  [email protected]  [email protected];[email protected];[email protected]
4  [email protected]     [email protected];[email protected];[email protected];[email protected]

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.

1
  • 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, 2013 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)

1
  • easiest way so far... Apr 23, 2013 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.

2
  • not working...not with outlook 2010... Apr 23, 2013 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, 2013 at 13:37
0

On Google Sheet you can use

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .