Mar 17 2022 01:09 AM
Greetings!
I have the below VBA working and running, however, may anyone help me to add a VBA that will add the link to the excel file where the user can click to access the file:
INSERTED MODULE:
Sub Check_Tasks()
Dim lastRow As Long, r As Long
With Sheets("Master Database")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
If .Cells(r, "K").Value = Date And .Cells(r, "AJ").Value = "" Then
Send_Outlook_Email "Task Reminder", .Cells(r, "AI").Value, .Cells(r, "AH").Value, "Email address removed"
.Cells(r, "AJ").Value = Now
End If
If .Cells(r, "I").Value = Date And .Cells(r, "AK").Value = "" Then
Send_Outlook_Email "Task Due", .Cells(r, "AI").Value, .Cells(r, "AH").Value, "Email address removed"
.Cells(r, "AK").Value = Now
End If
Next
End With
End Sub
Private Sub Send_Outlook_Email(subject As String, body As String, ToEmail As String, CCEmail As String)
Static OutlookApp As Object 'Outlook.Application
Dim objMail As Object 'Outlook.MailItem
If OutlookApp Is Nothing Then
On Error Resume Next
Set OutlookApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OutlookApp Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application") 'New Outlook.Application
End If
Set objMail = OutlookApp.CreateItem(0) 'olMailItem
objMail.subject = subject
objMail.body = body
objMail.To = ToEmail
objMail.CC = CCEmail
objMail.Send
End Sub
WORKBOOK:
Private Sub Workbook_Open()
Check_Tasks
End Sub