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
Hi,
please give more details
email.vbs - it's your script?
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
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?
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
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
Thanks for correcting the Code Santosh!!
All, I have updated the code which will be used in the VB script.
Regards
Arun Sasi
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
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
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
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
Dear all,
that's pretty nice - but is there a possibility to sent personalized Attachments to the recipient?
Thanks
Best Regards
Martin
Yes Martin, You can send any attachment to the recipient.
Am I missing something? You are just listing a partial script here - is it intended to be a full example?
Hi Alden,
that is working - what is missing (more or less) is the implementation of adding a personalized attachment.
Best Regards
Martin
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
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
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
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.
I have used above mentioned code to create VB script, but getting invalid character error during VB script compilation...can anyone help
which is the updated code?