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,
- 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.
- 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 .send End With End If Next Exit Sub debugs: If Err.Description <> "" Then MsgBox Err.Description End Sub
Thanks a lot in advance