Macro to email based on Renewal Date

[howard]

I have written code to email a recipient where "yes" appears in Col G. Where "Yes" Appears in Col "G", then I want the header as well as the rows "Yes" to be emailed to the recipient"


I cannot get the code to do this


See full code below

 Sub Email_Reminder()
    Dim Email_Subject As String, Email_Send_To As String, Email_Body As String
    Dim Mail_Object As Object, Mail_Single As Variant
    Dim r As Long
    Dim cell As Range

r = 2

Do Until Trim(Cells(r, 7).Value) = ""
    Email_Subject = Sheets("Email").Range("B1")
    Email_Send_To = Cells(r, 6).Value
    Email_Body = Sheets("Email").Range("B2")

    For Each cell In Columns("G:G")

        If cell.Value = "Yes" Then

            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            With Mail_Single
                .Subject = Email_Subject
                .To = Email_Send_To
                .Body = Email_Body
                .Display
            End With

        End If
ResumeLoop:
    Next cell
Loop

Exit Sub
debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    GoTo ResumeLoop:
End Sub

See link for sample data


https://www.dropbox.com/s/rjqsnkgpb8gj19p/Email Reminder based on Date.xlsm?dl=0

It would be appreciated if someone could assist me

I have also posted on link below

https://www.excelforum.com/excel-pr...omaticaly-send-email-based-on-renew-date.html

 

[DanteAmor]

Hi Dante

The last item (G4) contains "Yes" and I4 is Blank, but no email is generated
See full code below

I did not want to download your file so that you could somehow learn to solve the problems of your data.
But, I downloaded the file and the problem with the G4 registry is that there is no BR3 sheet in your file.
If the sheet does not exist, then the file is not generated and then it does not send the mail.

 

[howard]

Hi Dante

I went through your code line by line and tried a few things but could not get it to work. I did not realise that I had not set up sheet BR3

My sincere apologies for this

Many thanks for all your help. Code works perfectly


Regards


Howard

 

[DanteAmor]

Hi Dante

I went through your code line by line and tried a few things but could not get it to work. I did not realise that I had not set up sheet BR3

My sincere apologies for this

Many thanks for all your help. Code works perfectly


Regards


Howard

it happens to everyone, there are details that escape and that is part of learning. but I'm glad to know that it works. Thanks for the feedback.

 

[howard]

I will never forget this again. Once again thanks for all your patience and effort

I have learnt a lot from your code