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="me@mycompany.com" />
<subject>
<![CDATA[this is a test]]>
</subject>
</sendEmail>
<deleteWorkspace />
</module>
</project>