I am new to VBA and loving it, but so confused.
I have located through searching the code attached below on sending the email from excel and have updated the code with some
input I needed.
I am confused on the IF Then statement that I need.
I want the spreadsheet to email the Supervisors and myself If/when a Expiration date is 10 days prior to due date(updates the
spreadsheet in yellow) for an employee. Then email again when its 5 days prior to due date(cell turns red) and remains red
until it is manually updated with the new date to stay red. I have set the colors up using conditional formatting but don't
know how to do it with VBA coding? (found .Interior.ColorIndex = 3 but can't figure how to enter it)
dates (20days and 5 days out) trigger, need the emails to include the employee's first/last name and Item Expiring(like CDL
License) with the date.
Many many thanks for any help. I hope I have followed the guidelines and explained it
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 3 'data in rows 2-3
' Get the email address
Email = Cells(r, 8)
' Message subject
Subj = "Upcoming Expiration Date(s)"
' Compose the message
Msg = ""
'Supervisor Name below
Msg = Msg & "Dear " & Cells(r, 7) & "," & vbCrLf & vbCrLf
Msg = Msg & "The following employee has a due date set to expire on "
Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "Troy Farmer" & vbCrLf
Msg = Msg & "HR-Safety Director"
' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines