Macro to send Email to Unique Recipients

Hi guys,

I managed to build a macro to send out emails using the following guide - http://spreadsheetpage.com/index.php...ail_from_excel

However I've received feedback that the same recipient would like to receive 1 email for the data that belongs to him. This differs from the current emails, where 1 line in excel generates 1 email.

Is there anyway to combine the data for the emails, based on the email address, before sending it out?

Thanks and apologies in advance if this has been addressed before, I've tried looking in the past threads already.

Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
        Email = Cells(r, 2)
        
'       Message subject
        Subj = "Your Annual Bonus"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that your annual bonus is "

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "William Rose" & vbCrLf
        Msg = Msg & "President"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")        '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg        

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub

Maybe this link will provide hep

Hi, thanks for the link, I'll have a look at it later.

But does anyone know if it's possible to add some lines into the macro? Because I'm already using my own version to send 50 emails a day so I would like to minimize the changes if possible.

Thanks.

Any gurus out there with any suggestions?

any experts out there with suggestions?