Make a distribution list from excel rows

Hi, i have a list of 400 email addresses, i currently have them in cells a1-a400

How can i insert commas so that i can paste into an email?

Thanks
Seamus2626Asked:
Who is Participating?
 
mankowitzCommented:
here is the quick & dirty

go to cell b1 and put in
=a1 & ","

Open in new window


Now go to cell b2 and put in
=b1 & a2 & ","

Open in new window


Now copy cell b2 to b400 and get  the text in b400
0
 
Glenn RayExcel VBA DeveloperCommented:
If you're pasting into Outlook, you don't have to add the commas.  Just copy all the cells, open an Outlook email message and paste into the "To:" (or Bcc:) box.  Then click "Check Names" and it will update and add delimiters (semi-colons or commas) automatically.

Regards,
-Glenn
0
 
johnb25Commented:
This is a macro I created for myself to create delimited lists.
It prompts you to select your range, and the delimiter character to use.
It creates a delimited list and copies it to the clipboard; then paste to where you need the list.
Paste this into a new module in VBA.


John


Sub DelimitedList()

' Macro to convert values in a range of separate cells into a delimited list for input to forms etc.
' Run this macro, select the input cells and enter the delimiter and encapsulating characters in the boxes as prompted.
' The list of separated values is then copied to the clipboard, and can be pasted as required.

' To run this macro, you need to have Microsoft Forms 2.0 Object Library installed.

Dim strArray As String
Dim strDelimiter As String
Dim strEncapsulate As String
Dim rngSelectedRange As Range
Dim DataObj As New MSForms.DataObject

Set rngSelectedRange = Application.InputBox(prompt:="Select the range containing your list", Type:=8)

' Select visible cells only
  rngSelectedRange.Select
  Selection.SpecialCells(xlCellTypeVisible).Select

' Prompts for separator and encapsulating values
strDelimiter = InputBox("Enter List Separator to use:" & vbCrLf & "(For New Line enter CR)")         'Cancel the input box if not needed
If UCase(strDelimiter) = "CR" Then strDelimiter = Chr(13)
'Else: strDelimiter = strDelimiter
'End If

strEncapsulate = InputBox("Enter Encapsulating Character:")       'Cancel the input box if not needed

For Each cell In Selection

    'Let strArray = strArray & strEncapsulate & cell.Value & strEncapsulate & Asc(strDelimiter)
    Let strArray = strArray & strEncapsulate & cell.Value & strEncapsulate & strDelimiter
Next cell

Let strArray = Left(strArray, Len(strArray) - 1)            'Deletes the delimiter at the end of the string - assumes one used

DataObj.SetText strArray
DataObj.PutInClipboard

MsgBox ("List copied to clipboard")

End Sub
0
 
Glenn RayExcel VBA DeveloperCommented:
^Heh!  I just offered that solution (mankowitz's) to somebody last week in a different question.  It works, but it's not needed for this application.
0
 
Seamus2626Author Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.