Multiple Email body to same recipient

[brt21]

Hi,

I wrote a VBA Code to send a mail to the recipient based on a criterion. My next step is a bit complex and I am not sure how to proceed.

Problem:

I have a criterion, passing which, a mail will be sent to a recipient. But there shall be several instances where the criterion is TRUE and more than one mail will be sent to the same person. In the actual application, a person could receive upto 10 mails with a slight modification in the email body. I would like to group the information to be written in the email body and send it to the recipient as one mail. Please have a look at the file here. Based on the code, the criterion is if the value in H:H is <= -2, the programmed Actions will be performed. Assuming that the criterion is TRUE and more than one set of detail have to be sent to the same Person (based on Email in E:E), I would like to send it as one mail.

Ideas:

inserting an additional criterion after the first set of FOR and IF Statement and the new criterion is to check if there are more than one instance of the email recipient. If the condition is TRUE, insert a FOR Statement to gather data from all the possible mails to be sent to each recipient.

I am still learning VBA and not sure how to proceed with this idea. Any suggestions or sample Code is highly appreciated. Meanwhile, I will try to solve the query myself.

Thank you!

Regards,
Bharath

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim date1 As Date
    Dim today As Date
    Dim x As Long
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lastrow
        date1 = Cells(x, 6).Value
        today = DateValue(Date)
        n = DateDiff("d", today, date1)
    
        If Cells(x, 8) <= -2 Then
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            
            With OutMail
                .To = Cells(x, 5).Value
                .CC = ""
                .BCC = ""
                .Subject = "Payment Reminder"
                .Body = "Hallo " & Cells(x, 2).Value & " " & Cells(x, 3).Value & vbNewLine & _
                        vbNewLine & _
                        "Please pay your bill." & vbNewLine & _
                        vbNewLine & _
                        "Regards," & vbNewLine & _
                        "Bharath"
                .Send
            End With
            
            Cells(x, 7) = "Yes"
            Cells(x, 7).Interior.Color = xlNone
            Cells(x, 7).Font.ColorIndex = 3
            Cells(x, 7).Font.Bold = True
        End If
    
    Next
    
    Set OutApp = Nothing
    Set OutMail = Nothing
End Sub

 

[Kenneth Hobson]

The way I would do it would be to create a unique set of emails, and then filter by each email that has value <=-2. From there, if more than one, I don't know what you want in the body. Normally, one would have an invoice number or such and a date that it was due and maybe number of day past due.

 

[brt21]

The way I would do it would be to create a unique set of emails, and then filter by each email that has value <=-2. From there, if more than one, I don't know what you want in the body. Normally, one would have an invoice number or such and a date that it was due and maybe number of day past due.

Hi Kenneth,

If you have tried the code with the excel file I attached, zou would have received a mail as follows:

"Hi Kobe Bryant,

Please pay your bill number 10001


Regards,
Bharath "

For example, if more than one instance pass my criterion, the person has to receive a mail like this:

"Hi Kobe Bryant,


Please pay your bill number 10001 and 10004


Regards,
Bharath "
<strike></strike>

And by filtering, do you mean through Excel or VBA?
I have not tried it through VBA.