VBA - Group by value in table and send e-mail

[oliverbusk]

I have a large list where I have different companies and organisations. My current VBA codes iterates through each table row, and send out an e-mail for each line.

However, what I need to achieve is that there might be companies which have the same parent organisation - in this case, I don't want to send out an e-mail for each company, but would rather send out one e-mail to the organisation, including all the associated companies.

I have tried to illustrate it in below table.



So for record #1 , I would just send out an e-mail as always. However for [2-3], I would like to send out one e-mail, which includes company name 2 and 3. Same goes for record [4-6], which should also send out one e-mail, but include company name #4 , #5 and #6 .

Example:

Hi Organisation B

Please check below companies in your organisation:

• Company 2
• Company 3

My current code: Below is my current code, which iterates through all table rows and send out mails:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">'Count table rows
 Set tbl = oWorksheet.ListObjects("DataTable")
 TableRows = tbl.ListRows.Count
 TableRows = TableRows + 1 'Skips the header
 iRow = 2 'Skip the header

 Do While iRow <= TableRows
       Set oNotesMail = oMailDB.CreateDocument
       oNotesMail.Form = "Memo"
       oNotesMail.SendTo = oWorksheet.Cells(iRow, 5).Value
       oNotesMail.Subject = "Company check"
       oNotesMail.Body = "Hi " & oWorksheet.Cells(iRow, 2) & "Please check"
       oNotesMail.SaveMessageOnSend = True
       oNotesMail.PostedDate = Now()
       oNotesMail.Send 0, oWorksheet.Cells(iRow, 5).Value
       Set oNotesMail = Nothing
    Loop
</code>

So above code send out an email (in Lotus Notes) for each line.
How can I achieve my example above?

Please note

• The table will always be sorted by 'Organisation' - hopefully this will help when the code is iterating through the rows.