access 2013 - runtime 3061

[MC42015]

I have been trying to build an OnClick in Access VBA to send separate outlook emails with corresponding attachments from an Access query. I have found a bit of old code that I am trying to adapt.
This is for monthly invoice statements.

I have a parameter in the query to pull the exact attachments I need by date.

My query records start with a unique(Autonumber) id, and includes the e-mail address and the path to the attachment, The attachment is on the same drive/network.

This is an example of a query record
STID CustID ACCT APCEmail STMTAP STDATE STMTPATH
30 740 999999 [e-mail] TRUE 1/5/2019 C:\Users\Summaries\EMAIL\999999.xlsx

My first problem is on Set rsEmail, runtime 3061 with two few parameters. Is this VBA correct to open a parameter query?

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
 Set rsEmail = MyDb.OpenRecordset("qrysendEMstmt", dbOpenSnapshot)
  
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(3)) = False Then
                sToName = .Fields(3) 
                sSubject = "SG Acct Summary: " & .Fields(2) 
                sMessageBody = ""Please find attached account summary for your reconciliation." & vbNewLine & "Kindly feedback payment status." & vbNewLine & "Thank you!"
 
                DoCmd.SendObject acSendObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
 

Thank you for any input so I can continue to test this.....

[twinnyfo]

My guess is that your error is in qrysendEMstmt.

Please post your SQL for that Query and we can take a look.

This error can happen sometimes when you are using references to open forms.

Hope this hepps!

[MC42015]

thank you for your guidance!
This qry has two parameters that I can work around (if that is the problem!)
One is the date - to pull the records I want
The other is for testing - to pull fake account that e-mails to me feature -

SELECT tblSTMT.STID, 
       qrySendEM.CustID, 
       qrySendEM.ACCT, 
       qrySendEM.APCEmail, 
       qrySendEM.STMTAP, 
       tblSTMT.STDATE, 
       tblSTMT.STMTPATH
FROM tblSTMT 
INNER JOIN qrySendEM 
ON tblSTMT.ACCT = qrySendEM.ACCT
WHERE (((qrySendEM.ACCT)=[Enter Acct]) 
AND ((tblSTMT.STDATE)=[Enter Statement Date]));

[twinnyfo]

I notice that you are also using an embedded query for some of the data. I am not certain about this, but I think trying to build that Query from within the OpenRecordset may not work as expected. Perhaps post that query's SQL also?

I always recommend building such Queries as a SQL statement within VBA, then open the resultant string:

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
strSQL = _
    "SELECT tblSTMT.STID, " & _
        "qrySendEM.CustID, " & _
        "qrySendEM.ACCT, " & _
        "qrySendEM.APCEmail, " & _
        "qrySendEM.STMTAP, " & _
        "tblSTMT.STDATE, " & _
        "tblSTMT.STMTPATH " & _
    "FROM tblSTMT " & _
    "INNER JOIN qrySendEM " & _
    "ON tblSTMT.ACCT = qrySendEM.ACCT " & _
    "WHERE (((qrySendEM.ACCT)=[Enter Acct]) " & _
    "AND ((tblSTMT.STDATE)=[Enter Statement Date]));"
Set db = CurrentDB()
Set rst = db.OpenRecordset (strSQL)
 
... etc.

This also better allows you to troubleshoot your SQL, as you can send it to the Immediate Window to see if the Query actually says what you want it to say.

Hope this hepps.