Skip to Content
Author's profile photo Arun Sasi

Add an attachment to BODS Job Notification email using VB Script

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


Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi,

      please give more details

      email.vbs - it's your script?

      Author's profile photo Arun Sasi
      Arun Sasi
      Blog 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

      Author's profile photo Santosh Panda
      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?

      Author's profile photo Arun Sasi
      Arun Sasi
      Blog 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

      Author's profile photo Santosh Panda
      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

      Author's profile photo Arun Sasi
      Arun Sasi
      Blog Post Author

      Thanks for correcting the Code Santosh!!

      All, I have updated the code which will be used in the VB script.

      Regards

      Arun Sasi

      Author's profile photo Former Member
      Former Member

      Perfect, thanks! I guess I could have just written it myself too given how comfortable I am in VBS.

      But it's still good for everyone else, too! cc Martin Bernsteiner / Gokul Gawande

      Author's profile photo chandra koduri
      chandra koduri

      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

      Author's profile photo Arun Sasi
      Arun Sasi
      Blog 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

      Author's profile photo chandra koduri
      chandra koduri

      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

      Author's profile photo Martin Bernsteiner
      Martin Bernsteiner

      Dear all,

      that's pretty nice - but is there a possibility to sent personalized Attachments to the recipient?

      Thanks

      Best Regards

      Martin

      Author's profile photo Gokul Gawande
      Gokul Gawande

      Yes Martin, You can send any attachment to the recipient.

      Author's profile photo Former Member
      Former Member

      Am I missing something? You are just listing a partial script here - is it intended to be a full example?

      Author's profile photo Martin Bernsteiner
      Martin Bernsteiner

      Hi Alden,

      that is working - what is missing (more or less) is the implementation of adding a personalized attachment.

      Best Regards

      Martin

      Author's profile photo Gokul Gawande
      Gokul Gawande

      Hello Alden,

      If it's not working for you, refer the one which Santosh has mentioned above. or refer this one Add attachment to your mail in BODS- A step by step process

      Regards,

      Gokul

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Arun Sasi
      Arun Sasi
      Blog 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

      Author's profile photo Venkata Ramana Paidi
      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.

       

      Author's profile photo G. ERS-AOT
      G. ERS-AOT

      I have used above mentioned code to create VB script, but getting invalid character error during VB script compilation...can anyone help

      Author's profile photo G. ERS-AOT
      G. ERS-AOT

       

      which is the updated code?