Linking Outlook to an Access Database

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.
LVL 3
Oliver WastellMemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Will the process be initiated from within Outlook or Access?
OM Gang
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Oliver WastellMemberAuthor 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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Oliver WastellMemberAuthor 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.
0
Jeffrey CoachmanMIS LiasonCommented:
So then lets qualify the focus of this question...
< present a list of orders/quotes (stored in tblCustomerRFQ) for the client in an Outlook form>
AFAIKT, this can be done in Access

From your Schema there, one email a can belong to may contact.
But for this discussions, let's presume one contact, and one email address.

As I explained above, if can you Identify the contact "friendly name" in Outlook, (From the "From" field in your linked Outlook table), then you can add a field in your Email (or Contacts) table to holds this value.
Now you can use this value to "Lookup" the ContactID and Email address.

With the ContactID identified, you can then filter the  RFQ for for that contacts Orders.

Then on the RFQ form add a button to filter the report for that specific order, then send it as an email.

But again, lots of details are missing here, (it is still not clear what forms or report you have created already)
 ...so first create, then populate, the Friendly Name field
Then create the RFQ Report
Then create the RFQ Form

Then we can go from there...

JeffCoachman
0
Luke ChungPresidentCommented:
We've created this several times for a variety of clients. The architecture is to create a VBA add-in on the Outlook side. The lookup can be based on the email address, domain name, or other indicators to retrieve the data to display. Don't mix an Access app with it. It should be self contained within Outlook so you can retrieve the data whether it's in Access, SQL Server, whatever. Good luck.
0
Oliver WastellMemberAuthor Commented:
@LukeChung-FMS - thanks for that - I have broken the back of this question now albeit contrary to your advice to avoid using an Access app.  I have never created VBA add-ins before, but have instead written code in Outlook\Module1 which does the job for now (although a proper add-in is something that I will look at in the future for ease of deployment).  I have created a form in the existing MS Access front-end database which does the job nicely (screenshot below).  If the back-end is migrated to SQL Server, the MS Access front-end will be retained (with a little tweaking).

@JeffCoachman - thanks for your last post - sorry for the delay in responding.  You was right to suggest producing the form in MS Access as Outlook forms have their limitations and was not really suitable.  Also better to keep everything together in one front-end application.

For others that might be looking to do something similar, below I have a diagram showing how the problem is solved, the Outlook code and the MS Access form.

The Process (the user only participates in Steps 1 and 6):
Email Linking Process
The Outlook Code:
Option Explicit

'References Required:
' - Visual Basics for Applications
' - Microsoft Outlook 14.0 Object Library
' - OLE Automation
' - Microsoft Office 14.0 Object Library
' - Microsoft ActiveX Data Objects 6.1 Library
' - Microsoft Access 14.0 Object Library

Const MODULE_NAME = "Outlook (Module1)"

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hwnd As LongPtr, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As LongPtr
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hwnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long
#End If

Public Const SW_SHOWMAXIMIZED As Long = 3
Public Const SW_SHOWNORMAL As Long = 1


Const M_DATABASE_PATH As String = "[my file path here]\_Frontend\ESL_MIS.accdb"
Const M_TEMP_EMAIL_STORAGE As String = "[my file path here]\_Backend\DATA\TempStore"

'attach this procedure to the Quick Access Toolbar or a custom button on the ribbon
Sub sAssociateEmailWithClientOrder()
On Error GoTo Err_sAssociateEmailWithClientOrder
    Dim objitem As Object
    Dim strSender As String
    Dim strSubject As String
    Dim dteReceived As Date
    Dim dteCreated As Date
    Dim lngContactID As Long
    Dim lngMailItemCount As Long
    Dim strFilePath As String
    Dim strFileName As String
    
    'initialise
    lngMailItemCount = 0
    
    Select Case TypeName(Application.ActiveWindow)
        
        Case Is = "Explorer"
            'check the number of mail items selected (i.e. not read receipts, etc.)
            For Each objitem In Application.ActiveExplorer.Selection
                If TypeOf objitem Is MailItem Then
                    lngMailItemCount = lngMailItemCount + 1
                End If
            Next
            
            'check that not more than one mail item selected
            Select Case lngMailItemCount
                
                'No emails selected
                Case Is = 0
                    If Application.ActiveExplorer.Selection.Count > 0 Then
                        MsgBox "Only emails can be associated with a contact.", vbInformation Or vbOKOnly Or vbSystemModal, "Not Mail Item"
                    Else
                        MsgBox "Please select an email.", vbInformation Or vbOKOnly Or vbSystemModal, "Select Email"
                    End If
                    
                'One email
                Case Is = 1
                    For Each objitem In Application.ActiveExplorer.Selection
                        'only associating mail items, ignore everything else
                        If TypeOf objitem Is MailItem Then
                            dteReceived = objitem.ReceivedTime
                            dteCreated = objitem.CreationTime
                            strSender = LCase(objitem.SenderEmailAddress)
                            strSubject = objitem.Subject
                            lngContactID = Nz(fFindContactID(strSender), 0)

                            If lngContactID <> 0 Then
                                'save a temporary copy of the email
                                strFileName = fConvertToSQLdate(dteCreated, True) & "_" & fCleanFileName(strSender) & ".msg"
                                strFilePath = M_TEMP_EMAIL_STORAGE & "\" & strFileName
                                objitem.SaveAs strFilePath, OlSaveAsType.olMSGUnicode
                                
                                'pass details to application for associating the email to
                                fOpenDatabase strSender, strFileName, dteReceived, strSubject, lngContactID
                            Else
                                MsgBox "There are no contacts registered with this email address." & vbCr & vbCrLf & _
                                    "Please register the contact in the database before trying again.", vbInformation Or vbOKOnly Or vbSystemModal, "Please Register Contact"
                            End If
                        End If
                    Next
                    
                'more than one email
                Case Is > 1
                    MsgBox "Please select just one email.", vbInformation Or vbOKOnly Or vbSystemModal, "Select Email"
            
            End Select
    End Select

