Sending Outlook Email to SQL Table using either Javascript or SQL Server

Question

I have built some functionality in a mobile GIS application called GeoMedia Smart Client which allows the user to fill out email details in a form, which then opens Outlook to allow them to send the email. The functionality uses Javascript and XML workflows to populate the email with the values from the form as well as automatically populating asset details in the body.

I have built a SQL Server table to save these emails with Date Sent, From, To, Subject, Body etc. This table is populated via a SQL trigger in Smart Client. The problem is, the user has the option of editing the email in Outlook or Smart Client. Any edits in Outlook won't be saved in the SQL table as the trigger only runs in Smart Client. Is there a way using either SQL or Javascript to send the Recipient, Subject, Body etc from the open email in Outlook to the SQL table? I realise Javascript is client side and SQL is server side.

I know it's bad design having the ability to edit the email in either Smart Client or Outlook, but the other option using SMTP had it's own problems with our IT department and security issues allowing external Smart Client users access to the server.

Hopefully that makes sense,

Regards

Josh

Answer 1

You can develop a VBA macro or add-in where can handle the Outlook object model events and save the changes made by a user or programmatically to the SQL database. Also you may consider developing a custom UI for Outlook where users can save emails directly to the db without other software involved.

See Getting Started with VBA in Outlook 2010 to develop a VBA macro. Be aware, VBA macros are not designed for distributing the code on multiple PCs. If you need to get the solution working for multiple users, see Walkthrough: Creating Your First Application-Level Add-in for Outlook.