Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Problem with sending report via email

    I’m currently using this function to send the text of my report via email. However, it is being attached as an attachment to the email. Is there a way to just past the data in the email itself, instead as an attachment?

    Thank you
    Joe

    Private Sub cmdExportToEmail_Click()
    On Error GoTo Err_cmdExportToEmail_Click

    Dim stDocName As String
    Dim txtorderNo As String
    txtorderNo = Me.OrderID
    stDocName = "rptExport"
    ' DoCmd.OpenReport stDocName, acPreview
    DoCmd.SendObject acSendReport, stDocName, acFormatTXT, "me@company.com", , , "Order ID# " & txtorderNo

    Exit_cmdExportToEmail_Click:
    Exit Sub

    Err_cmdExportToEmail_Click:
    MsgBox Err.Description
    Resume Exit_cmdExportToEmail_Click

    End Sub

  2. #2
    Join Date
    Jan 2007
    Posts
    5
    I use the following:

    Code:
    Private Sub SendMailButton_Click()
    
    Dim Olk As Outlook.Application
    Set Olk = CreateObject("Outlook.Application")
    
    Dim OlkMsg As Outlook.MailItem
    Set OlkMsg = Olk.CreateItem(olMailItem)
    
    With OlkMsg
    
    Dim OlkRecip As Outlook.Recipient
    Set OlkRecip = Recipients.Add(Me![MsgAddress])
    OlkRecip.Type = olTo
    .Subject = Me![MsgSubject]
    .Body = Me![MsgBody]
    .Send
    
    End With
    
    Set Olk = Nothing
    Set OlkMsg = Nothing
    Set OlkRecip = Nothing
    
    End Sub
    Something like that.

  3. #3
    Join Date
    Nov 2004
    Posts
    78
    This line is my main problem.
    .Body = Me![MsgBody]

    How do you get all your data into the MsgBody?

    I want the layout of the email to be the same as the report.

    Like

    Invoice number

    Dear customer name
    Your total balance is 500

    We appreciate your payment
    Thanks

    Do I have to refer to each field and make the extra lines needed?

    Thanks

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    pretty much, your'll need to do something like this

    Code:
    .Body = .Body & rpt![Invoice number] & vbCrLf
    .Body = .Body & vbCrLf
    .Body = .Body & "Dear " & rpt![customer name] & vbCrLf
    .Body = .Body & vbCrLf
    .Body = .Body & "Your total balance is " & rpt![balance] & vbCrLf
    .Body = .Body & vbCrLf
    .Body = .Body & "We appreciate your payment" & rpt![balance] & vbCrLf
    .Body = .Body & "Thanks"
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the only other option i can think of is to write a complex VBA method that will change the reports controls to HTML and then put the HTML in the email.

    That isn't worth the effort though
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Nov 2004
    Posts
    78
    OK, my code looks like this,

    Code:
        stDocName = "rptExport"
        stemail = Me.vEmailAddress
    
    Dim strBody As String
    strBody = "Order ID#:" & Reports![rptExport]![txtOrderID] & vbCrLf
    strBody = strBody & "Vendor Name:" & Reports![rptExport]![vCompanyName] & vbCrLf
    strBody = strBody & "Request Date:" & Reports![rptExport]![RequestDate] & vbCrLf
    strBody = strBody & "Need By::" & Reports![rptExport]![NeedBy] & vbCrLf
    strBody = strBody & "Customer Name(s):" & Reports![rptExport]![rptCustomerName]
    
    DoCmd.SendObject , , , stemail, , , "OrderNo# " & txtorderNo, strBody
    However, the rptExport has a sub report that has multiple names. Each name in a separated line.
    How do I get all names into the email?

    Thanks

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forget the report completely?

    your report is based on queries, no? (if no: fix!)
    feed the query returns into the mail.

    with a bit of juggling, you can even make it look pretty with html mail format and then coding in the appropriate html tags.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Posts
    78
    The main report is based on a query. The main report ahs a sub report that is linked with the OrderID.
    Do I need to make a query for the sub report?
    If so, after I make the query, how do I show all lines in the body?


    Feed the query returns into the mail.????
    How does this work?

    Thanks

  9. #9
    Join Date
    Nov 2004
    Posts
    78
    An addition to the above reply.
    Is there a way to select all from the report, copy it, and past it into an email?
    Or I can export the report as a word document, open the doc, copy all, and paste it into the email. The question is, how do I do it with code?
    Thanks

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    speaking DAO (ADO equivalent exists)

    dim recs as DAO.recordset
    set recs = currentdb.openrecordset("myQuery")
    with recs
    do while not .eof and not .bof
    myMailBody = recs!thisfield & vbnewline ' <<<<<<<< WRONG
    .movenext
    loop
    end with
    recs.close
    set recs=nothing
    '...loops thru the recordset and dumps each thisField as a new line.

    it is not much further to go to add html tags.
    simple e.g.
    myMailBody = "<b> " & recs!thisfield & " </b>" & vbnewline
    ...and from there to tables and whatever html you like.

    izy

    CORRECTION
    myMailBody = myMailBody & recs!thisfield & vbnewline
    Last edited by izyrider; 01-14-07 at 16:30.
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2004
    Posts
    78
    Izy, Thank you.

    I copied your function and it looks like this.
    Code:
    Dim myMailBody As String
    Dim recs As DAO.Recordset
    Set recs = CurrentDb.OpenRecordset("qryCustomerNameForExport")
    With recs
    Do While Not .EOF And Not .BOF
    myMailBody = myMailBody & recs!FullName & vbNewLine
    .MoveNext
    Loop
    End With
    recs.Close
    Set recs = Nothing

    However, I’m getting the error “too few parameters. Expected 1”
    What am I doing wrong?

    I also tried
    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim name, name1, srtname, strSQL As String
    strSQL = "SELECT * FROM qryCustomerNameForExport"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
    Do While Not rs.EOF '<<
    name = Nz(rs(1))
    name1 = name & Nz(rs(1))
    rs.MoveNext
    Loop
    End If
    srtname = name1
    Also, I’m using
    DoCmd.SendObject acSendReport, "rptInvoice", acFormatSNP, stemail, , , "OrderId ID# " & txtorderNo, strBody

    How can I manage to have the name of the attachment should be the orderNo, not the default report name?

    Thank you again.
    Joe

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmm - that was late-night code which sometimes doesn't hit 100% quality. (sorry for the error!) you spotted the error and proposed a fix
    here's another go my way:

    Dim myMailBody As String
    Dim recs As DAO.Recordset
    Set recs = CurrentDb.OpenRecordset("qryCustomerNameForExport" )
    if (recs.eof and recs.bof) then
    msgbox "no records!"
    else

    With recs
    Do While Not .EOF ' DONT WANT THIS >>>>>> And Not .BOF
    myMailBody = myMailBody & recs!FullName & vbNewLine
    .MoveNext
    Loop
    End With
    endif
    recs.Close
    Set recs = Nothing

    anyhow - back to your questions...
    i am trying to respond to your "stick it in the body" question, so i will ignore stuff relating to reports.

    too few parameters looks like it is coming from your 'subreport' source - is it expecting a criterion from the 'mother' query (which doesn't exist in my no-report approach)?

    it really is do-able. a loop of loops if there are many values in the 'mother', or if only one, redefine the 'child' query so it 'knows' what the criterion is (needs a JOIN most likely).

    post the SQL you have (both queries if you using two) and it will get fixed.

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Nov 2004
    Posts
    78
    Currently my code looks like this

    Code:
    Dim stDocName As String
    Dim txtorderNo As String
    Dim stemail As String
        txtorderNo = Me.OrderID
        stDocName = "rptVendorOrderOnly"
        stemail = Me.vEmailAddress
    '    DoCmd.OpenReport stDocName, acPreview
    'DoCmd.SendObject acSendReport, stDocName, acFormatTXT, stemail, , , "Order ID# " & txtorderNo
    
    DoCmd.OpenReport stDocName, acPreview, , , acHidden
    Dim strBody As String
    strBody = "OrderID#: " & Reports![rptVendorOrderOnly]![txtOrderID] & vbCrLf
    strBody = strBody & "Original Order#: " & 
    strBody = strBody & "Vendor Name: " & Reports![rptVendorOrderOnly]![vCompanyName] & vbCrLf
    strBody = strBody & "Request Date: " & Reports![rptVendorOrderOnly]![RequestDate] & vbCrLf
    strBody = strBody & "Need By: " & Reports![rptVendorOrderOnly]![NeedBy] & vbCrLf
    'strBody = strBody & "Customer Name(s):" & Reports![rptVendorOrderOnly]![rptCustomerName]
    strBody = strBody & "Address: " & Reports![rptVendorOrderOnly]![Address] & vbCrLf
    strBody = strBody & "City: " & Reports![rptVendorOrderOnly]![City] & vbCrLf
    strBody = strBody & "State: " & Reports![rptVendorOrderOnly]![State] & vbCrLf
    strBody = strBody & "Zip: " & Reports![rptVendorOrderOnly]![Zip] & vbCrLf
    strBody = strBody & "Instructions: " & Reports![rptVendorOrderOnly]![Instructions] & vbCrLf 
    strBody = strBody & "We appreciate your business."
    
    'Dim myMailBody As String
    'Dim recs As DAO.Recordset
    'Set recs = CurrentDb.OpenRecordset("qryCustomerNameForExport")
    'If (recs.EOF And recs.BOF) Then
    ''MsgBox "no records!"
    'Else
    'With recs
    'Do While Not .EOF ' DONT WANT THIS >>>>>> And Not .BOF
    'myMailBody = myMailBody & recs!FullName & vbNewLine
    '.MoveNext
    'Loop
    'End With
    'End If
    'recs.Close
    'Set recs = Nothing
    
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Dim name, name1, srtname, strSQL As String
    'strSQL = "SELECT * FROM qryCustomerNameForExport"
    '
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(strSQL)
    'If rs.RecordCount > 0 Then
    'Do While Not rs.EOF '<<
    'name = Nz(rs(1))
    'name1 = name & Nz(rs(1))
    'rs.MoveNext
    'Loop
    'End If
    'srtname = name1
    
    
    'DoCmd.SendObject acSendReport, stDocName, acFormatTXT, stemail, , , "Order ID# " & txtorderNo
    DoCmd.Close acReport, "rptVendorOrderOnly"
    'DoCmd.SendObject , , , stemail, , , "Order ID# " & txtorderNo, strBody
    
    DoCmd.SendObject acSendReport, "rptVendorOrderOnly", acFormatSNP, stemail, , , "Order ID# " & txtorderNo, strBody

    The data for the email is coming from the oprn report rptVendorOrderOnly
    And the data for the names is coming from the query qryCustomerNameForExport

    This is the SQL for the Query
    Code:
    SELECT tblCustomerName.LastName, tblCustomerName.FirstName, tblCustomerName.MiddleName, Trim(([LastName]+", ") & ([FirstName]+" ") & [MiddleName]) AS FullName
    FROM tblCustomerName
    WHERE (((tblCustomerName.OrderId)=[Forms]![frmOrder]![OrderID]));
    I hope you can help me.
    Thanks
    Joe

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have shown us two queries:

    SELECT
    tblCustomerName.LastName,
    tblCustomerName.FirstName,
    tblCustomerName.MiddleName,
    Trim(([LastName]+", ") & ([FirstName]+" ") & [MiddleName]) AS FullName
    FROM tblCustomerName
    WHERE (((tblCustomerName.OrderId)=[Forms]![frmOrder]![OrderID]));

    and

    SELECT
    *
    FROM qryCustomerNameForExport

    but if i have understood what you are trying to achieve, you want output something like:

    Smith, Fred Sebastian
    <something>
    <something>
    <something>

    so where is <something> coming from? i.e. what is supposed to be in the 'subreport'.
    ...and (importantly) how does it relate to the first of the two queries above - which is/are the common fields?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Stupid question. Can you not simlpy make a code to save your report as *.rtf, then open the *.rtf file, copy everything, create a new message and paste this into the body of the e-mail?

    I've done this very method before when I needed to dummy test reports. Its a untidy method but it does work and its easy to do.

Posting Permissions

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