0

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.

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. seven days before the service date
  2. on the service date

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 adapt them.

Here is the code I am 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
            .send
        End With

    End If
Next

Exit Sub

debugs:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub
Community
  • 1
  • 1
T.Sarathchandra
  • 5
  • 1
  • 3
  • I also came to know that it is possible through this thread https://stackoverflow.com/questions/31189863/sending-email-from-excel-automatically-based-on-date – T.Sarathchandra Dec 31 '19 at 10:06

1 Answers1

0

scheduling a macro is not as difficult as it may sound! The code below can execute any sub inside the module it resides in at any given time with task_sub being "email" in your case!

Sub schedule_macro()
Application.OnTime "05:00:00", "task_sub"
End Sub

Here is the catch, for this macro to work. The file containing your macro needs to be open, basically always to really make sense.

To circumvent this, windows (I am guessing all other operating systems have similar options) has Task Scheduler. I found a good explanation here.

Now, this still requires your machine to be switched on at some point during the day. So perhaps you can simply just schedule all this during lunch breaks as to not interupt your normal schedule. If going on holidays, just copy paste the routine to a colleague that will be in the office during your time off.

I hope this information will help you!

MartijnDib
  • 28
  • 6