How feasible is this?

[farscape_fan]

We are looking at moving from Access to SalesForce due to SalesForce's ability to track Emails. My boss wants to know how feasible it would be for Access to keep track of Emails in the same way.

Our Database is structured with One company having many Locations; in turn, each location can have many staff.
A Communications Table/Form stores details of Communications with staff in each location. Therefore, when a User sends an Email, would it be possible for Outlook to . . .

1) Cross-check the Email address in the TO Field against the Staff Table in Access.
2) If the Email address exists in Outlook, retrieve the LocationID, StaffID from the Location Table.
3) Make a Temporary Table in Access with the Date; LocationID, StaffID, SenderID, MessageTypeID, and Subject
4) Append the above fields to the Communications Table.

This sounds wildly complicated, but I've looked through the Internet and these Forums, and because of the ability of Outlook and Access to 'speak', I thought it might be feasible, but just how easy it would be or how much programming would be involved is beyond me as it's not simply sending a Task or Email from Access but rather querying Access and then returning Data from Outlook to Access.

Thanks in advance for any guidance--it's greatly appreciated, as always.

Brian

[HansV]

You'd have to write code in Outlook that uses the ItemSend event to track outgoing e-mails, and ADO or DAO to open a recordset on the table in the Access database, to look up and append data. It's far from trivial, but not impossible.

[farscape_fan]

[quote name='HansV' post='781032' date='22-Jun-2009 17:14']You'd have to write code in Outlook that uses the ItemSend event to track outgoing e-mails, and ADO or DAO to open a recordset on the table in the Access database, to look up and append data. It's far from trivial, but not impossible.[/quote]

Realistically speaking, do you have any idea how long you think a job like this would take to Code? I seriously doubt I'd have the expertise to do it, so I'd probably have to get a Developer. Do you think this would take days or weeks to code, in your Expert opinion?

Thanks as always for your guidance and help.

[HansV]

A programmer who knows DAO or ADO and Outlook VBA should be able to whip up something in an hour or two. Thorough testing will take more time, of course. Here is an example; the code should go into the ThisOutlookSession module (or into a class module, but then you'd need to create an Application variable). It is simpler than what you want, but it should give you an idea.

[codebox]Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim dbs As DAO.Database
Dim rstStaff As DAO.Recordset
Dim rstComm As DAO.Recordset
Dim rcp As Recipient

On Error GoTo ErrHandler

Set dbs = DBEngine.OpenDatabase("\\Server\Share\Folder\Datab ase.mdb")
Set rstStaff = dbs.OpenRecordset("tblStaff", dbOpenDynaset)
Set rstComm = dbs.OpenRecordset("tblCommunication", dbOpenDynaset)
For Each rcp In Item.Recipients
rstStaff.FindFirst "Email=" & Chr(34) & rcp.Address & Chr(34)
If Not rstStaff.NoMatch Then
rstComm.AddNew
rstComm!StaffID = rstStaff!StaffID
rstComm!DateTime = Now
rstComm!Subject = Item.Subject
rstComm.Update
End If
Next rcp

ExitHandler:
On Error Resume Next
rstStaff.Close
Set rstStaff = Nothing
rstComm.Close
Set rstComm = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
[/codebox]
Note: you'll run into Outlook security. You can use a utility such as ClickYes to suppress the security warning.

[farscape_fan]

Hi Hans.

Just realized I didn't post back a thank you. I originally was going to provide an update with my Thank You, but my boss still hasn't told me his decision. I have a hunch we'll be switching to Salesforce, but you never know.

Thank you again, so much as always, for your helpful replies and guidance.

Take care

Brian