Automatically Send Emails from Excel Based on Cell date

Hi All,

Please help me anybody.

I am suffering with sending email from excel automatically ..Please see attached screen shot for your reference :
 

I am looking for a VBA Code to send emails automatically..

Sample file Link : https://we.tl/t-1JUyVYad5G

Waiting for valuable solution to send emails from outlook (office 365) or gmail accounts.

I got a suggestion from a friend below:

"I have a spreadsheet that I do exactly this, although I export the page as a PDF first, however my email is generated as part of some other code that is run, i.e. if its Wednesday do this and it sends mine through outlook installed on my machine. This could be easily set to if today = (your cell reference) do this.

Below is the part of code to check whether its a Wednesday. You would change this to comparing against your cell value and iterate through the list.

If Application.WorksheetFunction.Weekday(Now, "2") = 3 Then ' if its a Wednesday, also email the updated Quality List.

Run ("ExportQualityAsPDF") ' this generates a file called "Quality.pdf"

Else

'do nothing
End If

Code to Create the Email:

Sub Mail_QUALITY_PDF()
    '' Coded by Gary Hewitt-Long - 2023-10-06
         
  
    Dim OutApp As Object
    Dim OutMail As Object
  
    On Error Resume Next
    On Error GoTo 0
  
    AttachmentName = "Quality.pdf" 
    DORFileLocation = "N:\Continuous%Improvements\PMS\DOR\Archived%DOR\"
  
 
 myMessage = "<B> Outstanding NCR's and Customer Complaints </B> <BR> <BR>Good Morning, <BR> <BR>Please find attached a list of outstanding NCR's and Customer Complaints. If you have misplaced the original NCR, these are stored on the N drive and you can re-print. Thanks. <BR> <BR> <B>Have a Nice/Safe Day!</B> <BR> <B> <BR> <BR></B>"

EmailTo = ThisWorkbook.Sheets("Distribution_List").Range("J2").Value ' who it will email in the To Field, taken from the sheet Distribution List
EmailCC = ThisWorkbook.Sheets("Distribution_List").Range("J3").Value ' who it will email in the CC Field, taken from the sheet Distribution List
EmailBCC = ""


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
  
  

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


        On Error Resume Next
        With OutMail
            .To = EmailTo
            .CC = EmailCC
            .BCC = ""
            .Subject = "Quality - Overdue complaints and NCR's - " & Format(Date, "dd-mmm-yyyy")
            .BodyFormat = 2 'olFormatHTML ' olFormatHTML seemed to stop working, use option 2 instead.
            .HTMLBody = "<B> Quality </B> <BR> <BR>Good Day, <BR> <BR>Please find attached a listing of the TOP overdue Customer Complaints and NCR's. If you have misplaced the original NCR, these are stored on the N drive and you can re-print. Thanks. <BR> <BR> <B>Have a Nice/Safe Day!</B> <BR> <B> <BR> <BR></B>"
            '.Attachments.Add Dest.FullName
            'You can add other files also like this
            .Attachments.Add ("N:\Continuous Improvements\PMS\DOR\Archived DOR\" & AttachmentName)
            .Display   'or use .Send
        End With
        On Error GoTo 0
      

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
  
 
End Sub

Please give a brief (step by step procedure to implement on my sheet  or work on my sheet and send back to me .I am looking for valuable suggestions and help from this forum. 

Regards,

Ganesh