Email send looping / querydef problems (1 Viewer)

[Indigo]

Hello All!
I am hoping you can help me with a looping / querydef problem. I am trying to send an email to each supervisor who has an outstanding
follow-up on an audit based on the results from this query. I am using Access and Outlook 2016.

SELECT Audit.GroupID, Audit.TMName, Audit.Process, AuditCM.CMActivity, AuditCM.FUDate, AuditCM.FUComplete, SupvInfo.AreaID, SupvInfo.Email
FROM (Audit INNER JOIN SupvInfo ON Audit.GroupID = SupvInfo.GroupID);

I have the following code, and it generates an email to each Supervisor, even if they do not have an outstanding audit because I can't
figure out how to pass any parameters - i.e.

" WHERE qryAuditFollowUp.FUDate <=#" & Forms!frmSecurity!ShiftDate & "#" & " AND qryAuditFollowUp.FUComplete = False"

to generate only the supervisors I need to email.
As well, this code, sends all of the outstanding audits to each supervisor, instead of the specific GroupID.

Public Sub SendFollowUpEmail()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String
    
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    
    Dim strSQL As String
    Dim strBody As String
    Dim rsBody As DAO.Recordset
    
    Set db = CurrentDb()
            '**creates an instance of Outlook
            Set outApp = CreateObject("Outlook.application")
            Set outMail = outApp.CreateItem(olMailItem)
            
    Set rs = db.OpenRecordset("SELECT DISTINCT AreaName, Email" & _
                                " FROM qrySTWFollowUp")
                                
    Do Until rs.EOF
    
        emailTo = rs.Fields("Email").Value
                    
        emailSubject = "Past Due Audit Follow-up(s)"
        emailText = rs.Fields("AreaName").Value & " Audit Past Due:" & vbCrLf
        
                strSQL = "SELECT * FROM qryAuditFollowUp " & _
                        " WHERE qryAuditFollowUp.FUDate <=#" & Forms!frmSecurity!ShiftDate & "#" & " AND qryAuditFollowUp.FUComplete = False"
                Set rsBody = CurrentDb.OpenRecordset(strSQL)
                
            If Not rsBody.RecordCount <> 0 Then
                ' do nothing
                    Else
                                   
            '********************* Send outstanding audit results in a HTML table ********************

                        rsBody.MoveNext
                        Loop
     
                    rsBody.Close
                    End If
        
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.To = emailTo
        outMail.Subject = emailSubject
        outMail.HTMLBody = emailText + strBody & "</Table>"
        outMail.Display
              
        rs.MoveNext
        Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

So I need to loop through each group ID and send an email that contains only the outstanding audits to each supervisor in that group.

Can anyone help me? :banghead:

 

[Uncle Gizmo]

Have a look at Gina Whipps website here:-

https://www.access-diva.com/vba16.html

Sent from my SM-G925F using Tapatalk

 

[Indigo]

Thank you, Uncle Gizmo. I have just solved the multiple email issue...

but I still am seeing all of the audit follow-ups going to each supervisor.

What I need is for the follow-ups for just Group 1 to go to the Supervisor for Group one and for the ones for Group 2 to go to the Supervisor for Group 2 and so on....

 

[Indigo]

Would a For Each Loop work to go through each group ?? I've never done that and not sure where to place it in the code... researching now, but any advice would be appreciated...

 

[Uncle Gizmo]

Would a For Each Loop work to go through each group...

I blogged about using a record set and for each loop together on my website here:-

http://www.niftyaccess.com/create-many-records/

Also the code sample is currently free, all you need do is subscribe to my newsletter...



Sent from my SM-G925F using Tapatalk

 

[Indigo]

So I am already looping through the recordset that has the group IDs and email addresses for the supervisors.

    Set rs = db.OpenRecordset("SELECT DISTINCT AreaName, Email" & _
                                " FROM qrySTWFollowUpEmail WHERE qrySTWFollowUpEmail.FUDate <=#" & Forms!frmSecurity!ShiftDate & _
                                "#" & _
                                " AND qrySTWFollowUpEmail.FUComplete = False")
    
    Do Until rs.EOF

Would I then add the following?:

    strAreaName = rs!AreaName
    For Each strAreaName In rs

Because when I do, I get a Compile Error:

For Each control variable must be a Variant or Object

I had indicated:

Dim strAreaName As String

I'm still confused.

 

[Cronk]

From what I gather, you have a set of audits which have issues outstanding and you want to email for each audit, an email to a group of people. However the group for each audit has different people, some of which might be in other audits and some not.


If so, you need to loop through 2 recordsets, the outer one being for each audit and the inner one being for the addressees.


The (incomplete) code outline below demonstrates this.

set rstAudits = db.openrecordset("YourQueryToSelectTheAudits")
rstAudits.movefirst
do while not rstAudits.eof
    '--Now get the addresses for the particular audit

   set rstAddresses = db.openrecordset("YourQueryToGetAddressess where AreaName='" & rstAudit!AreaName & "'"

   strAddress =""
   rstAddress.movefirst
   do while not rstAddress.eof
       strAddress = strAddress & ":" & rstAddress!Email
       rstAddress.movenext   

    loop
    '--Send email
   rstAudit.movenext
loop

 

[Indigo]

Actually what I did was with this first recordset that determines the email to send to:

Set rs = db.OpenRecordset("SELECT DISTINCT AreaName, Email, [B]AreaID[/B]" & _
" FROM qrySTWFollowUp")

I added the following to the second recordset that provides the data to the body of the email I send:

strSQL = "SELECT * FROM qryAuditFollowUp " & _
" WHERE qryAuditFollowUp.FUDate <=#" & Forms!frmSecurity!ShiftDate & "#" & " AND qryAuditFollowUp.FUComplete = False" & _
[B]" AND qryAuditFollowUp.AreaID =" & rs("AreaID")[/B]

And it works perfectly! Thank you for your follow-up.