Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Send Email Transactions to a SQL Server Database

Objective
-----------

Develop a MS Outlook VBA project that will send email transactions(ie. Mail received, mail deleted, mail opened, etc.) to a SQL*Server database.  For example, when a user opens an email I need to send a record, ie. transaction, to the database recording such event.  Also, I want to send the email's subject, from address, to address, # of attachments, etc. as a part of the record into the SQL*Server database.

Questions
------------
1.  Is it possible to connect to a SQL*Server database using MS Outlook Macro/VBA, and automate such event when the transaction occurrs?  If so, is there a technical document illustrating such?
2.  Do email messages have a unique identifier that can be tracked?

Thanks!
0
dkbailey1
Asked:
dkbailey1
  • 2
1 Solution
 
David LeeCommented:
Greetings, dkbailey1.

> Is it possible to connect to a SQL*Server database using MS Outlook Macro/VBA
Yes, using ADO.

> and automate such event when the transaction occurrs
For the events you listed, yes.

> is there a technical document illustrating such
I don't think there's any specific technical documentaation for accessing SQL server from inside Outlook.  There doesn't need to be.  You can access SQL server via ADO from pretty much anything.  The ADO documentation has what you need, it won't care that it's running from inside Outlook.  There is technical documentation for Outlook's event model.  There are books on the subject, information on the MSDN web site, and both OutlookCode.com (especially good) and Slipstick.com have information about the events available in the Outlook object model.

> Do email messages have a unique identifier that can be tracked?
Not really.  Each item in Outlook has a property called EntryID that is unique within a message store (i.e a given PST file or online mailbox).  If an item is moved from one store to another, then its EntryID changes.  If having a unique identifier is necessary, then you'll need to create it.  Outlook objects do support user created properties, so this is actually pretty easy to do.

Cheers!
0
 
dkbailey1Author Commented:
So I can process ADO recordsets from an Outlook VBA module in the same way that I could from MS Access?  I need to be able to Delete, Insert, and Update records in the SQL*Server database from the Outlook module.  Sounds like I can, please confirm.  Thanks.
0
 
David LeeCommented:
Yes, you can.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now