Email issue-invalid argument from code

[Sentra52]

I'm just learning VBA so I am easily confused by it. I've designed a database which queries the main table looking for dates that are equal to the current date. I built a form and report based on this query. The email address is in the query, form, and report, but it varies depending on the individual record. When I try to get it to email based on the email in the query, I fail miserably. I've got it to the point where it tries to send, but I get an invalid argument and this is boggling me. Any help will be appreciated.

Code that I've done so far:



Function mail()
On Error GoTo mail_Err


Dim strSQL As String
Dim rsnewquery As Recordset


DoCmd.OpenForm "newquery", acNormal, "", "", , acNormal
DoCmd.Minimize
DoCmd.OpenReport "newquery", acViewPreview, "", ""


Set db = CurrentDb
Set rsnewquery = db.OpenRecordset("newquery", dbOpenSnapshot)


rsnewquery.MoveFirst


Do Until rsnewquery.EOF

DoCmd.SendObject acReport, "newquery", "SnapshotFormat(*.snp)", " Query![newquery]![R/O] ", "", "", "TEST DO NOT REPLY...Just Delete", "feedback due", False, ""
DoCmd.GoToRecord acQuery, "newquery", acNext, 1

rsnewquery.MoveNext
Loop
rsnewquery.Close
mail_Exit:
Exit Function

mail_Err:
MsgBox Error$
Resume mail_Exit

End Function

 

[scriverb]

Try this one. You were passing the email address improperly. You were trying to pass it as a string literal. You have opened the query as a recordset so the rsnewquery("R/O&quot should work. Everything else looks okay as long as your report name is also "newquery"

DoCmd.SendObject acReport, "newquery", "SnapshotFormat(*.snp)", rsnewquery("R/O&quot, "", "", "TEST DO NOT REPLY...Just Delete", "feedback due", False, ""

Let me know if this works for you.
Bob Scriver

 

[hermanlaksko]

Hi

I have had a look at your code and found 3 things.

1. In Tools/Refrences have a ref to Microsoft DAO 3.x
2. the db is not declared i.e. dim db as Dao.Database also dim your recordset as dao.recordset this tells access how to handle the recordset and what to expect.
i.e. Dim rsnewquery As DAO.Recordset, db As DAO.database

3. in your line:
DoCmd.SendObject acReport, "newquery", "SnapshotFormat(*.snp)", rsnewquery!R/O, "", "", "TEST DO NOT REPLY...Just Delete", "feedback due", False, ""
You had an error in the ref to the table and you also had " around this argument try the above line.

Last I do not understand why you open the form and report, you have a ref to the report in your "DoCmd......." argument thats all you need.
With these few changes I can get it to send no problem.
Let me know how it turns out.

 

[Sentra52]

Thanks for the recommendations. I made the recommended changes but now I get "Object variable or with block variable not set"

hermanlaksko, I had the form and record open to get visuals when the task was running because it get very noisy in my work area (Air Force Base Flightline). They give us nice tools, but no training to use them since it isn't our "specialty", and our specialist in the area refuse to assist us, because we are 'dumb'aircraft maintainers nad therefore should not have access to technical stuff like databases.


This is what I have now:

Function mail()
On Error GoTo mail_Err


Dim db As DAO.Database
Dim rsnewquery As DAO.Recordset
Dim strSQL As String


Set db = CurrentDb

DoCmd.OpenForm "newquery", acNormal, "", "", , acNormal
DoCmd.Minimize
DoCmd.OpenReport "newquery", acViewPreview, "", ""



rsnewquery.MoveFirst


Do Until rsnewquery.EOF

DoCmd.SendObject acReport, "newquery", "SnapshotFormat(*.snp)", rsnewquery!R / O, "", "", "TEST DO NOT REPLY...Just Delete", "feedback due", False, ""




rsnewquery.MoveNext
Loop
rsnewquery.Close
mail_Exit:
Exit Function

mail_Err:
MsgBox Error$
Resume mail_Exit

End Function

 

[hermanlaksko]

Ok no problem

Declare the recordset i.e.
Set rsnewquery = db.OpenRecordset("newquery", dbOpenSnapshot)
This line was in your priv code ?? you must have deleted it by accident - so put in your earplugs U R flying ;-)
Let me know if you have any more problems
All the best

 

[Sentra52]

Hoooooorrrraaayyyyy!

I've got a winner!
Thank you very much. This will make a lot of people happy!
We were manually doing alot of paperwork, tracking, and reporting. This will get 2 people out from behind desk and back on the planes....My Commander will "crap his pants"! His "techies" said it wasn't possible, but I knew that it was. Access has great potential when it's used. Maybe now I can convince him to send us to VB classes! Thanks Again!

P.S. Drop me an email and I'll send you guys a "choke on my own words" letter of thanks from the C/O himself along with our squadron coin.

 

[Sentra52]

Oop! forgot...email to: witherst@yahoo.com