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!
pabrannPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
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
0
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
0
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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?
0
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
0
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
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
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!!!!
0
pabrannPresidentAuthor Commented:
Excellent suggestions. I appreciate it very much!!
0
omgangIT ManagerCommented:
You're very welcome.
OM Gang
0
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
            Next
        End If
            'print the message
        olMsg.PrintOut

        Sleep (2000)    'pause 2 seconds
        'intCount = intCount + 1
    Next
   
Exit_PrintItems:
    Set olMsg = Nothing
    Set olItems = Nothing
    Set olFolder = Nothing
    Set olMailBox = Nothing
    Set olNS = Nothing
    Exit Sub
   
Err_PrintItems:
    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
'Do
'i = i + 1
'tempFilePath = tempDir & i & atm.FileName
'Loop While fs.FileExists(tempFilePath)
'Debug.Print "FilePath: " & tempFilePath

        'save temporary file
    atm.SaveAsFile tempFilePath

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

Err_PrintAttach:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function PrintAttach"
    'If fs.FileExists(tempFilePath) Then fs.DeleteFile (tempFilePath)
    PrintAttach = False
    Resume Exit_PrintAttach
   
End Function
0
pabrannPresidentAuthor Commented:
Thanks again! Everything works......
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
Outlook

From novice to tech pro — start learning today.