By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,638 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,638 IT Pros & Developers. It's quick & easy.

Split MS-Access reports into separate emails

P: 27
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 email@email.com#mailto:email@email.com#

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!


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSendAll_Click()
  5.  
  6. Dim rsAccountNumber As DAO.Recordset
  7. Dim strTo As Variant
  8. Dim strSubject As String
  9. Dim strMessageText As String
  10.  
  11. Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [email] FROM [queAutoUpdate]", dbOpenSnapshot)
  12.  
  13. Debug.Print strTo
  14.  
  15. With rsAccountNumber
  16.  
  17. Do Until .EOF
  18.  
  19. DoCmd.OpenReport "test", _
  20. acViewPreview, _
  21. WhereCondition:="EmployeeID = '" & !EmployeeID & "'", _
  22. WindowMode:=acHidden
  23.  
  24. strTo = ![email]
  25. strSubject = "Updated Balance "
  26. strMessageText = "Text Here"
  27.  
  28.  
  29. DoCmd.SendObject ObjectType:=acSendReport, _
  30. ObjectName:="test", _
  31. OutputFormat:=acFormatPDF, _
  32. To:=strTo, _
  33. Subject:=strSubject, _
  34. MESSAGETEXT:=strMessageText, _
  35. EditMessage:=True
  36.  
  37.  
  38. DoCmd.Close acReport, "test", acSaveNo
  39.  
  40.  
  41. .MoveNext
  42.  
  43. Loop
  44.  
  45. .Close
  46.  
  47. End With
  48. End Sub
Feb 13 '19 #1
Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
It's only a guess, but if EmployeeID is a long number (or AutoNumber which is also a long number), line 21 should read
Expand|Select|Wrap|Line Numbers
  1. WhereCondition:="EmployeeID = " & !EmployeeID, _
The single quotes would only be used if EmployeeID were text.

Phil
Feb 13 '19 #2

P: 27
Thanks Phil

Unfortunately EmployeeID is a text
Feb 14 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
Feb 14 '19 #4

P: 27
twinnyfo

Thanks for the reply! Someone informed me to change

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acReport, "test", acSaveNo.
and
Expand|Select|Wrap|Line Numbers
  1. EditMessage:=False
And it works! Yay!

Thank you for your time.
Feb 15 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
Feb 15 '19 #6

Post your reply

Sign in to post your reply or Sign up for a free account.