1

I've got a table that is set up similar to the following: personID|Last_Name|First_Name|feeDescription|feebalance|client1_email|client2_email

1|Test|Joe|2017 Fees|90.00|joe@test.com|joe2@test.com
1|Test|Joe|2017 Parking|40.00|joe@test.com|joe2@test.com
2|Sample|Nellie|2018 Membership|120.00|whoanellie@test.com|Null

What I would like to do is create a sql job that runs once a week and emails users with balance. I'd like to include a sent mail column so they only get the email once but would get a new email if a new fee was added. It would sent to client1_email if not null and cc to client2_email if not null.

The body would look similar to:

Dear Joe Test:
You have the following outstanding account fees with us:
     2017 Fees          $90.00
     2017 Parking       $40.00

And proceed to process the rest of the table:

 Dear Nellie Sample:
 You have the following outstanding account fees with us:
     2018 Membership   $120.00

Some personID's may have 1 fee, some 10. Is there a way to accomplish this?

  • SSRS's subscription service supports data driven schedules, cc's and bcc's. You get you more leeway on email nuances, attaching files, embedding images, etc., etc.. Plus you have access to all your tables and stored procedures to pull in the data you need. – Ross Bush Apr 27 '17 at 16:38

2 Answers2

0

If you want to simply embed a CTRL+LF(carriage return) in your body then simply use the ascii codes.

CHAR(13)+CHAR(10)

Here is a similar question.

Community
  • 1
  • 1
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

Try it's

SELECT 'Dear ' + Last_Name + ' ' + First_Name + '
You have the following outstanding account fees with us:
' + feeDescription +'   ' + feebalance
from table
Fabiano Carvalho
  • 504
  • 1
  • 6
  • 17