• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

How to link and view pertinent outlook email data in an Access SQL Server database

I have a client that wants to maintain an Access/SQL Server history of all emails referenced to a particular quote or order. They would like the database to contain fields tracking sales rep, project engineer, recipient, email date, subject, schedule dates, attachments, key words, etc. and then links to each email. I don't know how to handle the email body and attachments, as it would be redundant and cumbersome to store that information in the database.  I prefer the simplest approach to get the job done. I created a macro and an Outlook user form with a few controls and code to save this information to the SQL Server database. I don't yet know how to reference the various parts of the email with VBA and how to create links in the Access application to open linked emails. Thanks for your help!
  • 6
  • 5
1 Solution
omgangIT ManagerCommented:
You can begin by linking the Outlook folder to Access as a linked table.  Not all fields are available via linked table but many are and it may suffice for your needs.  It's also possible to access the Outlook object model via VBA in Access but no need if the linked table solution is suitable.
OM Gang
omgangIT ManagerCommented:
In Access 2010, select External Data tab in the Ribbon and then the More drop-down.  You'll see the option to import or link to an Outlook folder.
OM Gang
pabrannPresidentAuthor Commented:
Ok great, I think this has fantastic possibilities. The only thing I will need to do is have a field added to this linked table that can house the order number / quote number. How can I add this field and have it populated by the user?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

pabrannPresidentAuthor Commented:
And just one more thing. I notice that this table has a field named "Has Attachments". Is it possible to have a link so the attachment can be viewed or opened?
omgangIT ManagerCommented:
I think it is possible to create a user-defined field in Outlook for the folder you have linked.  I am having troubles with the Access wizard to link an Outlook folder so cannot check myself right now.

For your purposes might you not be better off creating an independent table in Access (or SQL?) to hold all relevant data from the Outlook messages?  That way if the message is deleted from Outlook the record still exists in the database.  If so, your order #/quote # field should be in this table.

As for getting the attachment(s) from the message your probably going to need to use VBA.  I believe I have sample code; I'll look for it.  If you go this route then you don't really need to link the Outlook folder because you'll be automating Outlook in the code.

OM Gang
omgangIT ManagerCommented:
I've got my Exchange Inbox opened in Access 2010 (via query instead of linked table....let me know if you'd like to see how to do that) and I'm thinking you won't be able to see a user-defined field if you do create it in Outlook.  This means you'll need to create a local database table and then use code or an insert/append query to get the data from Outlook into the table.

OM Gang
pabrannPresidentAuthor Commented:
Yes, I have most of the information necessary to do as you suggest. I think the only think I need now is how to get attachment data via VBA, such as path and file name, etc..

Thanks so much for your help!!!!
pabrannPresidentAuthor Commented:
Excellent suggestions. I appreciate it very much!!
omgangIT ManagerCommented:
You're very welcome.
OM Gang
omgangIT ManagerCommented:
Here's some sample code to get Outlook message attachment(s).  My sample is from within Outlook VBA but you can easily modify to run it from Access VBA
OM Gang

Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

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

Public Sub PrintItems()
On Error GoTo Err_PrintItems

    Dim olNS As NameSpace
    Dim olMailBox As MAPIFolder, olFolder As MAPIFolder
    Dim olItems As Outlook.Items
    Dim olMsg As Outlook.MAILITEM
    Dim olAttachment As Outlook.Attachment
    Dim blPrint As Boolean
    Dim intCount As Integer, intMax As Integer
    Dim strMailboxName As String, strFolderName As String
    Dim strMsg As String
    strMailboxName = "Mailbox - Gang, OM"
    strFolderName = "Test"
    intMax = 10
    intCount = 1
    Set olNS = Outlook.GetNamespace("MAPI")
    Set olMailBox = olNS.Folders(strMailboxName)
    Set olFolder = olMailBox.Folders(strFolderName)
    Set olItems = olFolder.Items
        'sort the folder items
    olItems.Sort "[ReceivedTime]", False
    For Each olMsg In olItems
        'If intCount > intMax Then Exit For
        If olMsg.Attachments.Count <> 0 Then
                'call function to print each attachment
            For Each olAttachment In olMsg.Attachments
                blPrint = PrintAttach(olAttachment)
                If Not blPrint Then
                        'attachment didn't print
                    strMsg = "Attachment:  " & olAttachment.FileName & "  of message with Subject:  " _
                            & olMsg.Subject & "  and Dated:  " & olMsg.ReceivedTime & "  --  Failed " _
                            & "to print."
                    MsgBox strMsg, , "Print Error"
                End If
        End If
            'print the message

        Sleep (2000)    'pause 2 seconds
        'intCount = intCount + 1
    Set olMsg = Nothing
    Set olItems = Nothing
    Set olFolder = Nothing
    Set olMailBox = Nothing
    Set olNS = Nothing
    Exit Sub
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_PrintItems
End Sub

Public Function PrintAttach(ByRef atm As Attachment) As Boolean
' Runs print command on associated file. Returns False if file cannot be printed.
On Error GoTo Err_PrintAttach

    Dim fs As Object
    Dim i As Integer
    Dim tempFilePath As String, tempDir As String
    Dim v As Variant, verbs As Variant
    Dim blPrint As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")

    blPrint = False
        'assign temp directory
    tempDir = "c:\TempPrint\"
    tempFilePath = tempDir & atm.FileName
        'check file extension and only proceed if .pdf, .tiff or .xlsx
    Select Case Right(tempFilePath, 4)
        Case ".pdf"
            blPrint = True
        Case "tiff"
            blPrint = True
        Case "xlsx"
            blPrint = True
    End Select
        'if attachment isn't of appropriate type then exit without printing
    If Not blPrint Then GoTo Exit_PrintAttach

' Ensure another temporary file is not replaced
'i = i + 1
'tempFilePath = tempDir & i & atm.FileName
'Loop While fs.FileExists(tempFilePath)
'Debug.Print "FilePath: " & tempFilePath

        'save temporary file
    atm.SaveAsFile tempFilePath

        'delete temp file
    'fs.DeleteFile tempFilePath, True
    Set fs = Nothing
    Exit Function

    MsgBox Err.Number & ", " & Err.Description, , "Error in Function PrintAttach"
    'If fs.FileExists(tempFilePath) Then fs.DeleteFile (tempFilePath)
    PrintAttach = False
    Resume Exit_PrintAttach
End Function
pabrannPresidentAuthor Commented:
Thanks again! Everything works......
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now