# Include data(more than one row and column) into Email body

**URL:** <https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [March 11, 2011, 2:26pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498 "2011-03-11T14:26:23Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 11, 2011, 2:26pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/1 "2011-03-11T14:26:23Z")

</div>

Hi, I have an automatic job that generates an excel file and includes the attachment into each email address found in the query. From this task, I want to eliminate the task of sending the attachment and send the \*\*data directly into the body of the email. \*\*

**Sample of data** :  
Week\_Ending RegHrs Missing Over\_Booked Email  
02-27-11 30 10 M@  
03-06-11 32 8 M@…  
03-13-11 55 15 M@…  
03-06-11 41 8 1 P@…

I tried the email derivation but I do not know how to group the records before sending the email. I do not want to send data by rows.  
If there is more than one row in the file for the same email address, I want to get all the data related to that user and send only one email with all the correlated rows and columns.  
I am using TDA ver2.7

Thanks in advance for any help to this task.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 11, 2011, 3:58pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/2 "2011-03-11T15:58:40Z")

</div>

Sounds like you need to use the Loop Dataset activity.

You would need to execute a query that deterimines who the separate emails would go to. Then for each row in that set execute your report query filtered by that user name(use a bind variable for this). This would give you a report only for that user that can be embedded in the body of the email.

See this Blog posting. There is an example file in the section “putting it altogether”. It shows using a loopdataset with email.

[http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx](http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx)

Let me know if this was not enough to answer your question.

Debbie

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/3 "2011-03-15T15:17:22Z")

