424,279 Members | 1,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Split MS-Access reports into separate emails

P: 13
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
5 Days Ago #1
Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,424
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
5 Days Ago #2

P: 13
Thanks Phil

Unfortunately EmployeeID is a text
5 Days Ago #3

twinnyfo
Expert Mod 2.5K+
P: 2,886
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.
5 Days Ago #4

P: 13
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.
4 Days Ago #5

twinnyfo
Expert Mod 2.5K+
P: 2,886
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.
4 Days Ago #6

Post your reply

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