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.

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

Postby 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
ericbird
Verified User
 
Posts: 2
Joined: Mon Aug 17, 2009 9:54 am

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

Postby 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>

Support_Julie
Support Specialist
 
Posts: 75
Joined: Thu Mar 05, 2009 3:49 pm
Location: Ashland, NE USA


Return to Community Forum

Who is online

Users browsing this forum: No registered users and 0 guests