Send Email

[tcheck]

We currently use Access to send emails that have information and query's or reports attached to them. I would like to use the text of a query and put it in the subject or message box of the email instead of sending a report or query. Can this be done?

[Bulzie]

I think you can use Docmd.SendObject and just omit the optional parameters(lookup DoCmd.SendObject online)? Maybe like:

DoCmd.SendObject ,, ,"SendTo", , , Me.YourSubject, Me.YourBodyMessage, , False

Also if you go to Forum, Code Repository on this site, you should be able to find some email code examples.

[June7]

What do you mean by the 'text of a query' - data from fields? For only one record? Code can concatenate data with literal text to construct a string for use as email subject and/or body. If a form is open to the desired record, it is simple for code behind form to reference bound controls. As Bulzie said, common topic - search forum and Google.

[tcheck]

This is my current code;

If Me.Dirty Then Me.Dirty = False
LResponse = MsgBox("An email will be sent to operations of a termination.", vbYes, "Employee Termination Notice")
DoCmd.SendObject acQuery, "qry_EmployeeTerminationNotice", "Excel97-Excel2003Workbook(*.xls)", "timt@gmail.com", "", "", "Termination Notice", "", True, ""

Instead of the users having to open up an Excel file, I would like the information in the query (which is (John Smith has been terminated on 6/1/2017) in the message portion of the email.

[Bulzie]

You could do a DLookup maybe to get that statement.

vTermText = DLookup("[YourTextField]", "qry_EmployeeTerminationNotice")

DoCmd.SendObject acQuery, "qry_EmployeeTerminationNotice", "Excel97-Excel2003Workbook(*.xls)", "timt@gmail.com", "", "", "Termination Notice", vTermText, True, ""

[tcheck]

Thank you, it looks very promising. I am out of the office today but will be back on Monday to try the code. Will it also send the file?

[Bulzie]

Maybe something like below. You might have to test it and revise as needed or others my spot if something needs to be changed.

vTermText = DLookup("[YourTextField]", "qry_EmployeeTerminationNotice")

DoCmd.SendObject acSendQuery, "qry_EmployeeTerminationNotice", acFormatXLS, "timt@gmail.com", "", "", "Termination Notice", vTermText, True, ""

[tcheck]

This worked freat for the project that just had one line of code in it, now they want several (75 or so) to be sent with the email. When using the above code, I get the first line item, that is it.

[June7]

Options:

1. SendObject sends query or report as PDF attachment

2. VBA opens recordset object, loops recordset to concatenate a very long string of data as the body text for SendObject

[Bulzie]

Loop through your query to send all the emails. See the change in bold where you get the address from the query as move through each record. Syntax might not all be right in that code but that's the idea.

Dim db as database, rs1 as Recordset
Set db = Currentdb
Set rs1 = db.OpenRecordset("qry_EmployeeTerminationNotice")

rs1.movefirst

Do Until rs1.eof

vTermText = DLookup("[YourTextField]", "qry_EmployeeTerminationNotice")
DoCmd.SendObject acSendQuery, "qry_EmployeeTerminationNotice", acFormatXLS, rs1!EmailAddress", "", "", "Termination Notice", vTermText, True, ""

rs1.moveNext

Loop

[June7]

If you need a number of records combined for one email, the message body concatenation would be within a loop but the SendObject would be outside that loop.

Do you want to send just one email or do you want to send a batch of emails to multiple addresses? And each email has data from multiple records? You might need multiple queries and multiple loops to accomplish what you want. Just need a better understanding of what that is.

[tcheck]

Thanks again. I just need to send about 25 - 75 different items to one or two email address.

[tcheck]

When I get to this line item I get a Run-Time Error 13
Type mismatch.

[June7]

When you get to what line item? With what code?

[tcheck]

Set rs1 = db.OpenRecordset("qry_EmployeeTerminationNotice")