Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Unanswered: Report Loop Stuck on First Record

    Hello

    Could someone help with the following? I have the following code which should split a report and e-mail it.

    The code correctly splits the pdf to the first record and then opens an email with the correct To/ Subject/ Body however after sending the first file the second email opens with the next To/ Subject/ Body but the attached still relates to the first record its 'looping' the contacts but not the report.

    Any ideas?

    Thanks
    Junkbot

    Code:
    Private Sub Command3_Click()
        Dim dbName As Database
        Dim rst As Recordset
        Dim lRecNo As Long
        Dim lBillCnt As Long
        Dim zWhere As String
        Dim zMsgBody As String
        Dim zEmail As String
        Dim zSubject As String
        Dim zDocname As String
        zDocname = "rptFSMMonthly"
        Set dbName = CurrentDb()
        Set rst = dbName.OpenRecordset("qryReportData", dbOpenDynaset)
        rst.MoveFirst
    
        lBillCnt = 0
      
        Do While Not rst.EOF
    
            If rst![contact_email] <> "" Then
    
                zWhere = "[school_code] = " & (rst![school_code])
                DoCmd.OpenReport zDocname, acPreview, , zWhere
                zEmail = rst![tblHbschool.contact_email]
                zSubject = "Meals Report for : " & rst![school_name]
                zMsgBody = "Dear " & rst![contact_name] & vbCrLf & "Please find your attached." & vbCrLf & "Regards"
                DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
    
                lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
            End If
    
            rst.MoveNext        '*** Move to Next Record ***
        Loop
    
        MsgBox Format(lBillCnt, "#,###") & " Emails Created."
        Set rst = Nothing     '*** Close RecordSet ***
    
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Never used the acformatPDF

    Could it be that as the PDF File has been created its just sending that one again.
    your coding look OK

    What i do know is when you PDF a file it uses the Caption report name as its file name

    So you could change Caption name in the Report on the Fly
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Aug 2011
    Posts
    10
    I have fixed this.

    Thanks
    Last edited by junkbot; 04-17-15 at 08:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •