asked on

Send HTML Email populated with data via a Stored Procedure.

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>

<Table>
CPU <insert data>
Memory <insert data>
etc...
Optional lines in table
Monitor <insert data>
Trackball <insert data>
</Table>

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.

etech0

It's not so bad setting up html. Basically what you'll need is to wrap each paragraph (aka line) in the paragraph tags, like this:

Dim msg as string
msg = "<p>Hello " & varName & ",</p>"
msg = msg & .....(put the rest of the message in basically the same way

Open in new window

So what you're doing is including the html tags in the your string.

For the table, you wrap the whole thing in <table> ...... </table> (ignore the dots of course)
Then, each row of the table is wrapped in <tr> .... </tr>
And each "cell" of the table is wrapped in <td> ... </td>

So you would have <table> </table>, and within it would be some <tr></tr>, and within each of those would be some <td></td>.
Example table:
<table>
<tr><td>first cell in first row</td><td>second cell in first row</td></tr>
<tr><td>first cell in second row</td><td>second cell in second row</td></tr>
</table>

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luke Chung

Our Microsoft Access Email and VBA Library, Total Access Emailer, can handle this for you.

It bypasses the issues around Outlook by using SMTP. That means your user doesn't have to have Outlook installed and open for your application to send emails.

Total Access Emailer will let you send HTML formatted emails or take a report that's exported to HTML and filter it for each recipient. It can also use HTML template files to customize your emails, styles, etc. It can also attach your filtered reports as PDF files for each recipient. It'll automatically send the personalized emails to everyone in your list.

Runs as an add-in interactively. Can also be programmatically added via procedure calls by referencing our VBA library. Professional version includes a royalty-free runtime distribution license.

Free trial version here: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html