Can I send an email from access that takes some data from a report

[FeatherDust]

I have an employee productivity database. Off a query I run a report that has a summary line for each employee.



I would like to send an email to each employee that includes some of the fields from the report. The rest of the email would be static.

Can this be done and if so how complicated is it to do?

Thanks

[FeatherDust]

bump......

[RuralGuy]

You can not retrieve any data from a report. It is just a piece of paper as far as the computer is concerned.

[FeatherDust]

Thats what I was afraid of?

Then to generate the emails do you think I would be better off exporting the results to an excel spreadsheet, or dumping the results into a separate database.

Thanks

[RuralGuy]

It seems to me that you should be able to get the results you want without using Excel.

[dbDamo]

You can use the reports source query. Here is the code I am currently using, adapt it to suit your needs:-

 
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("6g Daily Email Query", dbOpenSnapshot)
 
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(0)) = False Then
sToName = .Fields(0)
sSubject = "Todays Testing for " & .Fields(1)
sMessageBody = "Below is a listing of testing work for you Today. If you need a detailed listing of tests, please contact the Testing Team who will provide this.  " & vbCrLf & _
                    "Number of Tests Due to Complete Today: " & rs.Fields(2) & vbCrLf & _
                    "Number of Tests Due to Start Today: " & rs.Fields(3) & vbCrLf & _
                    "Number of Tests Past Planned Completion Date: " & rs.Fields(4) & vbCrLf & _
                    "Number of Tests Past Planned Start Date: " & rs.Fields(5) & vbCrLf & _
                    "Number of Tests Due to Start Tomorrow Prep Not Complete: " & rs.Fields(6)
 
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
 
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 3021 Then
MsgBox "There are no records to send"
End If

[FeatherDust]

I have next to know expeience using VBA but have managed to get your code to work by some trial and error. I do have one question though.

For the variables in the email you use

rs.Fields(#)

In order to get it to work I had to take out the "rs".

What is the "rs" supposed to do?

Thanks

[dbDamo]

My apologies, rs should have read rsEmail, it does nothing except refer to the recordset. It isn't needed in your code, I now send my email in a slightly different way, so I do need it in mine.

[FeatherDust]

Thanks for all your help. I really appreciate it.

Now if I want a user to be able to run the code, what is the best way to set it up so they can do it.

[dbDamo]

Create a command button on a form and place the code in the command buttons On Click event.

Do you require the ability to disable the button? i.e. will multiple users be able to press this button? If so, would you be happy with each user sending the same emails, possibly multiple times each day?

This could result in recipients receiving the same email multiple times a day.

I only wanted my email to be sent once a day, so I have some code that disables the button after it is pressed until the next day.