Combining text from several fields into one

[alivibe]

Hi. I have a list of email addresses in Excel 7.0a that I need to format in order to use them in my email program. The addresses appear in a column like so:

mike@yahoo.com
ted@sbcglobal.net
aaron@comcast.net

I need to combine these emai addresses so they have semi-colons and one space separating them, so that they read:

mike@yahoo.com; ted@sbcglobal.net; aaron@comcast.net

Then I'll cut and paste them into my email message.

What is the best way to do this? Thank you so much for your help!

~Ali

 

[DRJ]

Hi - Welcome to the board

Assuming these emails are in column A then

in another cell type

= A1 & "; " & A2 & "; " & A3

 

[Ekim]

=A1&"; "&A2&"; "&A3

Assuming that your original list is in A1:A3.

HTH

Mike

 

[alivibe]

Thanks--but I have a dumb question. How do I actually see the email addresses? All I see now is #NAME? in the cell I typed the formula.

Also, I have to do this with thousands of email addresses. Is there an effective way to do this for 1,000 email addresses at a time?

Thanks so much for your help!!

 

[Smitty]

Welcome to the Board!

Book1
	A	B	C	D
1	E-MailAddresses	ConcatenatedAddresses
2	mike@yahoo.com	mike@yahoo.com;ted@sbcglobal.net;aaron@comcast.net
3	ted@sbcglobal.net
4	aaron@comcast.net
Sheet1

As for 1,000 addresses, concatenating is probably the wrong way to go and you'll also exceed the Cell Text limit - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

What e-mail client are you using? You can name your range of addresses and set Recipient = MyEmailRange.

Hope that helps,

Smitty

EDIT: Crappy spelllinjg :wink:

 

[alivibe]

I'm using web based email--I can use either Hoarde, NeoMail or SquirrelMail. What I originally wanted to do was copy and paste 1,000 emails at a time into the BCC: field, but when I tried to do so, it only copied the first address (I think because the addresses are separated by paragraphs rather than spaces).

I can copy and paste a bunch of addresses into my Outlook Express, but I have a limit on how many emails I can send out that's pretty low.

I've been trying to get this email out for weeks now--it's been a real nightmare...

 

[Smitty]

If using Outlook/Outlook Express is an option, see: http://www.rondebruin.nl/

He's got pretty much all the code that you'll need as well as 2 free Add-Ins.

Smitty

 

[DRJ]

Try this quicky vba macro

Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub CombineEmails()

Dim x               As Integer
Dim LastRow         As Integer
Dim MyEmails        As String
Dim FileToOpen      As String
Dim lngRet          As Long

    FileToOpen = ThisWorkbook.Path & "\" & "EmailList.txt "
    LastRow = Range("A65536").End(xlUp).Row
    For x = 2 To LastRow
        MyEmails = MyEmails & "; " & Range("A" & x).Value
    Next x
    MyEmails = Mid(MyEmails, 3, Len(MyEmails))
    Open ThisWorkbook.Path & "\EmailList.txt" For Append As #1
    Print #1, MyEmails
    Close #1
    lngRet = ShellExecute(0, "Open", FileToOpen & vbNullString, vbNullString, vbNullString, 1)

End Sub

Now just copy and paste from notepad to your email program.

 

[TheBuGz]

Great Site Pennysaver bro .

I was actually searching it for some few fonths to be honest

 

[DRJ]

Of course I assumed that your emails were in Column A starting with Row 2. Change as needed.