Change my email macro to email multiple recipients in different rows

[Jyggalag]

Hi all!

I currently have this setup in Excel:

And the code is this:

Option Explicit

Private Const FilePath As String = "\\COMPANY.MSAD.COMPANY.NET\userdata\t543932\home\Documents\TESTfolder\"
Sub send_email_complete()
Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
   
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Sheet1 has it)
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")
   
    '~~> Looping from rows 2 to 10 (update if necessary)
    For i = 2 To 10
        '~~> This creates a new email (so we can send out multiple emails)
        Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = ws.Cells(i, 15).Value2 & ";" & ws.Cells(i, 16).Value2 & ";" & ws.Cells(i, 17).Value2
.BCC = ws.Cells(i, 18).Value2
.Subject = ws.Cells(i, 13).Value2
.HTMLBody = "Dear all,<br/>" & "<BR>" & _
"Insignificant text not necessary for this example code<br/>" & "<BR>" & _
"Kind regards</br>" & "<BR>"
.Attachments.Add FilePath & ws.Cells(2, 19).Value2

.Display

End With
Next i

End Sub

The issue that I have right now, is that I want to send emails to each division (so right now a total of 10 emails to 30 recipients). However, in my real structure, I have AT MAX 3 emails per row, which means that some companies will be duplicated, as seen above, into the next 1-2 rows for further emails (I always have between 1-9 emails for each recipient (company in example above)).

Can somebody help me change my code so it can detect when the company is the same and then merge the emails together? So for company 1 it should send the email to 6 recipients, instead of 3, for example

@Siddharth Rout if you're available as well it would be greatly appreciated!

Kind regards,
Jyggalag

 

[RoryA]

I had no idea you could type .To and .Bcc without having the variable before

The With block holds a reference to an object. Anything inside that block that starts with a dot refers back to the specified object.