I have about 600 email addressesn Excel database. I wish to send and emial to
all these emails. I know you can copy and paste the email addresses to the
address box. However, I have to put a colon (;) to separate the addresses.
Is there an easy way to go about this.

Yes. If your addresses are in Col A
then in B1 enter the formula
=A1 & ";"
and copy down to row 600 (double click at right bottom of B1)
Selct Col B and paste in Outlook address bar


"Ralkie" wrote:

I have about 600 email addressesn Excel database. I wish to send and emial to
all these emails. I know you can copy and paste the email addresses to the
address box. However, I have to put a colon (;) to separate the addresses.
Is there an easy way to go about this.

Hello

Select part of the email addresses, and execute this bunch of VBA-code
statements:



Sub cellenSamenvoegen()
'
__________________________________________________ ____________________________
' |
|
' | Wim Gielis
|
' |
|
' | 05/05/2007, revised 07/02/2007
|
' | Custom module to join the contents of cells and put them in
|
' | Immediate Window and also on the Clipboard
|
' | Also on http://www.wimgielis.be
|
'
|_________________________________________________ _____________________________|


'voorafgaande noot: om de samengevoegde cellen naar het Klembord te kopiëren
moet je bij
'Tools References... (Extra Verwijzing) een vinkje zetten bij Microsoft
Forms 2.0 object library

Dim rng As Range
Dim lAantal As Long
Dim rLegeCellen As Range
Dim arrSamen() As String
Dim sScheiding As String
Dim sSamengevoegd As String
Dim MyDataObj As New DataObject
Dim sKlembordGelukt As String

On Error Resume Next
Set rLegeCellen = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rLegeCellen Is Nothing Then
If rLegeCellen.Count = Selection.Count Then
MsgBox "Je hebt enkel lege cellen geselecteerd. De macro stopt
hier.", vbInformation, Application.UserName
End If
Else
sScheiding = Application.InputBox("Geef het scheidingsteken op aub."
& vbNewLine & vbNewLine & "(Je mag " _
& "bijvoorbeeld ook , typen gevolgd door een spatie of zelfs dit
vak leeglaten)", "Scheidingsteken", _
",", Type:=2)

lAantal = 0

'array inlezen
For Each rng In Selection
lAantal = lAantal + 1
ReDim Preserve arrSamen(lAantal)
arrSamen(lAantal) = rng.Text
Next

'de tekst samenvoegen
sSamengevoegd = Join(arrSamen, sScheiding)

'het scheidingsteken aan het begin niet meenemen
sSamengevoegd = Right(sSamengevoegd, Len(sSamengevoegd) -
Len(sScheiding))

'de samengevoegde tekst naar het Immediate Window overbrengen
Debug.Print sSamengevoegd & vbNewLine

'de samengevoegde tekst naar het Klembord overbrengen
On Error GoTo 0
MyDataObj.SetText sSamengevoegd
MyDataObj.PutInClipboard

If Err.Number = 0 Then sKlembordGelukt = " en ook op het Klembord"
On Error GoTo 0

MsgBox lAantal & " cellen werden samengevoegd" & vbNewLine &
vbNewLine & "De inhoud van de samengevoegde " _
& "cellen staat nu in het Immediate Window in VBE" &
sKlembordGelukt, vbInformation, Application.UserName
End If
End Sub



The msgbox that pops up, will ask for a delimiter.

The result will be in the immediate window in VBA, and also in the clipboard.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ralkie" wrote:

I have about 600 email addressesn Excel database. I wish to send and emial to
all these emails. I know you can copy and paste the email addresses to the
address box. However, I have to put a colon (;) to separate the addresses.
Is there an easy way to go about this.

You are awesome! Your instructions were concise.

You have made my day!!

"Sheeloo" wrote:

Yes. If your addresses are in Col A
then in B1 enter the formula
=A1 & ";"
and copy down to row 600 (double click at right bottom of B1)
Selct Col B and paste in Outlook address bar


"Ralkie" wrote:

I have about 600 email addressesn Excel database. I wish to send and emial to
all these emails. I know you can copy and paste the email addresses to the
address box. However, I have to put a colon (;) to separate the addresses.
Is there an easy way to go about this.

Thanks. Helping you made my day...

I have added columns like Family, Friend, Work etc. (basically different
Distribution lists) and entered Y against the address belonging to that
group...

When required I filter on the right column, select (visible) Col B, paste in
BCC (not in To field :-))
and send out my mail...

"Ralkie" wrote:

You are awesome! Your instructions were concise.

You have made my day!!

"Sheeloo" wrote:

Yes. If your addresses are in Col A
then in B1 enter the formula
=A1 & ";"
and copy down to row 600 (double click at right bottom of B1)
Selct Col B and paste in Outlook address bar


"Ralkie" wrote:

I have about 600 email addressesn Excel database. I wish to send and emial to
all these emails. I know you can copy and paste the email addresses to the
address box. However, I have to put a colon (;) to separate the addresses.
Is there an easy way to go about this.

"Sheeloo" wrote:

Thanks. Helping you made my day...

I have added columns like Family, Friend, Work etc. (basically different
Distribution lists) and entered Y against the address belonging to that
group...

When required I filter on the right column, select (visible) Col B, paste in
BCC (not in To field :-))
and send out my mail...


Hi, I'm pretty new at some of the finer points of excel. How do you
"filter" and what do you mean by select (visible) Col B, i.e. how do you
select just the visible (I assume they are the result of the filtering?

Thanks

Suppose you have data in Col A to D with headings (labels) in row one

If you Choose Data-Filter-Auto Filter
Excel add a drop-down arrow to the cells in the top row, if you click it you
get the options to select the values you want to see.. excel then shows only
those rows which have that value.. this is what is called filtering...

Under certain conditions, when you select the filtered cells, underlying
data gets selected as a whole...

When you press F5 and then press Special you get an option to select VISIBLE
cells only... Mostly it is not required.

Only way you can master these things is by playing around... experiment and
see what happens.


"istella" wrote:



"Sheeloo" wrote:

Thanks. Helping you made my day...

I have added columns like Family, Friend, Work etc. (basically different
Distribution lists) and entered Y against the address belonging to that
group...

When required I filter on the right column, select (visible) Col B, paste in
BCC (not in To field :-))
and send out my mail...


Hi, I'm pretty new at some of the finer points of excel. How do you
"filter" and what do you mean by select (visible) Col B, i.e. how do you
select just the visible (I assume they are the result of the filtering?

Thanks