Send email to multiple users based on form

[JillianM]

Here is what I am trying to do. I have a form, which feeds a table. I am using it to track cadavers for a mobile lab we are doing. Each record on my form represents a single cadaver used in our lab. There will be multiple for any given day. Each record contains the name of the person in charge of the territory where the lab is being held. So, the same person will be in charge of a lab that may span 8 records or so. Hope this makes sense. So, I want to automate (using a command button) an email that gets sent out once a month, to each rep listing the cadavers used during their labs. I've been mulling over this for days. If I write a query that prompts for a date range, I can pull all of the records through by rep, but how would I automatically have it group by rep and send out only that reps records, all in one email? Originally, I was doing the automation record by record from the form, using this code:

Private Sub Command66_Click()
Dim stLinkCriteria As String
Dim Subject As String
Dim Text As String
Dim Message As String
If IsNull([RepEmail1]) Or ([RepEmail1]) = "" Then MsgBox "There is no E-mail address entered for this person!"
Exit Sub
Else
stLinkCriteria = Me![RepEmail1]
stSubject = "Mobile Lab Cadaver Disposal Fee"
stText = "Test"
DoCmd.SendObject acSendNoObject, , , stLinkCriteria, , , stSubject, stText
End If

I understand code, but not well enough to write it without serious help. I am going crazy here. Can someone give me some advice?

 

[jrbarnett]

Using your code above, you basically need to build the contents of the variable "stText" as the message body by looping through the recordset generated by running your query with the date ranges.

John

 

[JillianM]

Ok, I am confused. What I think you just said is that I can build each record for a day into the text string in the email?

Rep: Joe Smith Date:06/01/10 CadType:Shoulder
Rep: Joe Smith Date:06/01/10 CadType:Knee
Rep: Jim Brown Date:06/05/10 CadType:Ankle/Foot
Rep: Jim Brown Date:06/07/10 CadType:Ankle/Foot

I am having trouble figuring out how to concantenate the data properly in my query so that I could do a text string that includes the used fields. In the example above, I would want the email to read something like "Joe, On 06/01/2010 your lab used a shoulder and a knee...blah blah blah" Also, how would I get the command to send one email to each rep, without having to change the criterea to pull each rep individually? I hope my question makes sense. If you need clarification, please feel free to ask. Thanks!

 

[jrbarnett]

You need nested loops - one for each distinct lab person in charge, the second looping through the cadavers for that person's labs. This means you can't use the recordset below your form per cadaver as the basis for this email as it won't include all the necessary information, you will need to open your own based on the criteria.

You build the standard top of the email using information from the outer loop, the end result will be something like

"Dear Jim Brown,

The following body parts were used in your areas between <start date> and <end date> "

The second part builds a list of body data for that person, referencing the outer loop so you get something like:

Rep - Date - Type
Joe Smith - 6/1/2010 - Knee/Ankle
Jim Brown - 7/1/2010 - Foot
Bert Jones - 8/1/2010 - Shoulder
Joe Smith - 8/1/2010 - Foot

You can then append this to the first set of text, then add a simple ending, something like "Kind Regards, JillianM, your company" and send it.
Loop through to the next rep, and it processes the next one. I do this (but with SQL Server rather than Access), although the technique is still applicable.

John