Public Function GeneratePMNotification()
Dim r As Recordset
Dim email As String
Dim rd As Recordset
Dim ActionsDue As String
CurrentDb.QueryDefs("PeopleToEmail_qry").SQL = "SELECT DISTINCT tblPM_Equipment.PrimaryAssignee, tblUsers.UserEmail, tblPM_Completed.DueDate FROM (tblPM_Equipment INNER JOIN tblPM_Completed ON tblPM_Equipment.PM_EquipmentID = tblPM_Completed.PM_EquipmentID) INNER JOIN tblUsers ON tblPM_Equipment.PrimaryAssignee = tblUsers.UserName WHERE (((tblPM_Completed.TaskCompleted) Is Null) AND ((tblPM_Completed.EmailSent) Is Null) AND ((tblPM_Completed.DueDate) Between #10/28/2013# And CDate(#10/28/2013#)+7)) ORDER BY tblPM_Equipment.PrimaryAssignee, tblPM_Completed.DueDate"
CurrentDb.QueryDefs("PMDueEmail_qry").SQL = "SELECT tblPM_Equipment.PrimaryAssignee, tblPM_Equipment.PM_EquipmentID, tblPM_Completed.DueDate, tblPM_Equipment.Description, tblPM_Equipment.Facility, tblPM_Equipment.FacilityLocation, tblPM_Action.PM_Desc FROM ((tblPM_Equipment INNER JOIN tblPM_Completed ON tblPM_Equipment.PM_EquipmentID = tblPM_Completed.PM_EquipmentID) INNER JOIN tblUsers ON tblPM_Equipment.PrimaryAssignee = tblUsers.UserName) INNER JOIN tblPM_Action ON tblPM_Completed.PM_ActionID = tblPM_Action.PM_ActionID WHERE (((tblPM_Completed.TaskCompleted) Is Null) And ((tblPM_Completed.EmailSent) Is Null)) ORDER BY tblPM_Equipment.PrimaryAssignee, tblPM_Equipment.PM_EquipmentID, tblPM_Completed.DueDate"
'"SELECT * FROM qryDAOutput WHERE [Pull Date] = " & "'" & [Forms]![frmMain]![FileDate] & "'" & ""
Set r = CurrentDb.OpenRecordset("Select * from[PeopleToEmail_qry]", dbOpenDynaset)
Do While Not r.EOF
email = email & r(2) & ";"
Set rd = CurrentDb.OpenRecordset("Select * from [PMDueEmail_qry] Where [PeopleToEmail_qry]!PrimaryAssignee = [PMDueEmail_qry]!PrimaryAssignee and [PeopleToEmail_qry]!DueDate = [PMDueEmail_qry]!DueDate", dbOpenDynaset)
Do While Not rd.EOF
ActionsDue = ActionsDue + rd![PM_Desc] & "on " & rd![Description] & " ID Number: " & rd![PM_EquipmentID] & "Located at " & rd![Facility] & Chr(32) & rd![FacilityLocation] & Chr(12)
rd.MoveNext
Loop
rd.Close
DoCmd.SendObject acSendNoObject, , , r!UserEmail, , , "Preventative Maintenance Due This Week", r!PrimaryAssignee & Chr(44) & Chr(13) & Chr(13) & "Please perform the below preventative maintenance by the end of the week." & Chr(12) & ActionsDue, 1
r.MoveNext
Loop
r.Close
End Function