SendObject

[mlr0911]

I am trying to use the sendobject function to send emails. I am able to send the beginning record through email, but when I tell it to move to the next record, I still get the beginning record. Here is some code that I am working on. I tried writing a SQL statement to reference the "TO" field, but it didn't work correctly.

Dim xls As Object
Dim objrs As Recordset
Dim xlapp As Excel.Application
Dim xlbook As Excel.worksheet
Dim xlSheet As Excel.worksheet
Dim mysheetpath As String
Dim sql As String


DoCmd.SetWarnings False
sql = "SELECT DISTINCT qryOfficerEmail.[BI71_ADMIN_OFFICER_EMAIL]"
sql = sql + " from qryOfficerEmail"



'Execute query and export to C:\
'DoCmd.OpenQuery "Query1"
'DoCmd.OpenQuery "qryOfficerEmail"
'DoCmd.OutputTo acOutputTable, "tbltest", acFormatXLS, "C:\testfile.xls", False

Set rst = CurrentDb.OpenRecordset("tblOfficersEmail")
Do Until rst.EOF





'Will have to insert a loop here to go through the entire table
'DoCmd.SendObject acSendQuery, "qryOfficerEmail", acFormatXLS, [BI71_ADMIN_OFFICER_EMAIL], , , "Test Email", " if you have any questions, please contact TSS.", False
DoCmd.SendObject acSendTable, "tblOfficersEmail", acFormatXLS, BI71_ADMIN_OFFICER_EMAIL, , , "TEST", "TESTING", False
rst.MoveNext
Loop


DoCmd.SetWarnings True

'DoCmd.Quit


End Sub

 

[pbaldy]

It doesn't appear that you're using the value from the recordset. Try:

rst!BI71_ADMIN_OFFICER_EMAIL

 

[mlr0911]

Pbaldy..........thanks for your help.....your suggestion worked.

 

[mlr0911]

I'm trying to take this a step further.........by excluding information that does't match the user's email address. I created a new recordset that is being fed by a SQL. When I try to put "rst1!sql" I am getting an error message that is stating that "Item not found in this selection" or "Object required". I am trying to email a user specific information regarding their email address.

Here is the code that I am using:

Dim xls As Object
Dim objrs As Recordset
Dim xlapp As Excel.Application
Dim xlbook As Excel.worksheet
Dim xlSheet As Excel.worksheet
Dim mysheetpath As String
Dim sql As String

sql = "select qrytest.[account_number],qrytest.[bi71_admin_officer_email] "
sql = sql + "from qrytest "
sql = sql + "where qrytest.[BI71_ADMIN_OFFICER_EMAIL] = " & "'" & rst!BI71_ADMIN_OFFICER_EMAIL & "'" & ";"

DoCmd.SetWarnings False

'Execute query and export to C:\
'DoCmd.OpenQuery "Query1"
'DoCmd.OpenQuery "qryOfficerEmail"
'DoCmd.OutputTo acOutputTable, "tbltest", acFormatXLS, "C:\testfile.xls", False
Set rst = CurrentDb.OpenRecordset("tbltest")
Set rst1 = CurrentDb.OpenRecordset(sql, dbOpenDynaset)









Do Until rst.EOF

[COLOR="DeepSkyBlue"]I get the error message from below[/COLOR]

DoCmd.SendObject acSendQuery, rst1!sql, acFormatXLS, rst!BI71_ADMIN_OFFICER_EMAIL, , , "Transactions ", rst!BI71_ADMIN_OFFICER_NAME & ", please contact SS if more information is needed." & _
Chr(13) & Chr(13) & "Thanks," & Chr(13) & "TSS" & Chr(13) & Chr(13) & "Please note: this email has been automatically generated based off of Admin Officer name criteria.", False
rst.MoveNext
Loop


DoCmd.SetWarnings True

 

[pbaldy]

That's expecting a specific field:

rst1!FieldName

If you're trying to send info from that recordset, you can simply build the email body:

Variable = "Dear " & rst!FirstName & ", thanks for buying a " & rst!Product & ". Whatever"

 

[mlr0911]

Pbaldy, thanks for your reply.

What about referencing a different recordset. When I am using:

Set rst1 = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
sql = "select qrytest.[account_number],qrytest.[bi71_admin_officer_email] "
sql = sql + "from qrytest "
sql = sql + "where qrytest.[BI71_ADMIN_OFFICER_EMAIL] = " & "'" & rst!BI71_ADMIN_OFFICER_EMAIL & "'" & ";"


DoCmd.SendObject acSendQuery, rst1!sql, acFormatXLS...................

The rst1!sql is giving me the error messages stated above. What I am basically wanting to do is only send the information that is pertaining to their email address (thus the sql statement). Since there are several email address that have different information; a reciepiant doesn't need to see all of the other's information (hopefully I haven't confused you).

Thanks again for your help.

 

[pbaldy]

Well, I don't think SendObject can take a recordset for the query argument. Help states for that argument:

A string expression that's the valid name of an object of the type selected by the objecttype argument.

Which implies it would have to be a saved query. You can set a saved query to that SQL and use it if you want (search here on QueryDef), or use something like this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

Though it's about sending reports, you may be able to adapt the techniques to your needs.

 

[pbaldy]

It occurs to me that you don't need to change the SQL of a query, since your SQL is not dynamic. Create a saved query that gets its criteria from a form control. In your code, set that control to your recordset value, then send that saved query.