</div>

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.  
[AutomaticTasks.mdb](https://global.discourse-cdn.com/quest/original/2X/a/aef4129a1bf24ab02ceda261c0a679d76089db25.mdb) (276 KB)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/4 "2011-03-15T15:17:22Z")

</div>

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.  
[TimeSheet\_Email.tas](https://global.discourse-cdn.com/quest/original/2X/0/08a737b69d3325a1e28b61d0fda288c0d4afec78.tas) (28.5 KB)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/5 "2011-03-15T15:17:22Z")

</div>

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.  
[EMAIL\_ADDRESSES-ForTimeSheet.sql](https://global.discourse-cdn.com/quest/original/2X/b/b3bc053ea18fca7402c4987e8c72980bf9339c47.sql) (230 Bytes)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/6 "2011-03-15T15:17:22Z")

</div>

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.  
[FormLetter.txt](https://global.discourse-cdn.com/quest/original/2X/0/0c369c9eaa1d6ccbd5135f53adcfccf6065c84d1.txt) (136 Bytes)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/7 "2011-03-15T15:17:22Z")

</div>

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.  
[GetJobsPerEmail-ForTimeSheet.sql](https://global.discourse-cdn.com/quest/original/2X/a/a943d80427f61dfcdef3f67ebc2f83a9f6bdb5b7.sql) (634 Bytes)

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 14, 2011, 8:44pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/8 "2011-03-14T20:44:21Z")

</div>

It seems to me you are almost there. You only need to change your GetJobsPerEmail-ForTimeSheet.sql to give you the sum of the values for each employee. I made a real simple example and have attached it.

Debbie  
[EmployeeHours.zip](https://global.discourse-cdn.com/quest/original/2X/6/61233f05bf6968b9827cb0e020aecbd5fd842853.zip) (16.6 KB)

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 14, 2011, 8:45pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/9 "2011-03-14T20:45:17Z")

</div>

P.S. Please my sample files in C:\Temp to run. Also add a connection to the access database I included.

Debbie

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 15, 2011, 3:18pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/10 "2011-03-15T15:18:31Z")

</div>

Thanks Debbie, but unfortunatelly, I could not open the database. I am still using MS Accces 2003. Anyway, I am going to try by changing my second sql to get the sum of hrs. I Hope soon I will get the results I need per user which shoud include all the weeks with the correlated totals where the employee has not report hours. In case you want to look again at my task, I attached my mdb with the rest of files I included yesterday. Thanks Debbie.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 16, 2011, 12:07am UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/11 "2011-03-16T00:07:42Z")

</div>

, `TimeSheet-Step2-Finance`.EMP\_NO

, `TimeSheet-Step2-Finance`.`Name`

, SUM(`TimeSheet-Step2-Finance`.`Over_Booked`) AS `Over Booked`

FROM `C:\TOAD\DatabasesAutomaticTasks\AutomaticTasks.mdb`.`TimeSheet-Step2-Finance` `TimeSheet-Step2-Finance`

WHERE (`TimeSheet-Step2-Finance`.`Email_Address` = :EMAIL)

, `TimeSheet-Step2-Finance`.EMP\_NO

, `TimeSheet-Step2-Finance`.`Name`

ORDER BY `TimeSheet-Step2-Finance`.`Name` ASC

I think you need to leave the week ending dates out of the results. If you use thise query you get one row per person of the sume of the three values hours worked, missing hours, and over booked.

SELECT `TimeSheet-Step2-Finance`.`Service Center`, SUM(`TimeSheet-Step2-Finance`.`Hrs_Worked`) AS `Hrs Worked` , SUM(`TimeSheet-Step2-Finance`.`Missing_Hours`) AS `Missing Hours` GROUP BY `TimeSheet-Step2-Finance`.`Service Center`  
 ![Hours.png](https://global.discourse-cdn.com/quest/original/2X/a/a80fe79da7787a01e75011c4baf14eafee408a69.png)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 16, 2011, 12:09pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/12 "2011-03-16T12:09:17Z")

</div>

Thanks Dabbie for your time, but I cannot ignore the weeks because I need to point to each employee the week where they are missing hours.The system where employees report labour can be selected by week endings. Anyway, it seems what I wanted to do is not feasible at this point. Hope you can consider my request for future enhancements because if the data that needs to be forward to the employee is less than 5 lines, then for quickly access to put this data into the email body is faster than to do extra clickings to open the attachment.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 17, 2011, 12:51am UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/13 "2011-03-17T00:51:25Z")

</div>

I can enter an an enhancement but I am still not certain what the output is that you want. Using the sample data at the top of this post can you show me what you want the output to look like? PPerhaps a sample of one complete letter. From this I can see how to proceed.

Debbie

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 17, 2011, 9:38am UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/14 "2011-03-17T09:38:44Z")

</div>

Hi Debbie,

Please find attached a sample of the data I would like to include on the email body that is generated for each user.Thanks  
[TimeSheetTemplate.rtf](https://global.discourse-cdn.com/quest/original/2X/c/cb060486282da6a3176b2f9eb85c54e6d250c866.rtf) (5.66 KB)

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 17, 2011, 1:22pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/15 "2011-03-17T13:22:43Z")

</div>

I must be having a "blond moment". I totally forgot that I have already coded an enhancement for this.

Here is how you do it:

1. Use the Select to File activity and export your employee hour data to a csv file.
2. Then use the Find and Replace activity and use the Replace file option. This will insert the contents of the CSV file into the body of your text file.
3. You will need to then use Find and Replace to handle the formating. IE: replace commas with spacses, etc.

I have one user that does this with carriage returns and all kinds of formatting. Give this a try or let me know if you need a sample.

Debbie

P.S. I actually am a blond

 ![FormLetter.png](https://global.discourse-cdn.com/quest/original/2X/b/b903bf1dc0a6fb54efcb780cae74a592b03e0cf7.png)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 17, 2011, 3:55pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/16 "2011-03-17T15:55:38Z")

</div>

Thanks a lot Debbie for the good news. I am so happy to have the option to send more simple emails to users.  
I added the activity and saved the file as CSV. I used the replace activity and now I can see the text of my file into the email body; of course all data needs to be formatted but that is secondary. For now I am more worried about finding out what I am doing wrong because the body of the email is showing the csv file but not the format that is on the text file template. An example will be greatly appreciated. Thanks a lot Debbie. TDA is fantastic.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 18, 2011, 12:35am UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/17 "2011-03-18T00:35:49Z")

</div>

Okay, I have sample files attached. I had to do a couple of things to accomplish what you need.

1. Instead of using Select to file to export to csv, make an Export Wizard template. You need to export to csv without headers. I put the column headers in the template file. This made formatting easier.

2. Use a token, such as #TABLE# and insert the csv file into the body of the template.

3. Then replace all commas with two tabs. The Find and replace will take nonvisual characters. To do this you need to invoke the drop drown for text values. this is actually a richtext edit area. To enter a Tab press the ctrl and tab. Then use the enter key to close.

The email body now looks like this.

Dear Monica,

Please review your labour entries because hours are either missing or over booked.

Week\_Ending Reg\_Hrs Miissing\_Hrs Over\_Booked

03/06/2011 12 20

03/13/2011 8 32

\*\*The above total hours per week ending includes vacation and statutory holidays in any valid status. \*\*

Pretty slick, hugh?

Debbie  
[Sample.zip](https://global.discourse-cdn.com/quest/original/2X/5/500319327b1f2b3cb6a3713148a8d5f09a7be9c0.zip) (4.79 KB)

---

<div class="post-metadata">

**Author:** ![monica.chavezca.schn](https://avatars.discourse-cdn.com/v4/letter/m/e99b99/32.png) [@monica.chavezca.schn](https://forums.toadworld.com/u/monica.chavezca.schn)\
**Post date:** [March 18, 2011, 2:36pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/18 "2011-03-18T14:36:34Z")

</div>

Your job is amazing. Thanks a lot Debbie. TDA is wonderful.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 18, 2011, 2:55pm UTC](https://forums.toadworld.com/t/include-data-more-than-one-row-and-column-into-email-body/6498/19 "2011-03-18T14:55:57Z")

</div>


