Toad World® Forums

How to send a mail via Automation without attachment?


#1

how can a result be collected directly to e-mail body (or even better - subject?) via Automation (and scheduling) and sent , without having attachments?

example - select query gives 1 row of 8 digit integer and i do not need it to be sent to report (Excel), but i would prefer it to be sent via mail directly

simple example:
select count(id) from emp_credentials

where emp_status = ‘CU’

and dep_code not in (‘mark’,‘sale’)

result is 17

my aim is to schedule it for automatic run every Friday at 17.10,

but i cannot view attachment on my phone, so i would need this result directly in subject or body

Hi,


#2

I have attached a screenshot with steps and sample files.

Here is what you need to do.

  1. Use the Execute Script and execute to a result set variable. IE: RESULT
  2. Add a LoopDataset activity and set it to use the result set you named in step one. IE: RESULT
  3. For each row set a local variable to the value of one column. Do this by adding a Set variable acitivity(IE PJTNUM). Give it a name and set the value by dereferencing the result set. Use the # symble. IE: #RESULT.COL1# (I know you only have one row but you will still need to use the loop dataset)
  4. You can add a log comment and see the value of the variable. This is just for debug purposes. Use the same # symbol on the var name. #PJTNUM#
  5. To use this value in the body of an email you need to have a text file to use as a template. Create a text file with the content having a distinct word to find and replace. Then use the Find and Replace activity to update the data. IE: "This week there were NN projects finished. The Find and Replace would Find ‘NN’ and replace with #PJTNUM#
  6. Add an email activity and select the option that says use file for the body of the email. Point to the file.

The attached zip file can we used as an example. The file paths are to C:\temp and ofcourse the connect data and email info would need to be changed to work for you.

let me know if you have anymore questions.

Enjoy!

Debbie
AutomationEmail.zip (2.25 KB)


#3

I have attached a screenshot with steps and sample files.

Here is what you need to do.

  1. Use the Execute Script and execute to a result set variable. IE: RESULT
  2. Add a LoopDataset activity and set it to use the result set you named in step one. IE: RESULT
  3. For each row set a local variable to the value of one column. Do this by adding a Set variable acitivity(IE PJTNUM). Give it a name and set the value by dereferencing the result set. Use the # symble. IE: #RESULT.COL1# (I know you only have one row but you will still need to use the loop dataset)
  4. You can add a log comment and see the value of the variable. This is just for debug purposes. Use the same # symbol on the var name. #PJTNUM#
  5. To use this value in the body of an email you need to have a text file to use as a template. Create a text file with the content having a distinct word to find and replace. Then use the Find and Replace activity to update the data. IE: "This week there were NN projects finished. The Find and Replace would Find ‘NN’ and replace with #PJTNUM#
  6. Add an email activity and select the option that says use file for the body of the email. Point to the file.

The attached zip file can we used as an example. The file paths are to C:\temp and ofcourse the connect data and email info would need to be changed to work for you.

let me know if you have anymore questions.

Enjoy!

Debbie
AutomationEmail.png