Group table data by common value in column, for Outlook message

Question

I have a table of employee data, with each row including their manager's email address.

I want to send an Outlook message to the email addresses from a table column named "Manager Email". I want the email to include data from several other cells in the row.

The trick is that I want to group the data for employees that have the same manager into one message, perhaps separated by a new line in the message body.

I figure it will have to do with searching for matching email addresses in the email address column.

The email drafting code works, but with a separate message for every row.

Public Sub Email()
    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim rng As Range
    Set rng = Sheets("Audit").ListObjects("Table1").ListColumns("Manager Email").DataBodyRange
    Set objOutlook = Outlook.Application
    For Each cell In rng

        Set objMail = objOutlook.CreateItem(olMailItem)

        objMail.To = Range(cell.Address).Value
        
        objMail.Subject = "(Redacted)"
        
        'for testing, I am just pulling one cell from the row of the address being emailed, two columns away. If there is a way to be specific about the column rather than use Offset, that would be preferable
      
        objMail.Body = Range(cell.Address).Offset(0, 2).Value
      
        objMail.Close (olSave)
        Set objMail = Nothing 
    Next 
End Sub

Answer 1

Instead of "searching" for matching addresses, just sort on that column. Start at the top of the data with a "check" variable set to blank. On each row check that variable against the email address on the current row - if it is different then it's time to send the email. If it's the same then just add another row of data to the current email. After each row, set the check variable to the email from that row and move on to the next.

Edit - meant to say if you're not sure of the VBA to sort on a column then use Developer, Record Macro. The resulting code will need to be tidied up but will set you on the right path