Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    31

    Unanswered: Email Report / Task Scheduler

    I have a simple report that is suppose to run every week day morning and email out a copy of itself. Everything works just find if I click on open the report from access, but if I use the task scheduler to run the report, the report gets printed to my default printer then stays in the running stating till I cancel it. Any ideas?


    I had added the function fnwait thinking maybe I wasn't giving it enough time to save the pdf.

    Code:
    Private Sub Report_Activate()
    
    DoCmd.OutputTo acOutputReport, "BuyReportt", "PDFFormat(*.pdf)", "c:\temp\BuyReport.pdf", False, "", 0, acExportQualityPrint
    
    fnWait (10)
    
    Dim objOutlook As New Outlook.Application ' outlook object
    Dim objMessage As MailItem, strAttach As String ' outlook mail message
    
    strAttach = "c:\temp\BuyItemExcept.pdf"
    Set objMessage = objOutlook.CreateItem(olMailItem)
    
    With objMessage
      .To = "**@**.com"
      .cc = "**@***.com"
      .subject = "Buy Report" '& Date
      .Body = "Attached"
      .Attachments.Add strAttach
      .Send
    End With
    
    Set objOutlook = Nothing
    Set objMessage = Nothing
    
    Kill ("c:\temp\BuyReport.pdf")
    DoCmd.Quit
    
    End Sub
    
    Public Function fnWait(intSeconds As Integer)
    
    Dim varStart As Variant
      varStart = Timer
      Do While Timer < varStart + intSeconds
      Loop
    End Function
    Last edited by profector; 09-14-12 at 15:07. Reason: speeling :)

  2. #2
    Join Date
    Aug 2012
    Posts
    31
    I'm thinking it's more of a problem with the scheduler.

    I can open the report with the command below and everything works fine.

    "c:\path to\ msaccess.exe" "c:\pathto\db.accdb" \x macrothatopensreport

    I've but that in a batch file and clicking on the batch file works fine. But when the task scheduler opens the batch file I get an error

    Task Scheduler failed to start instance "{60c4a367-0296-4a74-8658-1eec12cd3928}" of "\BuyReport" task for user "domain\username" . Additional Data: Error Value: 2147942667.

    I had a several similar reports run for years under XP and Access 2003, but I haven't tried it since I switched to Windows 7 and Access 2007. I still have serveral Excel spreadsheets that get emailed out in the mornings using the task scheduler. What gives. blah.

  3. #3
    Join Date
    Aug 2012
    Posts
    31
    Well looks like it was the db after all. Actaully I think it was the reference to the MS Outlook Library that kept the task scheduler from working. As soon as I went striaght to the SMTP server the task scheduler ran just fine. I don't have a copy of the email in by outbox anymore but I can live with that, I'll just Bcc myself.

    For anyone that is interested. I used the same batch file as before and pointed the task scheduler to that batch. But I changed the VBA code in Activate Event to what's below. When I went back and looked out my other reports they all use the same method. None of them used the MS Outlook Object Library. Also this code didn't originate from me. I picked it up from the net 4 or 5 years ago and modified it to fit my needs. Thank you, to who ever did.

    Code:
    Private Sub Report_Activate()
     
    Const cdoSendUsingPort = 2
    Const cdoBasic = 1
    Dim objCDOConfig As Object, objCDOMessage As Object
    
    DoCmd.OutputTo acOutputReport, "BuyReport", "PDFFormat(*.pdf)", "c:\temp\BuyReport.pdf", False, "", 0, acExportQualityPrint
    
    strSch = "http://schemas.microsoft.com/cdo/configuration/"
    Set objCDOConfig = CreateObject("CDO.Configuration")
    With objCDOConfig.Fields
    .Item(strSch & "sendusing") = cdoSendUsingPort
    .Item(strSch & "smtpserver") = "192.168.XXX.XXX" 'ip address for SMTP
    .Item(strSch & "SMTPAuthenticate") = cdoBasic
    .Item(strSch & "SendUserName") = "UserName@domain.com"
    .Item(strSch & "SendPassword") = "Password"
    .Update
    End With
    
    Set objCDOMessage = CreateObject("CDO.Message")
    With objCDOMessage
    Set .Configuration = objCDOConfig
    .From = """My Name"" <me@mydomain.com>"
    .Sender = "me@mydomain.com"
    .To = "someoneelse@samedomain.com"
    .Subject = "BuyReport " & Date
    .TextBody = vbNewLine & vbNewLine & vbNewLine & "See Attached" & vbNewLine & vbNewLine & _
    vbNewLine & "09-14-12:  Pointed report to smtp" 
    .addAttachment "c:\temp\BuyReportt.pdf"
    .Send
    
    End With
    
    Set objCDOConfig = Nothing
    Set objCDOMessage = Nothing
    
    'Delete file
    Kill ("c:\temp\BuyReport.pdf")
    
    'Close Access
    DoCmd.Quit
    
    End Sub

  4. #4
    Join Date
    Aug 2012
    Posts
    31
    If anyone does know how to get this working with the MS Outlook Object Library please post back. This would come in handy for those that don't have access to a SMTP server.

Posting Permissions

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