Merge multiple email cells into one cell?

[Jyggalag]

Hi all,

I currently have this setup:

My VBA code is this ( credits to @RoryA )

Option Explicit

Private Const FilePath As String = "\\UBSPROD.MSAD.UBS.NET\userdata\t684895\home\Documents\faq folder\"
Sub send_email_complete()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim col As New Collection, itm As Variant
    Dim ToAddress As String, CCAddress As String, EmailSubject As String
   
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Search Export has it)
    Set ws = ThisWorkbook.Sheets("Search Export")

    Set OutApp = CreateObject("Outlook.Application")
    
    Dim BodyText As String
    BodyText = ws.Range("P2") & "<BR>" & "<BR>" & _
         "<b><u>" & ws.Range("P3") & "</b></u>" & " " & _
         ws.Range("P4") & "<BR>" & "<BR>" & _
         ws.Range("P5") & "<BR>" & _
         ws.Range("P6")
         
   Dim AttachmentName As String
   AttachmentName = FilePath & ws.Cells(2, 15).Value2
         
    For i = 2 To ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
      '~~> Constructing addresses and subject
      
      ToAddress = ws.Cells(i, 11).Value2 & ";" & ws.Cells(i, 12).Value2 & ";" & ws.Cells(i, 13).Value2
                  
      CCAddress = ws.Cells(i, 14).Value2
                   
      EmailSubject = ws.Cells(i, 10).Value2
      '~~> This creates a new email (so we can send out multiple emails)
      Set OutMail = OutApp.CreateItem(0)

      With OutMail
         .To = ToAddress
         .CC = CCAddress
         .Subject = EmailSubject
         .HTMLBody = BodyText
         .Attachments.Add AttachmentName
         
         .Display
      
      End With
   Next i

End Sub

Now my question is whether it is possible to somehow change my VBAcode so instead of targeting multiple columns cells, like it does now with row K-M & N as CC, if it could target just a single column, for example column K only and then email to multiple emails?

I imagine that I would write something along the line of "test1@mail.com, test2@mail.com, test3@mail.com" in cell K2 and then the vba macro would separate these emails based on the comma "," and send out an email to each separate email.

Is this possible? If so I would truly appreciate some assistance here!

Thank you very much for reading this everybody!

Best regards,
Jyggalag

 

[RoryA]

Yes, it is possible, using exactly the method you describe.

 

[Jyggalag]

Yes, it is possible, using exactly the method you describe.

Perfect! Thank you Rory haha

I tried it for now, but it seems to combine the emails into one?

 

[RoryA]

Use semicolons (like in the code) in the cell, not commas.

 

[Jyggalag]

Use semicolons (like in the code) in the cell, not commas.

Wow.

That was surprisingly easy! It worked!

Thank you so much Rory! Truly appreciated

Kind regards,
Jyggalag