Results 1 to 2 of 2
  1. #1
    tuggleport is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Location
    CA
    Posts
    7

    Thumbs up Email report while pulling and embedding and "combining" fields into email body

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The 3061 error can be because of spelling error or typo. Don't know if that's true here but you may want to check names in the sql against the names in the tables.

    Also, tmpId is dimmed as a string, so you will need quotes in the sql

    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))"
    For debugging purposes, you may wish to add a line that will print the value of your sql string to the immediate window. That will show you what Access is using.

    Debug.Print strSQL could go right before
    Set rstEmail = SampleData2.OpenRecordset(strSQL, dbOpenDynaset)
    You could put a
    Debug.Print strTracking right before

    Set rstTracking = SampleData2.OpenRecordset(strTracking, dbOpenDynaset)
    You will see the contents of the string in the immediate window.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using ListBox value as "Body of outlook task"
    By AccessFreak in forum Database Design
    Replies: 3
    Last Post: 05-07-2011, 04:40 PM
  2. Email from report to Email body
    By Ehmke66 in forum Programming
    Replies: 4
    Last Post: 01-03-2011, 01:06 PM
  3. Using InfoPath under "Create Email" wizard
    By Alex in forum Import/Export Data
    Replies: 0
    Last Post: 07-21-2010, 07:30 AM
  4. Export Contents of a Report into Email Body
    By Nosaj08 in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 09:05 AM
  5. Email field name into subject/body?
    By Stanggirlie in forum Access
    Replies: 0
    Last Post: 01-02-2009, 11:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums