I am trying to get an invoice to email selected contacts from a customer for a specific invoice with tracking numbers and a total number of boxes.
Everything is working expect when I try to get the tracking numbers to "coalesce" (well, as I know it) so I can have them display in the body of the email. After I get that to work I then will need to get the # of boxes to be counted by counting the number of tracking numbers.
I have underlined the code with the error. The error says it expected 2 parameters "Too few parameters. Expected 2. (Error 3061)". Maybe I need to change the dbOpenDynaset?
My eventprocedure is below:
Private Sub Command79_Click()
Dim SampleData2 As DAO.Database
Dim rstEmail As DAO.Recordset
Dim strSQL As String
Dim rstTracking As DAO.Recordset
Dim strTracking As String
Dim tmpTrackingCount As Integer
Dim tmpID As String
Dim tmpPO As String
Dim tmpEmailCount As Integer
Dim tmpCustContact As String
Dim tmpCustEmail As String
Dim tmpTracking As String
Dim tmpParcels As String
Dim tmpEmailBody As String
Dim tmpSubject As String
Dim stDocName As String
stDocName = "Invoices"
tmpEmailCount = 0
tmpTrackingCount = 0
Set SampleData2 = CurrentDb
tmpID = [Forms]![Invoices]![ID]
strSQL = "SELECT Invoices.ID, Contacts.EmailAddress, Contacts.Contact, Contacts.EmailInvoice FROM Invoices INNER JOIN Contacts ON Invoices.CompanyID = Contacts.CompanyID WHERE (((Invoices.ID)=" & [tmpID] & ") And ((Contacts.EmailInvoice)=Yes))"
Set rstEmail = SampleData2.OpenRecordset(strSQL, dbOpenDynaset)
'Set Customer ID
Do Until rstEmail.EOF = True
If tmpEmailCount = 0 Then
tmpCustEmail = rstEmail!EmailAddress
tmpCustContact = rstEmail!contact
tmpEmailCount = tmpEmailCount + 1
Else
tmpCustEmail = tmpCustEmail & "," & rstEmail!EmailAddress
tmpEmailCount = tmpEmailCount + 1
End If
rstEmail.MoveNext
Loop
strTracking = "SELECT Invoices.ID, ShippingUPS.UPSTrackingNumber, ShippingUPS.NumberOfBoxes FROM Invoices INNER JOIN Contacts ON Invoices.CompanyID = Contacts.CompanyID WHERE (((Invoices.ID)=" & [tmpID] & ") And ((ShippingUPS.UPSTrackingNumber)>0))"
Set rstTracking = SampleData2.OpenRecordset(strTracking, dbOpenDynaset)
'Set Customer ID
Do Until rstTracking.EOF = True
If tmpTrackingCount = 0 Then
tmpTracking = rstTracking!UPSTrackingNumber
tmpTrackingCount = tmpTrackingCount + 1
Else
tmpTracking = tmpTracking & "," & rstTracking!UPSTrackingNumber
tmpTracking = tmpTracking + 1
End If
rstTracking.MoveNext
Loop
tmpPO = [Forms]![Invoices]![PONo]
'Set Subject
tmpSubject = "Invoice# " & tmpID & " / PO# " & tmpPO & " - for your reference"
'Set Email Body
tmpEmailBody = "Hi " & tmpCustContact & "!" & vbNewLine & " Please find attached a PDF of Invoice# " & tmpID & " / PO# " & tmpPO & " for your reference. We have included your tracking numbers if they are available. " & vbNewLine & UPSTrackingNumber & tmpTracking
If tmpEmailCount <> 0 Then
'Send Email
DoCmd.Close acReport, stDocName
DoCmd.OpenReport stDocName, acViewPreview, , "[ID] = [Forms]![Invoices]![ID]", acHidden
Reports!Invoices.Caption = "Invoice #" & [Forms]![Invoices]![ID] & " " & [Forms]![Invoices]![CustomerAddresses].Form![Attention] & " " & [Forms]![Invoices]![PONo]
DoCmd.SendObject acSendReport, stDocName, acFormatPDF, tmpCustEmail, , , tmpSubject, tmpEmailBody, True
Else
End If
End Sub