Right now the unique identifier is "Name", but I'll probably add employee #. The email address is a field called "Manager Email" in the same query.
Right now the unique identifier is "Name", but I'll probably add employee #. The email address is a field called "Manager Email" in the same query.
Okay, then AFAIK the code should work. If you want to provide db for analysis, follow instructions at bottom of my post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
You really should rename all objects to remove spaces and special characters.
A name like "01 - 03 - Commission Summary Report - Sage One".... well, a better name might be "01_03CommSumRpt_SageOne" .
The only person that should see the object names should be the programmer.
In my dB, I print a report to PDF using only the OutPutTo command. I do not open it in preview mode first. No problems.
Using your code from post #9, I made a few changes (in blue):
Code:Option Compare Database '<<= every code page should have this line Option Explicit '<<= every code page should have this line Public Sub Command21_Click() Dim db As DAO.Database Dim rst As DAO.Recordset Dim sTo As String Dim sSub As String Dim sBody As String Dim strRep As String Dim strDPath As String Dim strFName As String Set db = CurrentDb() Set rst = db.OpenRecordset("SELECT * FROM [01 - 03 - Commission Summary Report - Sage One];", dbOpenSnapshot) If (rst.RecordCount <> 0) Then rst.MoveLast rst.MoveFirst With rst While Not .EOF ' What report to send strRep = "01 - 01 - Commission Summary Report - Sage One" ' Initial Path strDPath = "C:\Users\DLSmith\Documents\Commissions Project\Reports Sent\" ' Filename strFName = .Fields("Name") & "_Order_" & .Fields("Month") & .Fields("Year") & "_" & Format(Date, "mm-dd-yyyy") 'add file extension strFName = strFName & ".pdf" ' Output report as pdf ' DoCmd.OpenReport strRep, acViewPreview, "[Name] ='" & rst!Name & "'" ' DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0 DoCmd.OutputTo acOutputReport, strRep, acFormatPDF, strDPath & strFName, False, "", 0 ' DoCmd.Close acReport, strRep ' Send the report to whoever sTo = .Fields("Manager Email") sSub = .Fields("Name") & "_Commission Statement_" & .Fields("Month") & "_" & .Fields("Year") '"Name", "Month" & "Year" are reserved words in Access sBody = "First name from some field" & vbCrLf & vbCrLf sBody = sBody & "Continue with message" Send_Email strDPath & strFName, sTo, sSub, sBody .MoveNext Wend End With Else MsgBox "There are no records in the table!" End If rst.Close Set rst = Nothing Set db = Nothing End Sub
Thanks again for your input. I've tried making all the recommended changes, but the report is still returning 5 pages (5 names). I'm attaching the database so you guys can take a closer look and hopefully help me figure this out.
Steve - I tried using your code, but was getting errors. Maybe if you look at the attached DB, you can see where I went wrong.
thanks!
My apologies in advance. I do not mean to burn you, but this dB table structure needs a lot of work.
I changed most of the fields that used reserved words (Name, Month, Year, Group); most because I just saw another field "Group" I missed.
Here is a list of reserved words: http://www.allenbrowne.com/AppIssueBadWord.html
- Object names should only be letters, numbers and maybe the underscore. NO spaces, punctuation or special characters.
- Object names should not begin with a number.
- These two lines should be at the top of every code page:
"Option Explicit" ensures all variables are declared. It helps keep you from having a variable named "MyName" then typing "MyMane".Code:Option Compare Database '<<= every code page should have this line Option Explicit '<<= every code page should have this line
It took me a while to understand what you were asking. You want to email only the data that is for one person, not email everyone's data to everyone.The report is a 5 page report, one for each row of data in the query.
I'm trying to use the code to go through the data one row at a time, creating a report that is one page instead of 5. Then email this one page.
You need to filter on "Sales Representative" just like you filter on Group (department).
So there are two methods to accomplish this task.
1) Use VBA to modify the first query. Code would change the SQL of the query on-the-fly.
2) Put an unbound text box on a form, put the person's name in the text box and have to query reference the text box.
I used method two. Then I re-wrote your code and I have tested the code up to actually emailing the saved PDF.
You have variables that didn't change inside a loop. Not necessary, so I moved them above the start of the loop.
Added error handling and got rid of the "Go To". Except for the error handler, should never use "Go To".
Anyway, test the dB. Hope you can use at least some or it.......
Good luck with your project..
WOW! It works great. Thank you so much. I feel like I've learned a ton on this project. I can't tell you how much I appreciate your help. Thanks for all the advice on the db structure as well. I'm self taught, so I'm sure there are several areas where I need to improve.