Alternative to concatenate

Hi,

I'm looking to take a long list of e-mail addresses, probably 100 orso, that will be in Cell A2 downwards, one by one. In non-VB I would've used the concatenate formula to combine them all with a "; " in between, so I could then copy-paste in Outlook.

I know this isn't available in VB, so I was wondering if there's an obvious way to do this? My current longwinded solution is:

LValue = Worksheets("Results").Range("A2") & "; " & Worksheets("Results").Range("A2")

Worksheets("Results").Range("A102") = LValue

Not sure what you mean by isn't available in VB, but how about:

Sub ConcatenatEmailAddresses()
    Dim steMailList As String, x As Long

    For x = 1 To 100
        steMailList = steMailList & ";" & Range("A2").Cells(x, 1)
    Next x
    Range("A102") = Right(steMailList, Len(steMailList) - 1)
End Sub

Hello Toddneyx,

This code will find the last cell in column "A", and then add a semi-colon to each cell in the range.

  Dim Cell As Range
  Dim SendTo As String
  
    Set Rng = Range("A2", Cells(Rows.Count, "A")).End(xlUp)
    
    For Each Cell In Rng
      SendTo = SendTo & ";"
    Next Cell

recipients=Join([transpose(A2:A102)], ";")

or

With createObject("outlook.application").CreateItem(0)
  .Subject ="example"
  .To=Join([transpose(A2:A102)], ";")
  .send
end with

sheets("Results").Range("A2:A5").Name="Loco"

With CreateObject("outlook.application").CreateItem(0)
  .Subject = ""
  .To = Join([transpose(Loco)], ";")
End With

I hope you realise it's an unnecessary step, as I illustrated in my previous post.

The defining of a namedrange.

This will do:

  .To=Join([transpose(A2:A102)], ";")

and if you want to make that more robust:

  .To=Join(filter([transpose(A2:A1000)],"@"),";")

I've managed to get it working, partially - now it's just a matter of getting these two functions working under one button.

Private Sub PasteData()

Worksheets("Results").Range("A2:Z900").Clear

Worksheets("Data").Range("D2:D220").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Results").Range("A2")

    Dim dbresults As Range
    Set dbresults = Worksheets("Results").Range("A1:A200")
    Set dbresults = dbresults.Rows("2:" & dbresults.Rows.Count)
    Dim r As Range
    
    For Each r In dbresults.Rows
       
    If r.Cells(, EMAIL_COL) = "" _
    Then
    ElseIf r.Cells(2, EMAIL_COL) = "" _
    Then
    Else
    r.Cells(, EMAIL_COL) = r.Cells(, EMAIL_COL) & "; "
    End If
   
    Next r
    End
    
End Sub
 
Private Sub CopyEmail()

    Dim myvalue As String
    Dim x As Long
  
    myvalue = ""
    For x = 4 To 7
    myvalue = myvalue & Worksheets("Results").Cells(x, "A")
    Next x
    
    Worksheets("Results").Cells(102, "A").Value = myvalue
    
    Dim DataObj As New MSForms.DataObject
    Dim S As String
    S = myvalue
    DataObj.SetText S
    DataObj.PutInClipboard

End Sub

Private Sub Commandbutton1_Click()

CopyEmail
PasteData

End Sub

Is my sub PasteData() not "ending" correctly? Because if I assign these two subs to two different buttons, then it works 100%. But if I assign both subs to one button, or both functions under one sub - it fails.