At one point there was a tutorial that demonstrated how to retrieve a list of emails from a data source (I think it was a flat file, but I could be wrong), load those email addresses into a variable, and then use that variable in the Send Email activity.
I cannot seem to find this tutorial, and my attempts haven’t quite worked out the way I had intended… Am I nuts in thinking this existed?
[quote user=“N.B.”]
At one point there was a tutorial that demonstrated how to retrieve a list of emails from a data source (I think it was a flat file, but I could be wrong), load those email addresses into a variable, and then use that variable in the Send Email activity.
I cannot seem to find this tutorial, and my attempts haven’t quite worked out the way I had intended… Am I nuts in thinking this existed?
[/quote]I made a quick sample of this. Place both the excel file and automation script in your c:\temp. you will need to change your email configuration.
SendEmailSample.zip (7.45 KB)
Hi Debbie,
What we’re trying to do is actually build a table in a database (SQL Server, to be exact) that contains the Name of the report, and the recipient. Ideally we’d loop through the table, picking up every email address, concatenate them together, and then insert that string into the TO of the Email Activity.
The example sends an individual email for every recipient, but currently are scripts send one email to everyone in the TO line and that’s the kind of functionality we’d like to maintain.
Is that possible?
This should be possible but you can run into a size limitation. Any idea of how many addreses you might be concatonating? If it is not too many you would need a variable created outside of the loop that gets updated in side the loop by adding , or ; and then the next email address each time. You want to use the set varible outside the loop and the set variable value inside the loop so it updates the outside loop variable instead of creating new one with the same name inside the loop with a scope that pertains only to inside the loop. If set variable value does not exist then you are on an older version and don’t need to worry about it.
Another idea if you have a very large number of email addresses and use Outlook for email is to create a Outlook VB script to create a distribution list and then have Toad send the email to the distribution list.
I do not believe we’d reach a size limitation - after all, all of the current scripts have the email addresses hard-coded in them, and that would be our starting point. The reason for moving them to a database is so that we can easily cross reference terminated employees and remove their emails without having to edit every single script.
The concatenating can actually be performed via SQL Server and delivered to the variable - I’m just not sure how to set up the variable correctly, that’s all.
Last thought: Is there any chance that TDP will one day have some ‘built in’ variables that capture items like the current file name (ie. an automation script runs, and a built in variable captures the file name of the automation script), or other environmental pieces that could be useful in automation?
In that case what you want to do is simple. Create and set a variable with the type set to SQL.:
Then add email task:
What you name the variable in the Set Variable step is the same name you use in the To line of the email step.
I like your idea of adding Automation environmental vars. I entered QAT-4654 for this. Let me know if you think of any other vars to add.
This variable method works great!
For Oracle users:
select ‘info@email.com’ from dual
I’d like to be able to manage the distribution list using this variable method without having to open the TAS and re-import SQL file when the list changes.
Any ideas on how to make that happen?
It would be nice to have the ‘Link to File’ option, much like how the file export functions in the Automation tool.
Where is your list of emails stored? You can use a file as a data source (just keep the name and location the same when you update or replace the file). You can also set up a step to import the data from a file into the database prior to setting the variable. That way you can filter and do anything else you want with SQL before setting the variable with the email address.
I have the file saved on a network location as DistributionList.sql. I tested by adding a few names to the DistList, but when the TAS ran, it didn’t refresh from the updated sql.
I will look into an import option.
The sql for setting a variable can come from a file but there is no option for link to file (like there is for execute sql or select to file). If you change the job to first execute sql (with the link to file option checked) that truncates and loads a table with what you need and then use a simple select statement (that does not need to change) to load your variable from that table in the next step, you can accoplish what you need.
I entered QAT-4917 to add linked file option to set variable sql type
Debbie, It is people like you on your team that make this such a great product. You all monitor the forum and constantly improve an already great product based on how the users actually use the product. Let your bosses know what a great team you have.
We never tire of hearing that:) We will keep up the good work. And you and your friends keep up the good work. I can’t tell you how nice it is to have users answer other users questions before I can get to them. It really feels like a very knowable community!
debbie,
I have a script that runs that pulls two columns, project number and email address of a user. What i would like to do is send an email to each individual user with their project number in the email but I'm having a hard time figuering out the variable situation (so i have something in the To line). There could be a larger number of rows pulled from the original query. When i try to use SQL as the variable type it wants me to connect (using Oracle) but really i dont need to i just want to set the variable from the loop_data so i can prep the email to be sent. Hoping this is a simple missed step on my part to get this working - trying to mimic an Alert similiar to what Oracle has. Picture was my sad attempt to get it to work PA_EMAIL is the column i need and ZZZ_Service_Type is the varibale name i gave the loop_data. I also tried select * from :ZZZ_Service_type.PA_EMAIL .. saw a video that mentioned variableset.column_name
Thanks for any help you can give.
The loop data should have the sql to retrieve the result dataset. i.e. Select PA_EMAIL From YourDatabaseTable. This will be stored in the loop variable Loop_Data_1_SQL (unless you rename it as I do). Then inside the loop you want to use #Loop_Data_1_SQL.PA_EMAIL# in your To line of the email step. You basically have a virtual table called Loop_Data_1_SQL with one field in it PA_EMAIL that the loop is going step through 1 record at a time returning the next rows value in PA_EMAIL each time.
that worked, i made more difficult than it had to be… thank you for your help
