Solved

Make a distribution list from excel rows

Posted on 2014-09-05
5
250 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

739 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