Code to loop through and Send Emails to users. (1 Viewer)

tucker61

Registered User.
Local time
Today, 07:31
Joined
Jan 13, 2008
Messages
321
I have attached a cut down version of my Database.

What i am trying to achieve is that on the Form FrmQcNonConformanceDeail we have a field TBSubCat.

I am trying to look at this field, and then pull the email address's from the Query QryNCEmails. against that TbSubcat field and that Job, and then move on to the next email address for the same field tbsubcat, and the same job. So i can create a email to send to all the emails inthe Query QryNCEmails.

Example - I need it to pull both all email address's for the All Tbsubcat that are linked to job 00116634.

The first issue is have is that line Set Index line of code - Is bringing back a error 3061 Too few Parameters - Expected 1
The next issue i have is trying to loop through the email address's and bring back the relevant email address's for the users who are linked to job 00116634

appreciate any help.

Code:
  Function GetEmailList()
Dim Index As Recordset
Dim WhereClause As String
    GetEmailList = ""
    usertype = "Buying"
  
    WhereClause = "WHERE (User_Type_Desc='" & usertype & "') AND ([Product_range]='" & Forms!frmQcnonConformanceDetail.tbSubCat & "');"
    Debug.Print WhereClause
    Set Index = CurrentDb.OpenRecordset("SELECT DISTINCT EMail FROM tblqcusers INNER JOIN tblqcUserTypes ON tblqcusers.User_Type_ID = tblqcUserTypes.User_Type_ID " & WhereClause)
    If Not Index.EOF Then
        Index.MoveFirst
        While Not Index.EOF
            GetEmailList = GetEmailList & Nz(Index("Email"), "") & ";"
            Index.MoveNext
        Wend
    End If
    Index.Close
    Set Index = Nothing
    On Error Resume Next
    GetEmailList = Left(GetEmailList, Len(GetEmailList) - 1)
    On Error GoTo 0
End Function
 

Attachments

  • Database1.accdb
    928 KB · Views: 450

Ranman256

Well-known member
Local time
Today, 10:31
Joined
Apr 9, 2015
Messages
4,339
make these queries that produce the list of email addrs.

on the form, put a listbox, lstEmails,
on the form, put a textbox to show what report to send after picked its in txtReport

now click a Send button the users in the list the report.

Code:
'send the emails
sub btnSend_click()
dim vTo, vSubj, vBody
dim i as integer

for i = 0 to lstBox.listcount -1
    vTo = lstBox.itemdata(i)    'get next item in listbox
    lstBox = vTo        'set the listbox to this item

        'get rest of info from form
    vSub = txtSubj
    vBody = txtBody

   docmd.SendObject acSendReport ,txtReport,acFormatPDF,vTo,,,vSubj,vBody
next
end sub
 

Minty

AWF VIP
Local time
Today, 14:31
Joined
Jul 26, 2013
Messages
10,355
I would initially put your SQL into a string and examine it;

Code:
    Dim strSQL as String
   
     WhereClause = "WHERE (User_Type_Desc='" & usertype & "') AND ([Product_range]='" & Forms!frmQcnonConformanceDetail.tbSubCat & "');"
      strSQL = "SELECT DISTINCT EMail FROM tblqcusers INNER JOIN tblqcUserTypes ON tblqcusers.User_Type_ID = tblqcUserTypes.User_Type_ID " & WhereClause

    Debug.Print strSQL
    Set Index = CurrentDb.OpenRecordset(strSQL)
Then you can paste that into the normal query editor and see why it doesn't like it.
 

bastanu

AWF VIP
Local time
Today, 07:31
Joined
Apr 13, 2010
Messages
1,401
Can you please have a look at the updated file? You had the wrong field in there (Email instead of Emails). I also saved your SQL as a saved query and used that in the function.

Cheers,
Vlad
 

Attachments

  • Database.accdb
    584 KB · Views: 515

Users who are viewing this thread

Top Bottom