424,066 Members | 2,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

access 2013 - runtime 3061

P: 6
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 hmb@123.com 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?
Expand|Select|Wrap|Line Numbers
  1. Dim MyDb As DAO.Database
  2. Dim rsEmail As DAO.Recordset
  3. Dim sToName As String
  4. Dim sSubject As String
  5. Dim sMessageBody As String
  6.  
  7. Set MyDb = CurrentDb()
  8. Set rsEmail = MyDb.OpenRecordset("qrysendEMstmt", dbOpenSnapshot)
  9.  
  10. With rsEmail
  11.         .MoveFirst
  12.         Do Until rsEmail.EOF
  13.             If IsNull(.Fields(3)) = False Then
  14.                 sToName = .Fields(3) 
  15.                 sSubject = "SG Acct Summary: " & .Fields(2) 
  16.                 sMessageBody = ""Please find attached account summary for your reconciliation." & vbNewLine & "Kindly feedback payment status." & vbNewLine & "Thank you!"
  17.  
  18.                 DoCmd.SendObject acSendObject, , , _
  19.                     sToName, , , sSubject, sMessageBody, False, False
  20.             End If
  21.             .MoveNext
  22.         Loop
  23. End With
  24.  
  25. Set MyDb = Nothing
  26. Set rsEmail = Nothing
  27.  
Thank you for any input so I can continue to test this.....
2 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,780
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!
2 Weeks Ago #2

P: 6
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 -

Expand|Select|Wrap|Line Numbers
  1. SELECT tblSTMT.STID, 
  2.        qrySendEM.CustID, 
  3.        qrySendEM.ACCT, 
  4.        qrySendEM.APCEmail, 
  5.        qrySendEM.STMTAP, 
  6.        tblSTMT.STDATE, 
  7.        tblSTMT.STMTPATH
  8. FROM tblSTMT 
  9. INNER JOIN qrySendEM 
  10. ON tblSTMT.ACCT = qrySendEM.ACCT
  11. WHERE (((qrySendEM.ACCT)=[Enter Acct]) 
  12. AND ((tblSTMT.STDATE)=[Enter Statement Date]));
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 2,780
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4.  
  5. strSQL = _
  6.     "SELECT tblSTMT.STID, " & _
  7.         "qrySendEM.CustID, " & _
  8.         "qrySendEM.ACCT, " & _
  9.         "qrySendEM.APCEmail, " & _
  10.         "qrySendEM.STMTAP, " & _
  11.         "tblSTMT.STDATE, " & _
  12.         "tblSTMT.STMTPATH " & _
  13.     "FROM tblSTMT " & _
  14.     "INNER JOIN qrySendEM " & _
  15.     "ON tblSTMT.ACCT = qrySendEM.ACCT " & _
  16.     "WHERE (((qrySendEM.ACCT)=[Enter Acct]) " & _
  17.     "AND ((tblSTMT.STDATE)=[Enter Statement Date]));"
  18. Set db = CurrentDB()
  19. Set rst = db.OpenRecordset (strSQL)
  20.  
  21. ... 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.
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.