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

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts Page 1 of 1

ericbird

Verified User
Posts: 2
Joined: Mon Aug 17, 2009 9:54 am

Post by ericbird » Mon Aug 17, 2009 9:57 am
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:

name1@fakecompany.com
name2@fakecompany.com
name3@fakecompany.com

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

User avatar
Support Specialist
Posts: 91
Joined: Thu Mar 05, 2009 3:49 pm
Location: Ashland, NE USA

Post by Support_Julie » Mon Aug 17, 2009 2:52 pm
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>

2 posts Page 1 of 1