(Solved) – Sending an automatic email notification using Excel VBA when the due date is approaching

  • by

I am very new to VBA. Currently I am developing a machine maintenance system in which I need to send a reminder email automatically to the party which do machine maintenance when the servicing date of a particular machine is approaching. The difference of my problem from the most of the threads posted online is I want to search for the date of the next service from Service Details sheet and I want to get the email address of the service provider for that particular machine from the Machine Details sheet. I want to send two emails:

1.one email before 7 days of the service date
2.one email on the service date itself

The body of the email should be,

  1. There is a service scheduled for a Photo copy machine (Machine type taken from machine details sheet) on 1/1/2020 (next service date taken from Service Details sheet.
  2. There is a service scheduled for a Photo copy machine (Machine type taken from machine details sheet) today.

I want to send the email automatically without opening the excel file.

I went through many similar online threads and couldn’t find what I am searching for and I am not proficient enough in VBA to adopt them to fulfill my need. So, any help would be highly appreciated.

Here is the code I am currently using:

Sub email()

Dim r As Range
Dim cell As Range

Set r = Range("U2:U10000")

For Each cell In r
If cell.Value = Date   7 Then

        Dim Email_Subject, Email_Send_From, Email_Send_To, _
        Email_Cc, Email_Body As String
        Dim Mail_Object, Mail_Single As Variant
        Dim Machine_Code As Long
        Dim Machine_Type As Long

        Machine_Code = Application.WorksheetFunction.VLookup(cell.Value, Range("A:U"), 21, False)
        Machine_Type = Application.WorksheetFunction.VLookup(Machine_Code, Sheet1.Range("B:C"), 1, False)

        Email_Subject = "Service Reminder"
        Email_Send_From = "k.s@*******"
        Email_Send_To = Application.WorksheetFunction.VLookup(Machine_Code, Sheet1.Range("C:M"), 11, False)
        Email_Cc = "D@******.com"
        Email_Body = "There is a Service scheduled for a" & Machine_Type & "on" & cell.Value

        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
        .cc = Email_Cc
        .Body = Email_Body
        End With

    End If

   Exit Sub

    If Err.Description <> "" Then MsgBox Err.Description
  End Sub

Thanks a lot in advance

Leave a Reply

Your email address will not be published. Required fields are marked *