Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question

    [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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •