0 Replies Latest reply on Nov 16, 2016 6:55 AM by Ramesh89

    FDM email notification with attachment

    Ramesh89

      Hi All Experts,

       

      We are in FDM ver: 11.1.2.2.

       

      I need to send email notification with validation results and data files. i have written the below code it is sending the email with single attachment.

      The number of files generating in folder path is not constant. Files are  staring with name  XXX_ACTUAL_DATA_XXX_JAN_2016. i want to send all the files which are generated in folder path.

       

      I appreciate all your help.

      ***********************************************************************************************************************

      Sub SendEmail(strmsg, strAttach)

      '------------------------------------------------------------------

      'Hyperion System 9 Financial Data Quality Management Custom Script:

      '

      'Created By:       XXXXX

      'Date Created:  11/14/2016 16:05

      '

      'Purpose: This script is used to send a batch status email to a distribution list.

      'The email will come from the user that launched the batch process.  The email

      'will contain a status message with attachemt (passed from the BatchLoad script) for each file processed

      'as well as various rpt attachments.

      '              

      '------------------------------------------------------------------

      'Declare local variables

      Dim strFromAddress

      Dim strToAddress

      Dim strSubject

      Dim strEmailMsgLine

      Dim objMsg

      Dim objConfig

      Dim intSendUsing

      Dim strSMTPServer

      Dim intSendPort

      Dim aAttachments

       

       

      'Set variables based on the tParameters table

      'To edit this table, run the task flow menu item from the web client

      strSQL = "Select * from tParameters"

       

       

      Set rs = DW.DataAccess.farsFireHose(strSQL)

       

       

      If Not rs.bof And Not rs.eof Then

                                      rs.movefirst

                                      rs.Find "ParamName='SMTPServer'"

                                      strSMTPServer = rs.fields("ParamValue")

                                      rs.movefirst

                                      rs.Find "ParamName='SMTPPort'"

                                      strSMTPPort = rs.fields("ParamValue")

                                      rs.movefirst

                                      rs.Find "ParamName='BatchEmail'"

                                      strToAddress = rs.fields("ParamValue")

                                      rs.movefirst

                                      rs.Find "ParamName='DefFromEmail'"

                                      strDefFrom = rs.fields("ParamValue")

      End If

       

       

      rs.close

      Set rs = Nothing

       

       

      'Initialize message content variables

      strFromAddress = DW.Security.fUserEmailGet(DW.Connection.PstrUserID)

       

       

      'In case a user's email address was not entered in the user maintenance window,

      'use a default address to prevent a script error

      If strFromAddress = "" Then

                      strFromAddress = strDefFrom

      End If

       

       

      strSubject = "FDM Batch Load Status"

      strEmailMsgLine = strmsg

       

       

      'Initialize Mail Server variables

      'SMTP server name

      strSMTPServer = strSMTPServer

      'CdoSendUsing enumeration-1=use smtp on local machine, 2=use smtp over network

      intSendUsing = 2

      'SMTP port on server

      intSMTPPort = strSMTPPort '25

       

       

      'Create CDO configuration object

      Set objConfig = CreateObject("CDO.Configuration")      

      'Set Config object settings

      With objConfig.Fields

                      .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = intSendUsing

                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPServer

                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")= intSMTPPort

                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10

                      .Update

      End With

       

       

      'Create CDO message object

      Set objMsg = CreateObject("CDO.Message")    

       

       

      'Assign config object to configuration property of message object

      Set objMsg.Configuration = objConfig

       

       

      'Set Message object settings and send mail

      With objMsg

                      .To = strToAddress

                      .From = strFromAddress

                      .Subject = strSubject

                      strAttach="files path"  'path to source file

                      if not strAttach="" Then 'attach file to message

                      set fso=createOobject("Scripting.FileSystemObject")

                      if fso.FileExist(strAttach) Then .AddAttachment strAttach

                      set fso= Nothing

                      End if

                                     

                      .TextBody = strEmailMsgLine

                     

                      'Send the message

                      .Send

      End With

       

       

      'Destroy message objects

      Set objMsg = Nothing

      Set objConfig = Nothing

       

       

       

       

      End Sub