Concatenate email addresses

  • Thread starter Thread starter Renegade
  • Start date Start date
R

Renegade

I am working in Excel XP, is there a way to take a column of email addresses
and concatenate them into one cell to create a distribution list for email
purposes? Any assistance would be greatly appreciated.

Thanks.

Renegade
 
If not too many try

=A1 & "," & A2 & "," etc.

Or use a UDF to give you comma-separated list in one cell.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

Copy/paste to a General module in your workbook.

usage is =ConCatRange(A1:A50)


Gord Dibben MS Excel MVP
 
If you have a list of email addresses, depending on the package from which
you intend emailing (ie Outlook), is it not better to use the list as a
source for a mail merge?
 
Here is a slightly shorter, loopless UDF (if that is the way the OP wants to
go)...

Function ConcatCellsInColumn(ColumnRange As Range) As String
ConcatCellsInColumn = Join(WorksheetFunction.Transpose(ColumnRange), ",")
End Function

Note: This function will only work for data in a single column.
 
Back
Top