Send Object Command (Access 2000)

Topic

New Reply

I’m using a database to track training dates and want to notify trainees about expiration of their training. I know how to use the SendObject function when the audience is fixed. Does anyone know how to use the results of a query to fill in the TO: line item of the send function? The result I’m looking for is running a query/report based on date criteria and sending an email to each trainee whose record shows up in the query.

Reply | Quote

Replies

I think you do understand the question. I am not however VB conversant and at a loss how to proceed.

Reply | Quote

Charlotte,
Thanks for the reply, and yes you did understand the question. I have some code shown below but the “Set rs” line is giving me an ‘Invalid Argument’ when I run it. TEST-ALERT is a query name and USERID is a field name. It is suppose to build a string (strEmails) from the USERIDs in each record of TEST-ALERT and then send them a report. Any comments on the line and what may be causing the error? Thanks

Dim rs As Recordset
Dim strEmails As String
Set rs = CurrentDb.OpenRecordset(“Select UserID FROM [test-alert]GROUP BY UserID;”, dbOpenSnapshot)

Do While Not rs.EOF
strEmails = strEmails & rs!UserID & “;”
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
DoCmd.SendObject acSendReport, “test-alert-report”, strEmails

Reply | Quote

Chris, I am also not that experienced with VBA, but if I look at some code from examples I have seen, the Set rs that you have written isn’t the same. For example, here is the way I have seen it done.

sSQL = “Select distinctrow * From tbl_User_Setup Where UserName = ‘” & _
Me!txtUserName & “‘ and Password = ‘” & Me!txtPassword & “‘;”

Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

Although I don’t know why it wouldn’t work, you have an SQL query querying your query [test-alert]. Now the only problem you may have is that you placed brackets around test-alert. Try running it without the brackets. If that doesn’t work, try the above example.

Also, in the Do Loop, I don’t think you need the rs.MoveNext, but I am not sure. Have fun.

Reply | Quote

Lonnie:

The MoveNext method is needed, otherwise the Loop code will just hang out on the first record of the recordset. That is, if your name is the first name in the record set, without the MoveNext method, strEmail would end up as: Lonnie; Lonnie; Lonnie; Lonnie….and on and on and you never get to the end of the recordset (EOF = End Of File) ’cause you never leave the first record.

You’d think after having done this once you’d remember, but I’ve just spent nearly a full day wondering why my ASP page kept timing out only to find that I’d (mistakenly) deleted that all important MoveNext method. Oye!

Reply | Quote

Since you’re using Access 2000, the problem may be in your references. If you have the default ADO reference set, then you’ll get an error on the Set rs = line because that’s DAO syntax. If you have both ADO and DAO references set, you’ll probably still get an error because the ADO reference is still the default and Access isn’t sure which flavor of recordset you’re trying to open.

Go into the VB Editor (Alt+F11 is fastest), Select references from the Tools menu, and see if you have a DAO 3.6 reference set. If not, find it in the list and click on it to set the reference. Then go to your code and change it like this:

Dim rs As DAO.Recordset

The compile the project and see if everything compiles properly. If it does, try running your code. If you still have a problem, post back with the specifics.

Reply | Quote

Charlotte,
THAT fixed the SELECT line. The DAO 3.6 reference was not turned on. The subroutine continues and now halts on the last line (DoCmd.SendObject) with a runtine error 2282 “The formats that enable you to output data as a MS Excel, rich-text, MS-text, or HTML file are missing from the Windows Registry”
This one I don’t understand because I can manually output excel and HTML content though the DB without issue.
The rs strEmails loop is working correctly. When I mouse over the strEmails in the last line, the pop up window shows the content I’m looking for.

Reply | Quote

You forgot the output format argument:

DoCmd.SendObject acSendReport, "test-alert-report",acFormatDAP, strEmails 

See in the help-file for SendObject Method’s other formats

Reply | Quote

Thanks for the reply Francois. Adding the “acFormatDAP” argument did not have any corrective effect or change the
the Runtime error.

Reply | Quote

Do you try other formats ?
acFormatHTML
acFormatRTF
acFormatTXT
acFormatXLS
I just test it and acFormatDAP give me the same error but the other formats works fine.

Reply | Quote