For reasons I won't go into here, we are developing a report using PLSQL. When a user requests the report the request will be written to a table in the database. We want to use Report Caster to schedule the job that processes the report requests. The job will run over night. I will use WebFOCUS to process the request(s) and run the PLSQL job. The output from the PLSQL job will be a PDF or RTF file. I know how to do all of this except how to I get the report to the users since WebFOCUS does not actually build the report. It there a way to have Report Caster email a report that WebFOCUS does not generate. (I know I could ftp the report, but we want to explore email).

Any suggestions?

This message has been edited. Last edited by: jgelona,

Report caster can schedule any file and then distribute it. The file can be obtained throught a URL, FTP, or from the file system if the distribution server has access to it.

Thanks for the reply. I gave this some more thought and I think I am going to ftp the report file to the web server in a folder under ibi_html and email the user a link to the file.

jgelona,

As you are on a windows OS you could build a VBScript file to utilise CDONTS and attach the file. If you have Report Caster you could use the server settings from there.

I use that method to email out Excel BYTOC files held on a network drive which you can not achieve using Report Caster.

I'll look out the code when next at work.

T

jgelona, I do not recommend putting anything under the ibi_html alisa. It can make upgrading more difficult. I would use the approot alias instead.

jgelona,

This is some code that I use to use VB Script to email attachments on Windows using CDONTS / CDOSYS. Make sure you understand the code and the implications before you use it.

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Description : VB Script module to send emails with attachments using CDONT (Windows)
' Written By : Anthony Alsford EDATA Limited 05/01/2009
' Notes : This script MUST be executed from a machine that DOES NOT block certain
' ports via AntiVirus software or Firewall
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Error Resume Next
' First declare constants
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoServerPort = 25
Const cdoAnonymous = 0
Const cdoBasic = 1 'clear text
Const cdoNTLM = 2 'NTLM
' Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay
Const cdoIPAddress = nnn.nnn.nnn.nnn 'The IP address of your SMTP server
Const cdoEmail = yourname@yourdomain.etc 'The Email address you wish to use for send and return address
' Now Dim and set required objects
Dim fso, logfile, errfile
set fso = Wscript.CreateObject("Scripting.FileSystemObject")
set logfile = fso.CreateTextFile("E:\test_scripts\EMail.log")
set errfile = fso.CreateTextFile("E:\test_scripts\EMail.err")
' Set up the CDO configuration - the mail component needs to be achieved for each mail to be sent so is actioned later
set objConf = CreateObject("CDO.Configuration")
Set Flds = objConf.Fields
' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
With Flds
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cdoIPAddress
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")  = cdoServerPort
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoNTLM
  .Item("urn:schemas:mailheader:disposition-notification-to") = cdoEmail
  .Item("urn:schemas:mailheader:return-receipt-to") = cdoEmail
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  .Update
End With
' Set the body of the email - this should be split across lines to ensure that no line exceeds 1000 characters
strBody = "This is an automated Email - DO NOT RESPOND." & vbCRLF & vbCRLF & vbCRLF
strBody = strBody & "Regards" & vbCRLF & vbCRLF
strBody = strBody & "The PCC Team" & vbCRLF & vbCRLF
' Check to see that the file exists before trying to email it!
If fso.FileExists("drive:\fullpath\yourfilename.extn") Then
' Do something
  set objMsg = CreateObject("CDO.Message")
  With objMsg
    Set .Configuration = objConf
    .To = somename@domain.com
    .From = cdoEmail
    .Subject = "The subject line"
    .TextBody = strBody
' use .HTMLBody to send HTML email.
    .Addattachment "drive:\fullpath\yourfilename.extn"
    .DSNOptions = cdoDSNFailure
    .Send
  End With
  If Err.Number <> 0 Then
    errfile.Write "Error sending Email"
    errfile.Write "Error: " & Err.Number
    errfile.Write "Error (Hex): " & Hex(Err.Number)
    errfile.Write "Source: " &  Err.Source
    errfile.Write "Description: " &  Err.Description
    Err.Clear
    logfile.Write "email transit error " & vbCRLF
  Else
    logfile.Write "email sent OK" & vbCRLF
  End If
  set objMsg = nothing
Else
  logfile.Write "file attachment does not exist " & vbCRLF
End If

If anyone uses this code then a donation to their local cancer charity would be very much appreciated.

T

This message has been edited. Last edited by: Tony A,