Need macro to send email with custom range

[sfall]

A very simple example of the spreadsheet I am working with:


COMPANY INVOICE EMAIL
ABC Truck F1334 Mike@abc.com
ABC Truck F1244 Mike@abc.com
ABC Truck F1254 Mike@abc.com
A1 Freight F1543 Steve@a1.com
B&G Truck F1555 Bob@BG.com
B&G Truck F1556 Bob@BG.com

I have a macro that will send one email per line so that they get a seperate notice of each invoice but but there can be hundreds of lines per email address per day that need to be sent so I want it to group all lines for each company onto a single email so that in this example it would send 3 instead of 6.

I'm looking for a way to find all occurences of the same company name and paste that range into the body of the email but I can't figure it out.
(Prefereably it would only show cells that are not hidded or that I can specify in the macro but I'll take the whole row if that's all I can get...)

Any help or direction at all would be GREAT!!

 

[Norie]

Can you post all the code you currently have?

 

[sfall]

Here is what I currently have. I want to add a loop within the body of the email that will continue to find occurences of the same company name and add those lines OR something that will identify the range that includes all of the same company name and paste those lines into the body of the email. Either way it needs to continue through the full list until an emails is sent to each company. Those are my ideas but I'm open to suggestions if there is a better way!

Sub TestFile()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
 
    On Error GoTo cleanup
    For Each cell In Sheets("Daily Error Report").Columns("L").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Offset(0, 1).Value = "" Then
            If cell.Value Like "*@*" And Dir(cell.Offset(0, 0).Value) = "" Then
                Set OutMail = OutApp.CreateItem(olMailItem)
                With OutMail
                    .To = cell.Value
                    .Subject = "Invoice reports"
                    .Body = "The following invoices were processed today" & newline_
                    .Body = cell.Offset(0, -6).Value & "  -  " & cell.Offset(0, -2).Value
                    .Send
                End With
                Set OutMail = Nothing
            End If
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub