Skip to Content

Hi All,

In some of the ETL Projects there is always a need to send the processed Flat File(CSV, XLS, TXT) to the customer through e-mail. As far as I know there is no functionality in SMTP_TO() function to add an attachment to the email notification.

We can achieve this functionality through a VB Script using the exec() function in a script.

Below steps describes the steps to achieve this functionality:

Steps: Add an attachment to BODS Job  Notification email using VB Script

Detailed Description:

In order to ease the end user’s effort, it is often required that the processed Flat File(CSV, XLS etc) is sent to the user for validation. In BODS we cannot attach the report using the SMTO_TO() function

Below we will see an example of such activity.

Current Scenario:

There is no functionality in BODS to attach a file and send it to user. The same can be implemented in BODS by calling a VB Script through exec() function.

Solution: After the completion of job place a script which calls a VB Script (vbs) file to send email notification. The vbs file must be saved in the Processed location shared folder.

Declare the below Global Variable in the job.

$G_PROCESSED_LOCATION =’\\\XYZ_Location\ Processed’;

The email.vbs file has the following information;

strSMTPFrom = “User@abc.com

strSMTPTo = “User@abc.com

strSMTPRelay = “smtp.abc.com”

strTextBody = “JOB_NAME completed successfully in UAT. Attached is the file load status.”

strSubject = “JOB_NAME completed in UAT”

strAttachment = “\\ XYZ_Location \Processed\MyFile.xls”


                         Set oMessage = CreateObject(“CDO.Message”)

                     oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing“) = 2

                     oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver“) = strSMTPRelay

                     oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport“) = 25

                     oMessage.Configuration.Fields.Update

                     oMessage.Subject = strSubject

                     oMessage.From = strSMTPFrom

                     oMessage.To = strSMTPTo

                     oMessage.TextBody = strTextBody

                     oMessage.AddAttachment strAttachment

                      oMessage.Send

Here is the script to send the email

exec(‘cscript’,'[$G_PROCESSED_LOCATION]\email.vbs’, 8);

Regards

Arun Sasi


To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

    1. Arun Sasi Post author

      Hi Mikhail,

      Yes. This script was used in my previous project to attach files in the job notification. It was implemented in DEV, UAT and PROD..

      Regards

      Arun Sasi

      (0) 
  1. Santosh Panda

    Hi Arun,

    I was trying to send a file as attachments using your method but it’s not happening.

    Kindly help and explain in little detail how does this work?

    Does this method need any prerequisites?

    (0) 
    1. Arun Sasi Post author

      Santosh,

      Just checked how it was used in my previous project and I didnt find any issue with the code.

      Did you check your SMTP server settings? It might happen that the SMTP server has some issues.

      You can call the VB script through exec() function

      exec(‘cscript’,'[$G_PROCESSED_LOCATION]\email.vbs’, 8);


      Try to print the above statement and check if there is some error thrown.


      print(exec(‘cscript’,'[$G_PROCESSED_LOCATION]\email.vbs’, 8));


      Also try manually executing the VB script at the command prompt to check if the script is sending the file to users




      Regards

      Arun Sasi

      (0) 
      1. Santosh Panda

        Hi Arun,

        Smtp server has no issues,as we are able to send mail using the smtp_to() function

        I added the below line in my file and was able to send the attachment successfully.


        Here is the full code:

        strSMTPFrom = “User@abc.com

        strSMTPTo = “User@abc.com

        strSMTPRelay = “smtp.abc.com”

        strTextBody = “JOB_NAME completed successfully in UAT. Attached is the file load status.”

        strSubject = “JOB_NAME completed in UAT”

        strAttachment = “\\ XYZ_Location \Processed\MyFile.xls”

        Set oMessage = CreateObject(“CDO.Message”)

        oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing“) = 2

        oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver“) = strSMTPRelay

        oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport“) = 25

        oMessage.Configuration.Fields.Update

        oMessage.Subject = strSubject

        oMessage.From = strSMTPFrom

        oMessage.To = strSMTPTo

        oMessage.TextBody = strTextBody

        oMessage.AddAttachment strAttachment

        oMessage.Send

        Thanks,

        Santosh

        (0) 
        1. Chandra Sekhar K

          Hi Santhosh,

          Nice Documentation.

          When I try to above script i have some questions raised,

          1. How to find SMTP server.

          2. strAttachment = “\\ XYZ_Location \Processed\MyFile.xls”

            

          here Processed is mandatory or folder name

            The vbs file must be saved in the Processed location shared folder.

            what is meaning of Processed location

          When I located vbs file in E/ drive.

          3. exec(‘cscript‘,'[$G_PROCESSED_LOCATION]\email.vbs’, 8);

            

             what is meaning of cscript.

          Please share needful information

          Thanks in Advance.

          Thanks,

          Chandra

          (0) 
          1. Arun Sasi Post author

            Chandra

            Processed location is the naming convention which I used in our project where in the Excel file was placed. 🙂

            Cscript.exe is a command-line version of the Windows Script Host that provides command-line options for setting script properties

            Please note that the csscript.exe command executable can only be used on Windows and not on linux

            I wanted to create a script which can be used on Linux job server but could not test it

            Regards

            Arun Sasi

            (0) 
            1. Chandra Sekhar K

              Thanks Sasi your explanation.

              When I tried above process but not sent mail.

              I tried in administrator login also but no result.

              I used below script

              print(exec(‘cscript’,'[$G_PROCESSED_LOCATION]\email.vbs’, 8));

              and execute job successfully and shows as below,

              Capture.PNG

              Please share needful information.

              Advance thanks.

              Thanks,

              Chandra. K

              (0) 
  2. sarjan rao yerra

    Hi All,

    This is really interesting, recently I have come across the same requirement in my project and have implemented the above code, everything looks fine, but it throws me below error while running the job.

    Could you please advice if you have any Idea about this!!

    “CScript  Error: Execution of the Windows Script Host failed, Not enough storage is available to complete this operation”

    Regards,

    Sarjan

    (0) 
    1. Arun Sasi Post author

      Sarjan,

      Can you run the script using Administrator account (Run as Administrator). Also check if you have permission to run the VB script.

      Regards

      Arun Sasi

      (0) 
  3. Venkata Ramana Paidi

    Hi Arun,

    I am using the below code  but it is not sending any mail. I can send mails when I use direct smtp_to but below script is not sending mail.

    Option Explicit

    Dim MyEmail

    Set MyEmail=CreateObject(“CDO.Message”)

    MyEmail.Subject = “Subject Line”
    MyEmail.From = “**********@gmail.com“
    MyEmail.To = ”***********@gmail.com“

    MyEmail.TextBody = “This is the message body.”
    MyEmail.AddAttachment “C:\temp\order.txt”

    MyEmail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/sendusing“)= 2
    ‘SMTP Server
    MyEmail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserver“)= “172.XX.X.XX”

    ‘SMTP Port
    MyEmail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserverport“)= 25

    MyEmail.Configuration.Fields.Update
    MyEmail.Send

    set MyEmail=nothing

     

    Please help me to solve this issue.

    Thanks & Regards,

    Ramana.

     

    (0) 

Leave a Reply