Hi All,
I have a field in a table that holds file paths to PDFs of invoices, I am trying to put some code behind a button that attaches the invoices that have not yet been sent to the office, also there is a report that I would like attached to the same email. I have the code below but it is only attaching the first invoice (from the QueryFiles query)....I'm losing the will to live so any help would be greatly appreciated! It saves the report and loops through the records but just not sure how to put all the paths together into the email..?
I have a field in a table that holds file paths to PDFs of invoices, I am trying to put some code behind a button that attaches the invoices that have not yet been sent to the office, also there is a report that I would like attached to the same email. I have the code below but it is only attaching the first invoice (from the QueryFiles query)....I'm losing the will to live so any help would be greatly appreciated! It saves the report and loops through the records but just not sure how to put all the paths together into the email..?
Code:
Private Sub Officebtn_Click()
On Error GoTo ErrorHandler
Dim nrecords As String
Dim recip As String
Dim msg As String
Dim subj As String
Dim Offname As String
Dim Capt As String
Dim Repname As String, todayDate As String
Dim afiles As String
Dim rs As DAO.Recordset
nrecords = DCount("*", "OfficeInvoices")
recip = DLookup("OfficeEmail", "tblLogo", "ID=1")
Capt = DLookup("Captain", "ONCaptain", "Position=1")
msg = "Dear " & Offname & "," & vbNewLine & vbNewLine & _
"Please find attached the latest invoices for payment." & vbNewLine & vbNewLine & _
"Kind Regards" & vbNewLine & Capt
Offname = DLookup("OfficeRecipiant", "tblLogo", "ID=1")
subj = "Invoices for payment as of " & Format(Date, "Long Date")
If nrecords > 0 Then
todayDate = Format(Date, "MMDDYYYY")
Repname = Application.CurrentProject.Path & "InvoiceForOffice" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "InvoiceForOffice", acFormatPDF, Repname, False
Set rs = CurrentDb.OpenRecordset("SELECT * FROM QueryFiles")
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
afiles = rs!InvoiceAddress
rs.MoveNext
Loop
Call SendEmail(recip, subj, msg, True, , afiles)
DoCmd.SetWarnings False
DoCmd.OpenQuery "SentInvoices"
DoCmd.SetWarnings True
Else
MsgBox "All invoices have already been exported", vbInformation, "No invoices to export"
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End If
ErrorHandler:
If Err.Number <> 0 And Err.Number <> 2501 Then
MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
Exit Sub
End If
End Sub