We help IT Professionals succeed at work.

Linking Outlook to an Access Database

2,787 Views
Last Modified: 2013-09-30
I have created some software for quoting and order fulfillment in MS Access 2007.  I want to be able to select an email in Outlook's Inbox, hit a custom button which then checks the sender's email address in my Access database.  It should then present a list of all orders associated with that email address and allow the user to select one of them.  Once selected, a copy of the email will then be created in the database against that order.

I'm after some guidance on the best way to do this.  My Access Database is split with a frontend and a backend.  I'm experienced with VBA in MS Access, but have very limited experience with such integration tasks.  Hopefully with a little guidance I'll be able to figure the rest out.
Comment
Watch Question

omgangIT Manager
CERTIFIED EXPERT

Commented:
Will the process be initiated from within Outlook or Access?
OM Gang
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
This will be tricky...
First, note that despite the brevity of your request, you have asked for quite a lot here.
You are asking fa a complete "Project" here.,   Not asking a single question with one straightforward answer.

You have also left out a lot of important details, (like what exactly will be in the email, your skill level with VBA, if you can install 3r party apps, what tables/forms/queries you have already,  ...etc), ...so I can only sketch the broad strokes...

1. Link your inbox to MS Access.
However, if the sender is in your contacts list in Outlook, the Inbox linked table will present only the "Friendly" name, not the email address.
So, to be sure, you would have to store the Friendly name and the actual email address in your Access "Senders" table

3. Create a generic "Orders" Form (continuous view)
2. Create a generic "Orders" Report

3. Create a "Continuous" form from the Inbox linked table.
Then put a button on this form to "See all orders for this Customer"
The code on this button will open your "orders form"
The code will be something like this:

'Set the variables
Dim strSenderName As String

'Set the sender Name
strSenderName = Me.SenderName
'Open the Orders form
DoCmd.OpenForm "YourOrdersForm", , , "SenderName=" & "'" & strSenderName & "'"

4. On the Order form, create a buton to send out an email with the details of this order as a report.
The code on this button will be something like this:

Dim strSenderName   As String
Dim strSenderEmail  As String
Dim OrderID         As Long

'Set the sender Name
strSenderName = Me.SenderName
'Get and set the sender email address
strSenderEmail = DLookup("EmailAddress", "YourSendersTable", "From=" & "'" & strSenderName & "'")
'set the OrderID
lngOrderID = Me.OrderID

'Open the report, filtered for the sender and the order, hidden
DoCmd.OpenReport "YourOrdersReport", acViewPreview, , "SenderName=" & "'" & strSenderName & "'" & " AND " & "OrderID=" & lngOrderID, acHidden
'Create the email
DoCmd.SendObject acSendReport, "YourOrdersReport", acFormatPDF, strSenderEmail, , , "Order", "Here is one of your Orders", True
'Close the Report
DoCmd.Close acReport, "YourOrdersReport"

JeffCoachman

Author

Commented:
@OM Gang: To be Initiated in Outlook, i.e. select an email in Outlook and then hit a button on the Quick Access Toolbar.

@JeffCoachman: Thanks for your detailed input.  I've already implemented the functionality for sending documents from the database and you are right it was quite a project!  My client requires the facility to associate emails in Outlook with existing orders/quotes.  Sage ACT! has a similar facility that I'm looking to replicate.  

I'm making progress now I think.  I have the following in an Outlook module which is stripping the required data from the selected email and then uses an ADODB connection to the back-end to identify the respective client:

Sub Test()
    Dim objitem As MailItem
    Dim strSender As String
    Dim strSubject As String
    Dim dteReceived As Date
    Dim strEmailID As String
    
    Select Case TypeName(Application.ActiveWindow)
        
        Case Is = "Explorer"
            If Application.ActiveExplorer.Selection.Count > 1 Then
                MsgBox "Please select just one email.", vbInformation Or vbOKOnly Or vbSystemModal, "Select Email"
            Else
                For Each objitem In Application.ActiveExplorer.Selection
                    If TypeOf objitem Is MailItem Then
                        strEmailID = objitem.EntryID
                        dteReceived = objitem.ReceivedTime
                        strSender = LCase(objitem.SenderEmailAddress)
                        strSubject = objitem.Subject
                        Debug.Print "Sender: " & strSender & "," & vbCr & " Subject: " & strSubject & vbCr & " ID: " & strEmailID & ", " & vbCr & " Recieved: " & dteReceived
                        Debug.Print "ContactID: " & fFindContactID(strSender)
                    Else
                        Debug.Print "Not a mail item"
                    End If
                Next
            End If
  
        Case Else
            MsgBox "Please select an email.", vbInformation Or vbOKOnly Or vbSystemModal, "Select Email"
    End Select
end sub


Function fFindContactID(strEmailAddress As String) As Long
    Dim sqlContact As String
    
    Dim rs As ADODB.Recordset
    Dim cnAccess As ADODB.Connection
    Dim lngContactID As Long
    
    'initialise
    lngContactID = 0
    
    'look up email address and determine associated contact
    Set cnAccess = New ADODB.Connection
    cnAccess.Open "ESL_DATA" 'DSN
    
    sqlContact = "SELECT tblEmail.email, tblContact.firstName, tblContact.surname, tblContact.contactID " & _
                "FROM tblContact INNER JOIN tblEmail ON tblContact.contactID = tblEmail.contactID " & _
                "WHERE (((tblEmail.email)='" & strEmailAddress & "'));"
    
    Set rs = New ADODB.Recordset
    With rs
        .Open sqlContact, cnAccess, adOpenForwardOnly, adLockOptimistic
        
        If Not .EOF Then
            .MoveFirst
            Debug.Print !FirstName & " " & !surname
            lngContactID = !ContactID
        End If
        .Close
    End With
    Set rs = Nothing
    
    'return result
    fFindContactID = lngContactID
    
End Function

Open in new window



The schema for the database is:

Database Schema
The next step will be to present a list of orders/quotes (stored in tblCustomerRFQ) for the client in an Outlook form if that is the best way of doing it?  I'm on new ground here. I can use the ADODB connection to obtain the recordsource for a listbox on the Outlook form to present this hopefully (please tell me if this is feasible or if there is an easier way).  With an order selected, I think I'll be on the home straight as I use ADODB to create new records for the email in tblContactLog and tblContactDocument.  I will create a copy of the email and store this in a folder containing all documents and refer to it in tblContactDocument/storageFileName.

Author

Commented:
Obviously I need to add error handling and handle certain cases (like having multiple client records for the same email address), but I can deal with these once I have the basics in place.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Luke ChungPresident
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
...OK, so which approach are you leaning towards then?

FMS, utility would seem to do all of this for you.

My approach would involve you doing most of the design work your self.

Again, it was nave made clear what your skill level was with Access development or VBA coding, or what objects were in place already...
...or the exact steps you need the user to perform...

Author

Commented:
I managed to solve the problem largely myself using other expert exchange questions and internet searches, but there were valid contributions from the participating experts which did help and are worthy of consideration.

@JeffCoachman - thanks for all your inputs.  In my initial question, I did say that I was experienced in VBA/Microsoft Access and that I was simply after guidance (something along the lines of the process diagram I posted would have sufficed) and I felt that I clarified my requirement in the second post.  I did state in my second post that I had already implemented the functionality for sending emails so adding further information on existing objects would have just clouded the issue.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.