Automatically Send Emails everyday from Excel Based on Cell date comes to today date

[ganesh0604]

Dear All,

I am new to this forum.. I need a help

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 : Sample -11Jan24.xlsm

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

Regards,
Ganesh

 

[gplhl]

Hi, 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. Do you open the workbook everyday or are you looking to completely automate? If you are looking to completely automate, you may be able to use power automate for that.

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

 

[ganesh0604]

Hi Gary Hewitt-Long,

First of all, thank you for the reply. I am little poor in excel , But i understood what you said.

if necessary to open workbook everyday for sending emails automatically I will do (if its easy then go for it)

Also if possible let me know process to send emails automatically when I open my machine using Power Automation.. Because I dont know how to use it. But I have office 365 in my PC and have Power Automate service .( if it is long process and need special permissions from management, No need use this process)

My ideal Idea is " I want to send Email remainders from working copy sheet as '10 days before for Gauge due date and 30 days before for calibration due date to selected members in email sheet'.

Please give brief Explanation(step by step) based on above sample excel sheet

Kind Regards,
Ganesh

 

[gplhl]

Yes, you would need to open. Through Power Automate, you can do it without opening, however I have not written this code before.

 

[ganesh0604]

Hi Gary Hewitt-Long,

Can you please work with VBA code on my attached sample sheet (Sample-11Jan24) and send back the sheet through a link please (prefer We Transfer app). I would like open it everyday.

Regards
Ganesh

 

[ganesh0604]

Hi..

Sample sheet link to download : Sample -11Jan24.xlsm