Send Email from a Form...Help!

[mrussell71]

I have a form from which I want to create an XLS report and send the report to an email contained within the .xls report.
The action that I would like to happen "on click" is:
1. Run a query based upon a combo box selection and based upon a selecton of check boxes on the form. [mfg], [uncorrected_chk], and [corrected_chk]
2. Do a SendObject command, with the the command sending the query as a .xls file.
3. Open the email client (outlook) and place the [email_addr_to] field from the query in the To: box.

This is where I am having trouble. I cannot figure out how to populate the To: field with the [email_addr_to] field from the query.

Here is the code:
Private Sub e_mail_but_Click()
On Error GoTo Err_e_mail_but_Click

Dim strEmailTo As String
Dim strQryName As String

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

DoCmd.OpenQuery strQryName, acViewNormal, acReadOnly

strEmailTo = "email_addr_to"

DoCmd.SendObject acSendQuery, strQryName, acFormatXLS, strEmailTo, , , "Catalog Correction Report", , -1

ElseIf uncorrected_chk = 0 And corrected_chk = -1 Then
strQryName = "qry_corr_info_by_mfg_uncorrected"

DoCmd.OpenQuery strQryName, acNormal, acReadOnly

strEmailTo = "email_addr_to"

DoCmd.SendObject acSendQuery, strQryName, acFormatXLS, strEmailTo, , , "Catalog Correction Report", , -1

ElseIf uncorrected_chk = -1 And corrected_chk = -1 Then
strQryName = "qry_corr_info_by_mfg_both"

DoCmd.OpenQuery strQryName, acNormal, acReadOnly

strEmailTo = "email_addr_to"

DoCmd.SendObject acSendQuery, strQryName, acFormatXLS, strEmailTo, , , "Catalog Correction Report", , -1

End If

Exit_e_mail_but_Click:
Exit Sub

Err_e_mail_but_Click:
MsgBox Err.Description
Resume Exit_e_mail_but_Click

End Sub

I have tried a couple of different variations of the to: variant. But can't seem to get it to work. How should I instantiate the strEmailTo variable?

Any help would be appreciated.

 

[BoxHead]

I'm not sure if you're getting no email address in the message, an error, or an email being sent to email_addr_to.

Thinking you should change

  strEmailTo = "email_addr_to"

to

  strEmailTo = """" & [email_addr_to] & """"

If that doesn't work, try throwing in a temporary messagebox before the DoCmd.SendObject to see what value you are passing. EG:

  MsgBox "My strEmailTo value is... " & strEmailTo

HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[Trainingjason]

In one of the forums I used some one posted an email database. I will send you a copy if you email me

Trainingjason@aol.com

 

[mrussell71]

I'm still having difficulty with this issue. With the following code changes I am getting this error message, "Too few parameters. Expected 2". This error is getting thrown at the "Set rst = db.OpenRecordset(SQL)" line.
I've been unable to find any help at the knowledge base with this issue, any help would be appreciated.

Code:[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 FirstOfemail_addr_to FROM qry_corr_info_by_mfg_corrected;"

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

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

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

rst.Close

ElseIf uncorrected_chk = 0 And corrected_chk = -1 Then
strQryName = "qry_corr_info_by_mfg_uncorrected"

DoCmd.OpenQuery strQryName, acNormal, acReadOnly

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

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

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

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

rst.Close

ElseIf uncorrected_chk = -1 And corrected_chk = -1 Then
strQryName = "qry_corr_info_by_mfg_both"

DoCmd.OpenQuery strQryName, acNormal, acReadOnly

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

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

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

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

rst.Close

End If

db.Close

Exit_e_mail_but_Click:
Exit Sub

Err_e_mail_but_Click:
MsgBox Err.Description
Resume Exit_e_mail_but_Click

End Sub[/tt]