Concatenate email addresses

[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

 

[Gord Dibben]

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

 

[Markytee]

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?

 

[Rick Rothstein]

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.