Automated E-mail delivery through SQL/VB.Net

Question

• I have an HR solution created using VB.Net, with SQL Server 2008 R2 as its back-end.
• I have a new requirement from my client where the program sends payslips to each employee of a department automatically by the end of the month.
• Sql can do this. But the output from SQL is just text and the client wants to stick to the Payslip report designed in the program.

• Currently the end user has to select each employee, view his/her payslip, and click E-mail report. This report is handled by SSRS inside the .net application.

  1. Is it possible to use the meta data of the report in an email body, so that it produces an html page for the receiver?
  2. If i create a SSRS report manager, and schedule automated back ups, I presume I won't be able to loop through employees and so on. Am I wrong here?
  3. Is there a way to run a report, take a snapshot, and send an image attachment to respective employee?

Answer 1

Second the scheduled task/windows service. You can have a developer write a .net function that uses the System.Net.Mail.MailMessage class to generate an email. You can even have the developer create two alternate views for the email, one that is a prettified html version and the other that is a plaintext version. That way if the receiver cannot view html on their device, they can still see the necessary data.

Just take all of the SQL data and programmatically insert it into an html file with placeholder text for your variables.

Then, using your smtp credentials, send the email out to all of the employees.

Answer 2

One approach is the sheduled task. You could write a console application that 1st checks end of month every time it runs, loop through the employee and generate report. Now you can build a html report if you want and send an email with the Mail object IsBodyHtml= True mail message html property and tge body will be html string which u will need to construct or as pdf attachment of the report generated by ssrs.

There are many approaches.... Windows Service is probably my fav.