Currently I have an Access 2003 front end and a SQL Server 2008 backend. Traditionally I have used an Outlook object in Access to construct and send emails from the application. Problem is many users are upgrading, and outlook objects are incompatible depending on windows versions. Rather than troubleshoot 200 user machines, I wish to move the email functionality to the server.
The DBA wants me to do all the grunt work in the backend, which includes constructing the body of the message. We use HTML formatting, and the message has a table in it and has various bits of data that need populating within it. Now I dont have an issue with the sending of the email. I get that OK, but it is the constructing of the message and populating the fields I am confused about. I think it would be easier to construct the message in Access and just pass the details and message body to a 'mail sending' stored proc...but the DBA says 'No'.
So If I want to send this email how could I go about building the body of the message in SQL:
Hello <Insert name>,
Your order number is: <insert order number>
CPU <insert data>
Memory <insert data>
Optional lines in table
Monitor <insert data>
Trackball <insert data>
I also understand I can use html tags in the string, and I just need to set the format type to HTML.
Any help would be useful.