Split MS-Access reports into separate emails

[beginneraccess]

Hi,

I am trying to send separate Employees a PDF of their section/page of their report. The information is based on their EmployeeID. So each person has their balance information on a page then there's a page break, and then next page shows the next person's details. With the code below, it does email each of the employees one page but it so happens to only email the first person's page to EVERYONE. Is it possible to somehow automate so that each user is emailed his/her individual page of the report?

Another error is that the email pop up one by one so I have to press send each time for over 200 people, and that the email seems to be sending to the email but then followed by #mailto:the email# for example [e-mail]#mailto:[e-mail]#

I just started Access and have been copying and scraping code off of places I have found online. Many thanks in advance, if you can assist!

Have a great day!

Option Compare Database
Option Explicit
 
Private Sub cmdSendAll_Click()
 
Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String
 
Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [email] FROM [queAutoUpdate]", dbOpenSnapshot)
 
Debug.Print strTo
 
With rsAccountNumber
 
Do Until .EOF
 
DoCmd.OpenReport "test", _
acViewPreview, _
WhereCondition:="EmployeeID = '" & !EmployeeID & "'", _
WindowMode:=acHidden
 
strTo = ![email]
strSubject = "Updated Balance "
strMessageText = "Text Here"
 
 
DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="test", _
OutputFormat:=acFormatPDF, _
To:=strTo, _
Subject:=strSubject, _
MESSAGETEXT:=strMessageText, _
EditMessage:=True
 
 
DoCmd.Close acReport, "test", acSaveNo
 
 
.MoveNext
 
Loop
 
.Close
 
End With
End Sub

[PhilOfWalton]

It's only a guess, but if EmployeeID is a long number (or AutoNumber which is also a long number), line 21 should read

WhereCondition:="EmployeeID = " & !EmployeeID, _

The single quotes would only be used if EmployeeID were text.

Phil

[beginneraccess]

Thanks Phil

Unfortunately EmployeeID is a text

[twinnyfo]

What you want to do cannot be done using either OpenReport or SendObject, due to the nature of how those functions work.

You must establish a public variable that you can refer to in order to filter the report as it is generated, then send the report. This has been done multiple times on this forum, and I am unable to find that particular post. I can try to address this later, as I am currently unable to sit and create the code for you.

I can check back later and work through this.

[beginneraccess]

twinnyfo

Thanks for the reply! Someone informed me to change

DoCmd.Close acReport, "test", acSaveNo.

and

EditMessage:=False

And it works! Yay!

Thank you for your time.

[twinnyfo]

I’m glad that you found a solution, even though I don’t see how it can work, since you are never saving the filtered report and you are not filtering the report you actually send. But if you are satisfied, so am I.