Solved

Make a distribution list from excel rows

Posted on 2014-09-05
5
235 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 167 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 167 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 166 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now