Send Email

[June7]

Try:



Dim db As DAO.Database, rs1 As DAO.Recordset

Post your complete procedure. If you are using Bulzie's example, I do not understand the use of DLookup() on a query object when there is a recordset object of the same data. Also, that DLookup does not have WHERE criteria so it will simply return the first value it encounters in the specified field.

If you need to combine multiple records, then concatenate within a loop. Something like:

While Not rs.EOF
strbody = strBody & rs!field1 & ", " & rs!field2 & ", " & rs!field3 & vbCrLf
rs.MoveNext
Wend
strbody = "Some introductory text here." & vbCrLf & strbody

DoCmd.SendObject acSendNoObject, , , "timt@gmail.com", , , "subject", strBody, True

[tcheck]

Here is the code;

Dim db As Database, rs1 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("qry_my_Query")


rs1.MoveFirst


Do Until rs1.EOF


vTermText = DLookup("[Symbol]", "qry_my_Query")
DoCmd.SendObject acSendQuery, "qry_my_Query", "Excel97-Excel2003Workbook(*.xls)", "email@emailcom", "", "m", "Field", vDailyActivity, True, ""


rs1.MoveNext
Loop

[Bulzie]

tcheck just to verify, qry_my_Query has name and date of people who have been terminated correct? And you want to loop though this query and send an email for each person in the list? Do you still want to also send the excel file of this list as an attachment or just the text in the body of the message? How often do you run this as you don't want to repeat sending term emails for same people right?

June7 is right, if looping using my code you don't need the vTermText line (that was for the 1 time email example), you can get the data from the rs1 record for each loop. Maybe try June7 code if you still having issues.

[tcheck]

The query will run daily and will have the list in the body of the email as well ass an Excel file. The query has changed slightly,, it's not terminated employees, it's a list of items of new inventory. I re-ran the code and it still stops at

Set rs1 = db.OpenRecordset("qry_my_query")

Error 13 Type mismatch


With the below code.

Dim db As Database, rs1 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("qry_my_query")




rs1.MoveFirst




Do Until rs1.EOF




vTermText = DLookup("[Symbol]", "qry_my_query")
DoCmd.SendObject acSendQuery, "qry_my_query", "Excel97-Excel2003Workbook(*.xls)", "my@email.com", "", "", "Symbol", vDailyActivity, True, ""




rs1.MoveNext
Loop

[Bulzie]

Is "qry_my_query" the actual query? Can you run it manually with no errors?

Code should be something like:


Dim db As Database, rs1 As Recordset '(or you can try June7 DAO line: Dim db As DAO.Database, rs1 As DAO.Recordset)

Set db = CurrentDb
Set rs1 = db.OpenRecordset("qry_my_query")

rs1.MoveFirst

Do Until rs1.EOF

DoCmd.SendObject acSendQuery, "qry_my_query", "Excel97-Excel2003Workbook(*.xls)", "my@email.com", "", "", "Symbol", "Put text here " & rs1!Symbol, True, ""
rs1.MoveNext

Loop

Change my@email.com to a valid one. Also after the text, put whatever field you need to display, I picked rs1!Symbol. In testing, maybe put your email address to see if it is working.

[tcheck]

The name of the query is different to protect the client but the query does ope without issue.



Code should be something like:


THere is an actual email address in the code.
Change my@email.com to a valid one. Also after the text, put whatever field you need to display, I picked rs1!Symbol. In testing, maybe put your email address to see if it is working.

[Bulzie]

So if you compile the code above, do you still get the error? If so add the DAO. like June7 example to the Dim line and see if it compiles.