doCmd.SendObject .... Can't get it to work!!!

[mrussell71]

I'm at the end of my rope on this problem. I have a form that allows a user to select a manufacturer from a combo box [mfg] and then select what type of records to view/send, corrected or uncorrected, via checkboxes. I have queries that are run based upon the checkbox selections above, which will be the .xls object in the sendobject command.

The Problem that I am having is in supplying the To: field in Outlook from the doCmd.sendobject line. I have tried using a recordset and a querydef but keep getting an "Too few parameters. Expected 2." error. This error is getting thrown at the set rst = db. line. What I would like the code to do is to supply the receipent based upon the SQL statement and insert that email address into Outlook's To: field.

Here is a sample of the code I am using:
[tt]
Private Sub e_mail_but_Click()
On Error GoTo Err_e_mail_but_Click

Dim db As Database
Dim rst As Recordset
Dim SQL As String
Dim varEmailTo As Variant
Dim strQryName As String

Set db = CurrentDb()

If uncorrected_chk = -1 And corrected_chk = 0 Then
strQryName = "qry_corr_info_by_mfg_corrected"

DoCmd.OpenQuery strQryName, acViewNormal, acReadOnly

SQL = "SELECT First(qry_corr_info_by_mfg_corrected.email_addr_to) AS email_addr_to FROM qry_corr_info_by_mfg_corrected;"

Set rst = db.OpenRecordset(SQL)
rst.MoveFirst

MsgBox "My strEmailTo value is... " & rst![email_addr_to]

DoCmd.SendObject acSendQuery, strQryName, acFormatXLS, rst![email_addr_to], , , "Catalog Correction Report", , -1

rst.Close[/tt]

Any help is greatly appreciated.

 

[KyleS]

Hi,

SQL is a reserved word in Access, so try making your variable name "strSQL". Try that and see if you still get the parameter errors.

If you still do then check your Query "qry_corr_info_by_mfg_corrected" to find where it's looking for the parameters.

Hope this helps,
Kyle :