Massive Email Project

[Steven.Allman]

This encompasses every aspect of Access, so I just posted it in the general.

I have become pretty familiar with the email functions, and have just volunteered myself for a new update.

We are going to call this a lottery notification.
Employees are selected at random from a list of all employees. If the employee is a winner, we will email their supervisor. I have generated a make table query that selects my winners. The list is 175 names. There are about 40 supervisors. I want a doSendcmd option to send an email to

Supervisor 1, and include in the body the list of employees HE is the supervisor for.
Supervisor 2, and her employees..

SendObject (SendNoObject,,,,strSup,,strEmailEmp,,"Please be advised that the following employees" & "; " & strEmp & " have won the lottery for this month...."

Its the actual strings I am having trouble with.
The newly created table is "employees_selected" with the following fields
employee_name
supervisor_name
Supervisor_email
employee_number (randomly generated and sorted for the "selection" so doesn't really apply to my string)


Thoughts? or is this impossible?
I really want to hit ONE button, and it compile the 30-60 emails at once. I could easily do a single email and BCC all supervisors with the entire list saved as a PDF, that's easy, but I don't need all supervisors knowing who won the lottery.

Am I insane?

[pbaldy]

Not terribly difficult. I'd use 2 recordsets. The first would be the supervisors and their email address. Inside a loop of that, open a recordset on your table using the supervisor from the loop in the criteria. Loop that recordset and build your string of employees, then send an email.

[Steven.Allman]

Mind Blown...
But would this then require me to hit an email button 35-55 times?
Or would this one code write the separate emails and send them all out?
I have never done a loop, and wouldn't know where to start on that.

[pbaldy]

You can either generate the emails and let the user send each one, or send them automatically with one click. Here's my template code for a recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

[Steven.Allman]

Ok, right now you are on a whole new plane of existence.
Pretending I am a child, break it down.

Instead of using my query to create a single table with my 4 aforementioned fields,
I should create a table and sub-table (is that possible?) with Supervisor and any subordinates..
I cant create a relationship due to the table being deleted and overwritten when I run the lottery again.

I have attached some sample data from a ghost database that does mimics the process I want.

[pbaldy]

No, your first recordset is based on SQL that gets just the unique supervisors and their emails (SELECT DISTINCT...). The second uses the supervisor from the first loop to pull the employees of that supervisor from the same table.

[ssanfu]

<snip> I have generated a make table query that selects my winners. The list is 175 names.<snip>

IMO, it would be better to create the table once, then delete the old records and append new records.
Constantly deleting tables and recreating them is a good way to introduce corruption.

You can still use the query, just change it from a make table to an append query.
Use code to first execute a delete query, then the append query.

[Steven.Allman]

IMO, it would be better to create the table once, then delete the old records and append new records.
Constantly deleting tables and recreating them is a good way to introduce corruption.

You can still use the query, just change it from a make table to an append query.
Use code to first execute a delete query, then the append query.

That's a good idea there. I could then actually probably figure this out using a different query with relationships going back to original table too. Its the delete table that was making me scratch my head.

[Steven.Allman]

You can either generate the emails and let the user send each one, or send them automatically with one click. Here's my template code for a recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

Ok, so here is my code right now


Private Sub Command31_Click()
Dim rstmasterList As DAO.Recordset
Dim strTo As String
Set rstmasterList = CurrentDb().OpenRecordset("SELECT * FROM master WHERE [Name]='" & [Selected_personnel]![Name] & "'", dbOpenDynaset)
strTo = ""
If Not IsNull(rstmasterList("SUPV Email Address")) Then
strTo = strTo + rstmasterList("SUPV Email Address")
End If
DoCmd.SendObject acSendNoObject, , , rstRecordset, strTo, Bcc, Subject, MessageText, , 0
End Sub

Of course, now figuring out how to put that loop in there. Obviously this isn't working as is, because I have no reference for the [NAME] field, I am sure this is where you are telling me to add the loop feature, but I am seriously lost on that one. No idea how to make that work.

[Steven.Allman]

And I think I am really doing that wrong... It may write one email to the supervisor for each employee, meaning one supervisor may get 3 emails, but I am ok with that for now.

[pbaldy]

You can send a single email to each supervisor listing all their employees. In generic terms:

Start loop on list of supervisors
  open recordset on table using current supervisor as criteria
  start loop of employees of that supervisor
    'build a string of those employees   
    strEmployees = strEmployees & rs!Employee
  End inner loop

  Send email using above string
End outer loop