Use a query to generate "to:" email list?

[ericbird]

I'd like to run a SQL query, and then use the results of the query as the list of addresses to send an email to. For instance, the output of the query might be something like:

[email protected]
[email protected]
[email protected]

which i can feed into a rowset variable. Can I then use this rowset variable to send a message to all of these people? If so, how?

Thanks a lot.

Eric

[Support_Julie]

In the following project, the MYLIB.MYFILE file contains one field. Each record contains an email address.

First, query the database to find the email records you need.

Next, write that rowset to a CSV file.

Use the Search and Replace Task to replace the end of record value (which by default is CRLF) with a comma.

Now, load the data from the new CSV file to a variable using the “Set Variable” task.

This variable is used in the “TO” field of your Send Email task.

Code: Select all

<project name="Email Test" mainModule="Main" version="1.0">
	<module name="Main">

		<createWorkspace />

		<sql resourceId="MyDatabase">
			<query outputVariable="data">
				<statement>Select * from MYLIB.MYFILE</statement>
			</query>
		</sql>

		<writeCSV inputRowSetVariable="${data}" outputFile="data.csv" whenFileExists="overwrite" outputFileVariable="dataout">
			<data trim="both" />
		</writeCSV>

		<searchAndReplace inputFile="${dataout}" outputFile="dataout2.csv" searchFor="\r\n" replaceWith="," whenFileExists="overwrite" outputFilesVariable="dataout2" />

		<setVariable name="toAEmailList" inputFile="${dataout2}" />

		<sendEmail resourceId="My SMTP Server" toList="${toEmailList}">
			<from address="[email protected]" />
			<subject>
				<![CDATA[this is a test]]>
			</subject>
		</sendEmail>
<deleteWorkspace />


	</module>
</project>