[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Make a distribution list from excel rows

Posted on 2014-09-05
5
Medium Priority
?
274 Views
Last Modified: 2014-09-05
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
0
Comment
Question by:Seamus2626
5 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 668 total points
ID: 40306026
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 668 total points
ID: 40306029
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
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 664 total points
ID: 40306038
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40306044
^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
 

Author Closing Comment

by:Seamus2626
ID: 40306061
Thanks guys!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question