Arun Sasi

December 17, 2014 2 minute read

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

20 Comments

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

Former Member

January 12, 2015 at 2:45 am

Hi,

please give more details

email.vbs - it's your script?

Arun Sasi

Blog Post Author

January 12, 2015 at 12:32 pm

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

Santosh Panda

August 21, 2015 at 2:52 pm

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?

Arun Sasi

Blog Post Author

August 21, 2015 at 5:56 pm

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

Santosh Panda

August 25, 2015 at 10:31 am

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

Arun Sasi

Blog Post Author

September 23, 2015 at 9:46 am

Thanks for correcting the Code Santosh!!

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

Regards

Arun Sasi

Former Member

September 28, 2015 at 6:47 pm

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

chandra koduri

September 22, 2016 at 5:03 pm

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

Arun Sasi

Blog Post Author

September 23, 2016 at 8:17 am

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

chandra koduri

September 28, 2016 at 1:26 pm

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,

Please share needful information.

Advance thanks.

Thanks,

Chandra. K

Martin Bernsteiner

September 2, 2015 at 6:05 am

Dear all,

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

Thanks

Best Regards

Martin

Gokul Gawande

September 2, 2015 at 8:41 am

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

Former Member

September 22, 2015 at 7:52 pm

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

Martin Bernsteiner

September 22, 2015 at 10:01 pm

Hi Alden,

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

Best Regards

Martin

Gokul Gawande

September 23, 2015 at 9:30 am

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

Former Member

November 11, 2015 at 7:52 pm

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

Arun Sasi

Blog Post Author

November 17, 2015 at 6:35 am

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

Venkata Ramana Paidi

January 31, 2017 at 7:38 am

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.

G. ERS-AOT

March 2, 2023 at 8:43 am

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

G. ERS-AOT

March 2, 2023 at 9:47 am

which is the updated code?

Add an attachment to BODS Job Notification email using VB Script

Assigned Tags