Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Make a distribution list from excel rows

Posted on 2014-09-05
5
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

719 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