Automatically Loop and Send E-mail

LadyDi

Registered User.
Local time
Today, 12:55
Joined
Mar 29, 2007
Messages
894
I have a question for you. I am setting up a database to track preventative maintenance required for testing equipment in our repair shop. Some of my associates would like to be able to send e-mail notifications via the database when maintenance is due. I set up a query to show the people who need to receive an e-mail and a query to show the maintenance each person needs to perform. Each person will need to perform more than one maintenance step each time. I would like to get it so that the database will loop through the first query and generate one e-mail for each person. I would like the e-mail to list all the steps that person would need to do (as shown in the second query). I know this would require setting up loops and I'm afraid I'm not very good at writing loops. I think this will need two loops. One to loop through the first query to see all the e-mails that will need generated and one to loop through the actions to be included in each e-mail. The person's name is included in both queries and can be used to set up a link between the two. Does anyone have any suggestions on how to do this?
 
Yes, indeed you will need two loops, the second inside the first.

The first will be for every person and the second loop to get the maintenance steps for that person.

You'll need to open up a recordset for the people and another second recordset in the second loop a with the steps for a particular person.
 
This is what I have come up with so far. However, I keep get the "too few parameters" error message. When I hit Debug on that message, it highlights the line of code where I try to set my second record set. I'm trying to limit the second record set to the current record in the first record set, but I think I must be going about it the wrong way. Do you have any ideas?

Code:
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
 
I got it to work :) This is what I have. If there is a better way to do this, please let me know.

Code:
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 " & "#" & CDate([Forms]![PMAdministrativeOptions_frm]![StartDate]) & "#" & " And " & "#" & CDate([Forms]![PMAdministrativeOptions_frm]![EndDate]) & "#" & ")) 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 [PrimaryAssignee] =" & "'" & r![PrimaryAssignee] & "'" & "AND [DueDate] = " & "#" & r![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) & Chr(12) & ActionsDue, 1
Set rd = Nothing
ActionsDue = " "
r.MoveNext
Loop
r.Close
 
I'd suggest some changes. Rather than use Currentdb, set a reference to it and use that. (see amendments). Rather than changing the saved queries, open the recordsets based on the SQL strings. I also like to name my recordsets meaningfully so I know what is what.

To debug, the debug.print lines will show what has been generated in the Immediate window in the VBA window. Copy/paste that into a new query and let Access tell you what is missing.

I don't know what you are wanting to achieve using chr(12) and why use chr(32) when you are elsewhere using " ". Similarly chr(44) is a ","

I hope you have Option Explicit at the top of your VBA module.

Code:
Dim rstPeople As Recordset Dim email As String Dim rstDetails As Recordset Dim ActionsDue As String, strSQL as string
dim db as database

Set db = Currentdb
strSQL =  "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 " & "#" & CDate([Forms]![PMAdministrativeOptions_frm]![StartDate]) & "#" & " And " & "#" & CDate([Forms]![PMAdministrativeOptions_frm]![EndDate]) & "#" & ")) ORDER BY tblPM_Equipment.PrimaryAssignee, tblPM_Completed.DueDate" debug.print strSQL

set rstPeople = db.openrecordset(strSQL)

Do While Not rstPeople.EOF email = email & ";" & rstPeople!UserEmail  
strSQL="Select * from [PMDueEmail_qry] Where [PrimaryAssignee] =" & "'" & rstPeople![PrimaryAssignee] & "'" & "AND [DueDate] = " & "#" & format(rstPeople![DueDate],"mm/dd/yyyy") & "#") debug.print strSQL
Set rstDetails = db.openrecordset(strSQL)
ActionsDue=""
 Do While Not rstDetails.EOF         ActionsDue = ActionsDue & ", "  & rstDetails![PM_Desc] & " on " & rstDetails![Description] & " ID Number:  " & rstDetails![PM_EquipmentID] & " Located at " & rstDetails![Facility] & Chr(32) & rstDetails![FacilityLocation] & Chr(12)     rstDetails.MoveNext     Loop     ActionsDue = mid(ActionsDue,3)
    
    email = mid(email,3)
 DoCmd.SendObject acSendNoObject, , , rstPeople!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) & Chr(12) & ActionsDue, 1  rstPeople.MoveNext Loop
rstDetails.Close:set rstDetails= nothing
rstPeople.close: set rstPeople= nothing
set db = nothing
 

Users who are viewing this thread

Back
Top Bottom