Exit_sAssociateEmailWithClientOrder:
    Exit Sub

Err_sAssociateEmailWithClientOrder:
    MsgBox "Module: Outlook" & vbCr & _
            "Error No.: " & Err.Number & vbCr & _
            "Description: " & Err.Description, _
            vbExclamation Or vbOKOnly Or vbSystemModal, _
            "Outlook Error"
    Resume Exit_sAssociateEmailWithClientOrder

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
            lngContactID = !ContactID
        End If
        .Close
    End With
    Set rs = Nothing
    
    'return result
    fFindContactID = lngContactID
    
End Function


Function fOpenDatabase(strEmail As String, strFileName As String, dteReceived As Date, strSubject As String, lngContactID As Long) As Boolean
'Dealing with external objects so use inline error trapping
'Reference required: Microsoft Access Library
    
    On Error Resume Next
    Dim appAccess As Object
    Dim blnDBopen As Boolean
    
    'initialise
    blnDBopen = False
    fOpenDatabase = blnDBopen
    
    'Grab the database if open
    Set appAccess = GetObject(M_DATABASE_PATH)
    If Err.Number <> 0 Then
      'The database was not open so open it
      Set appAccess = CreateObject("Access.Application")
      Err.Clear
    End If
    
    'Make Access visible
    If Err.Number <> 0 Then
        MsgBox "There was an error"
        Set appAccess = Nothing
    Else
        appAccess.Visible = True
        blnDBopen = True
    End If

On Error GoTo Err_fOpenDatabase
    
    appAccess.Run "sOpenForm_SelectCustomerOrder", strEmail, strFileName, dteReceived, strSubject, lngContactID
    
    Set appAccess = Nothing
    
    'return result
    fOpenDatabase = blnDBopen

Exit_fOpenDatabase:
    Exit Function
    
Err_fOpenDatabase:
    MsgBox "Error No.:" & Err.Number & vbCr & "Description: " & Err.Description
    Resume Exit_fOpenDatabase
End Function


Private Function fConvertToSQLdate(dteDate As Date, Optional blnIncludeTime As Boolean = False) As String
On Error GoTo Err_fConvertToSQLdate
Const PROCEDURE_NAME As String = "fConvertToSQLdate"

    Dim strYear As String
    Dim strMonth As String
    Dim strDay As String
    Dim strHours As String
    Dim strMinutes As String
    Dim strSeconds As String
    

    strYear = Year(dteDate)
    strMonth = Month(dteDate)
    strDay = Day(dteDate)
    strHours = Format(Nz(Hour(dteDate), 0), "00")
    strMinutes = Format(Nz(Minute(dteDate), 0), "00")
    strSeconds = Format(Nz(Second(dteDate), 0), "00")
    
    fConvertToSQLdate = strYear & "-" & Format(strMonth, "00") & "-" & Format(strDay, "00")
    If blnIncludeTime Then
        fConvertToSQLdate = fConvertToSQLdate & "-" & strHours & strMinutes & strSeconds
    End If
    
    
Exit_fConvertToSQLdate:
    Exit Function
    
Err_fConvertToSQLdate:
    MsgBox "Module: " & MODULE_NAME & vbCr & _
        "Procedure: " & PROCEDURE_NAME & vbCr & _
        "Error No.: " & Err.Number & vbCr & _
            "Description: " & Err.Description

    Resume Exit_fConvertToSQLdate
End Function


Private Function fCleanFileName(strFileName As String) As String
On Error GoTo Err_fCleanFileName
Const PROCEDURE_NAME As String = "fCleanFileName"

    Dim Invalids, e, strTemp As String
    
    Invalids = Array("?", "*", ":", "|", "<", ">", "[", "]", "", "/", "@", ".")
    strTemp = strFileName
    
    For Each e In Invalids
        Select Case e
            Case Is = "@"
                strTemp = Replace(strTemp, e, "_at_")
                
            Case Is = "."
                strTemp = Replace(strTemp, e, "_dot_")
                
            Case Else
                strTemp = Replace(strTemp, e, "_")
                
        End Select
    Next
    
    fCleanFileName = strTemp

Exit_fCleanFileName:
    Exit Function
    
Err_fCleanFileName:
    MsgBox "Module: " & MODULE_NAME & vbCr & _
        "Procedure: " & PROCEDURE_NAME & vbCr & _
        "Error No.: " & Err.Number & vbCr & _
            "Description: " & Err.Description
    Resume Exit_fCleanFileName

End Function

Open in new window


The MS Access Form
Email Linking Form
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
...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...
0
Oliver WastellMemberAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.