Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: emailing reports combineing codes

    i found 2 different codes 2 send emails and i want to combine them.

    one code uses outlook to to send the email with a file.

    the other sends the same with different data snp report to different people. depending who its going to.

    docmd.output was cancled dont know why


    here is my code

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim MyDB As Database
    Dim MyRS As dao.Recordset
    Dim TheAddress As String
    Dim qdf As dao.QueryDef
    Dim db As dao.Database
    Dim strSQL As String
    Dim blRet As Boolean
    Dim sPDF As String
    Dim sName As String

    Set MyDB = CurrentDb
    Set db = CurrentDb
    Set MyRS = MyDB.OpenRecordset("qryEmailMovieReport", dbOpenSnapshot)
    MyRS.MoveFirst
    Do Until MyRS.EOF

    strSQL = "SELECT * FROM qryEndOfWeekFilmReportEmailing WHERE "
    strSQL = strSQL & "[Maker] = '" & MyRS![ID Number] & "'"

    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)


    DoCmd.OutputTo acReport, "repEndOfWeekFilmEmailing", acFormatSNP, DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp", False

    sName = DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp"

    If Len(sName & vbNullString) = 0 Then Exit Sub
    ' let's use the name of the selected Snapshot file
    ' to name our converted PDF document.
    sPDF = Mid(sName, 1, Len(sName) - 3)

    blRet = ConvertReportToPDF(vbNullString, sName, sPDF & "PDF", False)



    TheAddress = MyRS![EmailAddress]
    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo
    .Subject = "movie Report Starting " & Forms![frmreports]![Text4]
    .Body = ""

    Set objOutlookAttach = .Attachments.Add(DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.PDF")


    .Send


    End With
    MyRS.MoveNext
    Loop

    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub

    can any1 help thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's the question??
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2004
    Posts
    178
    it is not creating the snp file and it does not till me why

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it is not creating the snp file
    so the problem must be in this part:
    Code:
       
        MyRS.MoveFirst
        ' you say no errors, so MyRS does contain records
        
        Do Until MyRS.EOF
        
            strSQL = "SELECT * FROM qryEndOfWeekFilmReportEmailing WHERE "
            strSQL = strSQL & "[Maker] = '" & MyRS![ID Number] & "'"
            ' is ![ID Number] numeric? if YES, drop both single quotes 
           
            db.QueryDefs.Delete "NewQuery"
            Set qdf = db.CreateQueryDef("NewQuery", strSQL)
            ' something like this might be more efficient since
            ' it saves destroying and creating the object each time
            '    Set qdf = db.QueryDefs("NewQuery")
            '    qdf.SQL = strSQL
            '    qdf.Close
            '
            ' given the odd choice of name "NewQuery", are you sure that
            ' the report is looking at this query as .recordsource 
            '
            ' add a breakpoint here and run NewQuery manually. any records?
            
            DoCmd.OutputTo acReport, "repEndOfWeekFilmEmailing", acFormatSNP, DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp", False
            ' you make three identical calls to DLookup - more efficient
            ' is to setup sName first, and use it in the other two locations
            ' you can setup sName outside the loop i guess since it 
            ' seems to be constant
            ' 
            ' check sName for Null/empty before using it 
            '
            ' is Parms a single-record table? even if yes, why not give it
            ' an ID field = 1 and explicitly pull that row by adding criteria
            ' to your DLookup
            '
            ' you are lucky that acReport = acOutputReport (the constant that
            ' you should have used)
            '
            ' does acFormatSNP work in the immediate window?
            '    ? acformatsnp
            '    Snapshot Format (*.snp)
    
    izy
    Last edited by izyrider; 03-10-07 at 02:50.
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2004
    Posts
    178
    This is the first code i am useing an it works

    it sends the same report to different people with different data in it as a snp file. but some people cant read them so i am trying to convert them to pdf

    if i make them pdf i cant use docmd.send

    Sub SeparateEmails()
    '*** error trapping - execution goes to bottom on error
    On Error GoTo Err_SeparateEmails

    Dim db As dao.Database
    Dim qdf As dao.QueryDef
    Dim strSQL As String
    Dim rsGLTable As dao.Recordset
    Dim rsCriteria As dao.Recordset
    Dim blRet As Boolean
    Dim sPDF As String
    Dim sName As String

    Set db = CurrentDb
    Set rsCriteria = db.OpenRecordset("qryEmailMovieReport", dbOpenSnapshot)

    '*** the first record in the Criteria table ***
    rsCriteria.MoveFirst

    '*** loop to move through the records in Criteria table
    Do Until rsCriteria.EOF
    '*** create the Select query based on
    ' the first record in the Criteria table
    strSQL = "SELECT * FROM qryEndOfWeekFilmReportEmailing WHERE "
    strSQL = strSQL & "[Maker] = '" & rsCriteria![ID Number] & "'"

    'MsgBox strSQL
    '*** delete the previous query
    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)


    DoCmd.OutputTo acReport, "repEndOfWeekFilmEmailing", acFormatSNP, DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp", False

    DoCmd.SendObject acReport, "repEndOfWeekFilmEmailing", acFormatSNP, rsCriteria![EmailAddress], "diamondcinemanavan@eircom.net", "", "Movie report for week beginning " & [Forms]![frmreports]![Text4], "See attachment ", False

    sName = DLookup("[DefaultfileLocation]", "Parms") & "\reports\Movie Report.snp"

    If Len(sName & vbNullString) = 0 Then Exit Sub
    ' let's use the name of the selected Snapshot file
    ' to name our converted PDF document.
    sPDF = Mid(sName, 1, Len(sName) - 3)

    blRet = ConvertReportToPDF(vbNullString, sName, sPDF & "PDF", False)


    '*** goto the next record in Criteria table
    rsCriteria.MoveNext

    Loop

    rsCriteria.Close

    Exit_SeparateEmails:
    Exit Sub

    Err_SeparateEmails: '*** if there is an error, execution goes here
    '*** if the error is the table or query missing (3265)
    ' then skip the delete line and resume on the next line
    ' Error 2501 notifies you that the SendObject action
    ' has been cancelled. See the OnNoData Event of the report.
    If Err.Number = 3265 Or Err.Number = 2501 Then
    Resume Next
    Else
    '*** write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_SeparateEmails
    End If

    End Sub


    does this help

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tough to say without the least idea what the mystery routine ConvertReportToPDF() looks like.

    you are still pointlessly calling DLookup() twice in every loop when sName could be established just the once outside the loop. you are still using the wrong constant (which luckily for you has the correct value). you still have an absurd field called 'ID Number' which you claim is text (so why Number?). you still destroy and create objects unnecessarily when you could just edit the object properties. you still rely on DLookup() to return it's choice of row from the table. bah!

    meanwhile, the later call to your mysterious routine ConvertReportToPDF() should not be involved in the earlier codes' capability to produce a .SNP

    does your code compile?

    if yes, slap a breakpoint at the top of your code and another one on the mysterious ConvertReportToPDF() line.
    F8 thru until you find the line that is causing the problem. if nothing goes banana shaped until the ConvertReportToPDF() line, see if the SNP exists on disk at that stage. ??? maybe your mysterious routine is killing the SNP

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2004
    Posts
    178
    its now comming up with runtime error 2501

  8. #8
    Join Date
    Aug 2004
    Posts
    178
    it looks like that one of the ids were blank and thats whats wrong sorry

    i will tidy it up now that it works

  9. #9
    Join Date
    Aug 2004
    Posts
    178
    from this that code how do i get the it to request for email address

Posting Permissions

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