title: Sending an email notification using Excel VBA when the due date is approaching
body: <p>I am developing a machine maintenance system. I need to send a reminder email to the party which does machine maintenance when the servicing date of a particular machine is approaching.</p>
  
  <p>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.</p>
  
  <p>I want to send two emails:</p>
  
  <ol>
  <li>seven days before the service date</li>
  <li>on the service date</li>
  </ol>
  
  <p>The body of the email should be,</p>
  
  <ol>
  <li>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.</li>
  <li>There is a service scheduled for a Photo copy machine (Machine type taken from machine details sheet) today.</li>
  </ol>
  
  <p>I want to send the email automatically without opening the Excel file.</p>
  
  <p>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 adapt them.</p>
  
  <p>Here is the code I am using: </p>
  
  <pre><code>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" &amp; Machine_Type &amp; "on" &amp; 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
              .send
          End With
  
      End If
  Next
  
  Exit Sub
  
  debugs:
      If Err.Description &lt;&gt; "" Then MsgBox Err.Description
  End Sub
  </code></pre>