Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    26

    Unanswered: e-mail query results only to person that it affects

    I have:

    Private Sub cmdEmail_Click()
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset

    Dim strSQL As String
    Dim eSub, eText As Variant
    strSQL = "Select * from [NMC: email test]"
    eSub = Me!msgSubject
    eText = Me!msgMessage
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    Do While Not rs.EOF
    DoCmd.SendObject acSendQuery, "NMC: email test", acFormatHTML, rs![NMC Email Address], , , eSub, eText, False
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    DoCmd.SendObject
    End Sub

    but I want it to send the results only to the person it affects, not every person on the list. How do I proceed?

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    How do you determine who it affects?

    Will the query need to be filtered?

  3. #3
    Join Date
    Feb 2006
    Posts
    26
    Yes, the query results will be filtered by e-mail address and the results will need to be sent only to that person

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    If you want to create several emails one for each name in the dataset:

    create a loop outside of your current loop

    use a groupby query to get a full list of recipients

    loop through the recipients setting a variable = to each one

    use that variable as criteria in the SQL of your current loop (now your nested loop)

    send your email

    close the inner loop

    move to the next recipient name in your outer loop



    Or you can have the user choose a recipient from a combo box on the same form as the cmdEmail button. Use the code you have adding the combo box contents to the SQL WHERE clause and to SendObject.

  5. #5
    Join Date
    Feb 2006
    Posts
    26
    Thanks

    should I use the same kind of loop?

  6. #6
    Join Date
    Feb 2006
    Posts
    26
    I am having trouble accomplishing this...can you give me the outer loop?

  7. #7
    Join Date
    Feb 2006
    Posts
    26
    This is what I have so far , but I get a no current record error




    Private Sub cmdEmail_Click()
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim myobject, mycollection
    Dim qdf As DAO.QueryDef
    Dim eg As String

    Dim strSQL As String
    Dim strsql2 As String

    Dim stDocName As String


    Dim eSub, eText As Variant

    strsql2 = "SELECT [NMC: email test].[NMC email address] FROM [NMC: email test] GROUP BY [NMC: email test].[NMC email address]"
    Set rs2 = CurrentDb.OpenRecordset(strsql2)
    rs2.MoveFirst
    Do While Not rs2.EOF

    eg = rs2![nmc email address]



    strSQL = "Select * from [NMC: email test] WHERE ((([NMC: email test].[NMC email address]) ='eg'))"

    eSub = Me!msgSubject
    eText = Me!msgMessage

    Set rs = CurrentDb.OpenRecordset(strSQL)



    rs.MoveFirst

    Do While Not rs.EOF

    DoCmd.SendObject acSendQuery, "NMC: email test", acFormatHTML, rs![nmc email address], , , eSub, eText, False
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing


    rs2.MoveNext
    Loop
    Set rs2 = Nothing

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •