none
Access 2010 Email PDF RRS feed

  • Question

  • I have some VBA code that turns a report into a PDF and emails it out.  It loops through a recordset to email invoices to multiple people.  It works great but there is one glitch.  I emailed about 20 of them out and the database crashed for some reason.  When I got it back up I had to start the entire process over again.  I would like to be able to update a record in a table when the email is sent so if I have to restart the process, I can start where I left off instead of starting again.  Any suggestions?  My code is below:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef
    Dim strTable As String
    Dim strWorksheetPath As String
    Dim stReport As String
    Dim strSQL As String


    Set db = CurrentDb
    Set qdf = db.QueryDefs("Invoice_Items_Email_Report")
    Set rst = qdf.OpenRecordset
    stReport = "Invoiceable_Items"

    Do While Not rst.EOF
        strRptFilter = "[INVOICE_NUMBER] = " & Chr(34) & rst![INVOICE_NUMBER] & Chr(34)
            DoCmd.OpenReport stReport, acViewPreview, , , acHidden
            Reports!Invoiceable_Items.Caption = rst![INVOICE_NUMBER]
            DoCmd.SendObject acSendReport, stReport, acFormatPDF, rst![EMAIL1], , , rst![INVOICE_NUMBER], "Please see attached invoice. ", True
            DoCmd.Close acReport, stReport, acSaveNo
           
        DoEvents
        rst.MoveNext
    Loop

    rst.Close

    Tuesday, February 4, 2014 1:44 PM