multiple cells with email addresses add to code

[Patriot2879]

hi i have the below code where i want to add a range to the email address located in sheet (email links) i have done for example the A2:A20 but this doesnt seem to work it only find the email address in A2 and not in A3, A4, A5 etc, please can you help me wioth this? thanks for your help it is really appreciated.

Private Sub CommandButton4_Click()

Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

        aEmail.Body = "Hi There," & Chr(10) & vbCrLf & _
                      "Appt Number: " & Me.TextBox1.Value & Chr(10) & _
                      "MPAN / MPRN: " & Me.TextBox2.Value & Chr(10) & _
                      "Time Slot: " & Me.TextBox3.Value & Chr(10) & _
                      "PostCode: " & Me.TextBox6.Value & Chr(10) & _
                      "Status: " & Me.ComboBox1.Value & Chr(10) & _
                      "Reason: " & Me.ComboBox2.Value & Chr(10) & _
                      "Additional Notes: " & Me.TextBox7.Value & Chr(10) & vbCrLf & _
                      "Many thanks " & Chr(10)
        aEmail.Recipients.Add (Worksheets("Email Links").Range[B]("A2:A20")[/B].Value)
    
        aEmail.CC = (Worksheets("Email Links").Range[B]("B2:B20")[/B].Value)
        aEmail.BCC = ""
        aEmail.Subject = "" & UserForm7.ComboBox1.Value & "  " & UserForm7.TextBox6.Value & "  " & UserForm7.TextBox3.Value & " " & Worksheets("Northants").Range("J1").Value
        aEmail.Display
       

Dim emptyRow As Long

With ThisWorkbook.Sheets("Northants")
   emptyRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

  
   .Cells(emptyRow, 1).Value = TextBox1.Value
   .Cells(emptyRow, 2).Value = TextBox2.Value
   .Cells(emptyRow, 3).Value = TextBox3.Value
   .Cells(emptyRow, 6).Value = TextBox6.Value
   .Cells(emptyRow, 10).Value = ComboBox1.Value
   .Cells(emptyRow, 8).Value = ComboBox2.Value
   .Cells(emptyRow, 7).Value = TextBox7.Value
   .Cells(emptyRow, 5).Value = ComboBox3.Value
   
End With
Unload Me

End Sub

 

[BarryL]

create one cell where you concatenate these values with a ";" then just use that cell. I recommend using Rick Rothsteins ConCat function below

http://www.contextures.com/rickrothsteinexcelvbatext.html

 

[Patriot2879]

I'm sorry i am completely lost with what you mean, please can you offer any other advise? as i am fairly new to this and still learning. Thank you for your time.

 

[BarryL]

copy the code in the link i sent and paste underneath your code in the VBE.

then in a cell (lets say cell A21) put the formula =ConCat(";",A2:A20)

then in your macro change Range("A2:A20") to Range("A21").value

 

[Patriot2879]

hiya that is great it works , how do i get the ';' to go after each email address please?

 

[Patriot2879]

Hi sorry any ideas on this please? at the moment the ; goes at the front only and the email addresses are joined together, thank you again for all your help

 

[Patriot2879]

hi sorry me again it is the semi colan that i need after each email if possible, i have used the code but all the emails are joined together and not seperated, hope you can help please.

 

[Patriot2879]

Hello , any ideas on this one at all please on how i can add multiple emails with the semi colan after each email address?

 

[paulxmw]

try =CONCATENATE(A2,";",A20)

 

[Patriot2879]

Hi thanks for the response back, i have tried this it does put the semi colan after the first semi colan but it doesnt add all the other email address for example in A3, A4, A5 etc