Adding loop to existing code

[smithdam]

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.

[June7]

Okay, then AFAIK the code should work. If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

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):

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

[smithdam]

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.

[smithdam]

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!

[ssanfu]

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 Compare Database    '<<= every code page should have this line
Option Explicit              '<<= every code page should have this line

"Option Explicit" ensures all variables are declared. It helps keep you from having a variable named "MyName" then typing "MyMane".

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.

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.

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..

[smithdam]

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.