How to automate sending emails with attachments through BODS using a Custom Function
I came across a post by Arun Sasi regarding how to add an attachment through BODS via VBScript. The link to it is here, and I recommend you take a look to get an idea of the steps involved: http://scn.sap.com/docs/DOC-60700.
I found it very informative, and with the help of the comment by Santosh Panda on the same post, I was able to find a way to use a Custom Function to automate the procedure. To begin, let me mention why I decided to go about doing this.
1) The original post makes it clear that the attachment to send and the VBScript must be in the same file location
a)This was problematic to implement for us, because that means each batch job would need a new script based on the file directory
b) Our network locations get deleted every 10 days, and changing that to house a script on the network was out of the question
2) Ease of use
3) To eliminate some of the limitations I found in implementing a permanent file location with a script
a) One such issue was the ‘From’ and ‘To’ parts of the email change for our Jobs based on the environment we are in
4) To disprove the ‘BODS can’t email attachments’ notion
a) Although technically, it is still true. We use VBScript to do it
5) For the fun of it
All, Apologies for the picture content. I do not know why they appear so grainy. If you click on them, they appear much better. Thanks!
Starting out, I had no direction, but I will give you the end result as opposed to the steps to achieve this on your own. The Custom Function will be our starting point, and does the majority of what we need in order to carry this out. Then, we will look at how to set up variables to pass into the script. The [minimal amount of] ETL required to make it work. And lastly, the exec() function to pull it all together. You will notice that I redact some portions of the scripts for privacy purposes, but will maintain the majority.
The Custom Function
This is CF_Email_Attachments. Aptly named, because it can email attachments. The First chunk of this, regarding $LV_Env can be ignored. It helps determine the mailing address we use for different environments. The main information required is actually after the Return statement, which is a single large string. We Set the Return Parameter Variable to Varchar of size 2048. The VBScript itself comes in the lines to follow, with pipes for concatenating portions of the string that I have used as variables. For documentation on the VBScript itself, I recommend a google search on how to send emails with VBScript, and you will see something quite similar.
As you can probably imagine, the more information you can store in the Custom Function to automate, then the fewer parameters that need to be passed in to make the function work. This makes usability easier, but sometimes different batch jobs require different parameters.
I will note that $PV_Attachment_Location_Name is the entire file path to the VBScript that we will declare in the script below.
The First Script
The first script is arguably the most important, and requires a set of things for this to all work.The placement of the script just needs to be before the use of the Custom Function.
Not all of this is required, but some of it is necessary. Starting at the top, $LV_Integration_Name, $LV_Date_String, and $LV_File_Dir are not required, but for our uses were helpful. Using a date string in the name of a report allows a record of that report for future use.
$LV_Processed_Location is the first required step. This is the location we intend to save the VBScript that the Custom Function generates. $LV_Test_Report_FileName is step two, as I am assuming that you would like to send a report that you generated via BODS. Save a flat file as a .csv, or name it whatever document type you like.
$LV_VBS_Automation is the name of the VBScript. Notice how we save it as a .vbs. This is important.
The next 4 Local Variables are the 4 parameters that the Custom Function will use to generate the EmailUser.vbs.
Generate the file you wish to send
I am not going to go into this, but at this stage, you should have the ETL to generate a report or file that you wish to send, however that looks.
Assuming you created the report in a data flow already, we need one more data flow to create the VBScript. It does not (and should not) be complex.
Notice how I have Select Distinct turned on. This is extremely important, as BODS will generate tons of scripts if it is not, and will send potentially hundreds of emails. Also, notice how I needed a source table, even though I am not using anything from it, this is a BODS limitation.
The name of the column really does not matter.
This is nothing special, except I should note the data field for the column VBScript in the mapping is also set to Varchar 2048. This is the file we named as EmailUser.vbs in the first script.
So far, we have declared the variables we need, generated a report, used the Custom Function in a mapping to a file we named with a .vbs suffix, and now we need to use it all.
The magic happens in the script after everything else has ran. The exec() command is quite marvelous, and can be used to execute just about any file. For us, however, it looks like this:
That is it. Once the job runs, it will initialize our parameters, generate the file to send, map the field using the Custom Function, save it into a .vbs script and execute it with exec() command. Given that you are apart of the mailing list you emailed to(or if you emailed it to yourself) you should receive an email!
Thanks again to Arun Sasi for the original post and Santosh Panda for providing some more details. It was a fun journey getting this all worked out, and I hope I provided you and your teams a new capability!