Automatically Send Emails from Excel Based on Cell date

gannesh0604 Posts 24 Registration date Thursday July 20, 2023 Status Member Last seen January 31, 2024 - Jan 17, 2024 at 01:57 AM
Jiyahana Posts 9 Registration date Friday December 29, 2023 Status Member Last seen February 9, 2024 - Jan 24, 2024 at 06:59 AM

Hi All,

Please help me anybody.

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

1704967299541.png



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 

2 responses

Hi,

See https://excelribbon.tips.net/T000474_Sending_an_E-Mail_when_a_Due_Date_is_Reached.html

1
gannesh0604 Posts 24 Registration date Thursday July 20, 2023 Status Member Last seen January 31, 2024
Jan 20, 2024 at 03:51 AM

Hi Woolley,

Thank you very much for the link which you shared. This is a gun shot solution. But only thing is I am using Office 365. The above suggested code applicable to only Excel 2007, 2010, 2013, and 2016.. I am getting some macros errors while run the code..

Please give valuable suggestion to overcome my problem

Regards

Ganesh

0
Jiyahana Posts 9 Registration date Friday December 29, 2023 Status Member Last seen February 9, 2024
Updated on Jan 24, 2024 at 07:00 AM

Hey, ganesh0604 go through this https://answers.microsoft.com/en-us/msoffice/forum/all/automatic-email-from-excel-based-on-date-in-cell/4ebb9f55-e6db-413a-9d3f-eee893627440

0