?
Solved

save an Email from Outlook to an MS Access Table

Posted on 2009-12-16
5
Medium Priority
?
463 Views
Last Modified: 2013-11-27
Hi,

I found this code while looking through EE and thought that it was just what i needed.  I want to allow a user to select an email in Outlook and send it to the tbl_documents table of my Database with a specific customer id associated with it.  The code seems to be succesfully saving the email in the documents folder, but it does not seem to be adding it to the table.

For information, my database BE is MS SQL however the tables are linked into the FE when the database FE is opened.  Should i be treating the database as though the tables were in Access, or should my code be linking directly into MS SQL?

I would greatly appreciate some help on this.

Regards,
Tom
Sub SaveMessageToProjectDatabase()
    Dim olkMsg As Outlook.MailItem, strFilename As String, strProject As String
    If TypeName(Application.ActiveWindow) = "Explorer" Then
        Set olkMsg = Application.ActiveExplorer.Selection(1)
    Else
        Set olkMsg = Application.ActiveInspector.CurrentItem
    End If
    'Change the path on the following line as desired'
    strFilename = "C:\Projects\ContactPLUS Development\documents\" & ReplaceIllegalCharacters(olkMsg.Subject) & ".msg"
    olkMsg.SaveAs strFilename
    strProject = InputBox("What customer do you want save the message to?", "Save Message to Contact Plus")
    If strProject <> "" Then
        AddAttachment strProject, strFilename
    End If
    Kill strFilename
    Set olkMsg = Nothing
End Sub
  
Sub AddAttachment(strProjectName As String, strFilename As String)
    'Edit the values on the next 4 lines'
    Const DBNAME = "C:\Projects\ContactPLUS
Development\ContactPLUS_dev_TESTsoftware.accdb"     '<- Path and filename of database'
    Const TBLNAME = "tbl_documents"            '<- Name of table within the database'
    Const INDXNAME = "company_id"           '<- Name of index to field containing the project name'
    Const ATTFLD = "document_path"          '<- Name of field in the table that holds the attachments'
    Dim accApp As Object, _
        daoDB As Object, _
        daoRS As Object, _
        daoAtt As Object
    Set accApp = CreateObject("Access.Application")
    Set daoDB = accApp.DBEngine(0).OpenDatabase(DBNAME)
    Set daoRS = daoDB.OpenRecordset(TBLNAME)
    daoRS.Index = INDXNAME
    daoRS.Seek "=", strProjectName
    daoRS.Edit
    Set daoAtt = daoRS.Fields(ATTFLD).Value
    daoAtt.AddNew
    daoAtt.Fields("FileData").LoadFromFile strFilename
    daoAtt.Update
    daoRS.Update
    daoAtt.Close
    daoRS.Close
    daoDB.Close
    accApp.Quit 1
    Set daoDB = Nothing
    Set daoRS = Nothing
    Set daoAtt = Nothing
    Set accApp = Nothing
End Sub
  
Function ReplaceIllegalCharacters(strSubject As String) As String
    Dim strBuffer As String
    strBuffer = Replace(strSubject, ":", "")
    strBuffer = Replace(strBuffer, "\", "")
    strBuffer = Replace(strBuffer, "/", "")
    strBuffer = Replace(strBuffer, "?", "")
    strBuffer = Replace(strBuffer, Chr(34), "'")
    strBuffer = Replace(strBuffer, "|", "")
    ReplaceIllegalCharacters = strBuffer
End Function

Open in new window

0
Comment
Question by:d10u4v
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26063067
Your code is using an Access 2007 Attachment field to store the saved Outlook message.  I don't know if this would work with a SQL Server back end, but I suspect it would not.  The code looks OK for Access 2007.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26063074
I have similar code that loads saved files to Attachment fields in Access 2007.
0
 

Author Comment

by:d10u4v
ID: 26063168
Oh,  I was unaware that it was using an attachment field.  Sorry.

Is it possible to just send the path of the document which was created in the first part of the code (email) to a standard text field in the tbl_documents table?

What i'm trying to achieve is the ability for a user to select an email in Outlook and then press the button i have assigned the macro to in Outlook and then enter the customer number ID and have the email saved to the documents folder and the path to this email saved in the tbl_documents table.

I hope i'm making sense :)

Tom
0
 

Author Comment

by:d10u4v
ID: 26101024
Hi,

Can anyone help me with this question?  The code is saving the email to the folder, but i'm having problems trying to re-write the code to save the link to this saved email in the tbl_documents table with the customer number entered by the user.

Thanks,

Tom
0
 

Accepted Solution

by:
d10u4v earned 0 total points
ID: 26102758
This is what i have done. Seems to be working.
Sub SaveMessageToProjectDatabase()
    Dim olkMsg As Outlook.MailItem, strFilename As String, strProject As String, strMessage As String
    If TypeName(Application.ActiveWindow) = "Explorer" Then
        Set olkMsg = Application.ActiveExplorer.Selection(1)
    Else
        Set olkMsg = Application.ActiveInspector.CurrentItem
    End If
    'Change the path on the following line as desired'
    strMessage = olkMsg
    strFilename = "D:\Projects\ContactPLUS Development\documents\" & ReplaceIllegalCharacters(olkMsg.Subject) & ".msg"
    olkMsg.SaveAs strFilename
    strProject = InputBox("What project should I save the message to?", "Save Message to Project Database")
    If strProject <> "" Then
        AddAttachment strProject, strFilename, strMessage
    End If
    'Kill strFilename
    Set olkMsg = Nothing
End Sub
  
Sub AddAttachment(strProjectName As String, strFilename As String, strMessage As String)
    'Edit the values on the next 4 lines'
    Const DBNAME = "D:\Projects\ContactPLUS Development\ContactPLUS_dev_TESTsoftware.accdb"     '<- Path and filename of database'
    Const TBLNAME = "tbl_documents"            '<- Name of table within the database'
    Const INDXNAME = "company_id"           '<- Name of index to field containing the project name'
    Const TXTFLD = "document_path"          '<- Name of field in the table that holds the attachments'
    Dim accApp As Object, daoDB As Object, daoRS As Object
    Set accApp = CreateObject("Access.Application")
   
    Dim db As DAO.Database
    Dim rs As DAO.Recordset


    Set db = CurrentDb
    Set rs = db.OpenRecordset(TBLNAME, dbOpenDynaset, dbSeeChanges, dbOptimistic)


        rs.AddNew
        rs("document_path") = strFilename
        rs("company_id") = strProjectName
        rs("file_type") = "Email"
        rs("document_desc") = strMessage
        rs.Update
        
        rs.Close
        db.Close
        
    Set rs = Nothing
    Set db = Nothing

Set accApp = Nothing

End Sub
  
Function ReplaceIllegalCharacters(strSubject As String) As String
    Dim strBuffer As String
    strBuffer = Replace(strSubject, ":", "")
    strBuffer = Replace(strBuffer, "\", "")
    strBuffer = Replace(strBuffer, "/", "")
    strBuffer = Replace(strBuffer, "?", "")
    strBuffer = Replace(strBuffer, Chr(34), "'")
    strBuffer = Replace(strBuffer, "|", "")
    ReplaceIllegalCharacters = strBuffer
End Function

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question