Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Send Email Transactions to a SQL Server Database

Posted on 2006-06-23
3
227 Views
Last Modified: 2010-04-08
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
Comment
Question by:dkbailey1
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 16972164
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
 

Author Comment

by:dkbailey1
ID: 16983793
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
 
LVL 76

Expert Comment

by:David Lee
ID: 16987528
Yes, you can.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question