Results 1 to 5 of 5
  1. #1
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    Get data from Outlook e-mail Subject and put into Access DB table

    MS Access allows a table to send an e-mail to a specially designated OutLook folder. The person who receives the email then enters data into the fields on the email. I want to Open this particular Outlook email and extract the data in the Body of the e-mail and then update or add new data to an Access table.

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    I suggest you use the Microsoft Outlook Object Library. You can open your email as an object in your Access VBA and read the info from it into your database.

  3. #3
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    Getting OutLook email subject and Placing in Access Data Base

    Quote Originally Posted by Xipooo View Post
    I suggest you use the Microsoft Outlook Object Library. You can open your email as an object in your Access VBA and read the info from it into your database.
    Thanks I have reviewed the Outlook object library, the only problem is only the MS Outlook programmers know what the heck the objects do ! Does any one have any code that can find a Folder in Outlook, call the folder with the name "Access Data Collection Replies" and extract the email Subject from an Outlook mailitem into a string variable where I can parse the data and put it into the corect fields in an Access table. I can do the MS Access VBA code but I get really confused with the Outlook code . Any help would be appreciated. Thanks

  4. #4
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    Getting data from Outlook email body and into an MS Access table

    Quote Originally Posted by JrMontgom View Post
    MS Access allows a table to send an e-mail to a specially designated OutLook folder. The person who receives the email then enters data into the fields on the email. I want to Open this particular Outlook email and extract the data in the Body of the e-mail and then update or add new data to an Access table.
    I have tried to use the Outlook library and I attach the code I am using. The code compiles but after 10 loops through the For Each section it throws a runtime error 13, Type mismatch. The Debug.print statements seem to be OK until the 11th time throught the For Each. Any help would be appreciated. Also since there are 1667 entries to scan there must be a way I can just look at one subfolder in the Inbox folder but I can't seem to figure that out.

    [Code]
    Private Sub Command14_Click()
    Dim olApp As Outlook.Application


    Dim OLFolder As Outlook.Folder
    Dim olNS As Outlook.Namespace
    Dim olMail As Outlook.mailItem
    Dim strname As String
    Dim intCt As Integer
    'Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object




    Dim eFldr As Outlook.Folder
    Dim inCt As Integer


    On Error GoTo ErrorHandler
    Const CALLER As String = " Form_frmtestForm2:Command14_Click "




    ' Set the reference to outlook or create one
    Set olApp = CreateObject("Outlook.Application")
    ' Set the reference to the oulook MAPI namespace
    Set olNS = olApp.GetNamespace("MAPI")
    'why can't I reference just one folder in the Folders collection????
    Set eFldr = olNS.Folders("johnm1940@comcast.net").Folders("Inb ox")
    ' Cycle through the emails in the specified mailbox folder
    Debug.Print eFldr.Items.Count 'this shows about 1667 items


    For Each olMail In eFldr.Items
    Debug.Print Nz(eFldr.Name, "No flder name")
    Debug.Print Nz(olMail.Subject, "Nothing")
    Debug.Print intCt
    Debug.Print olMail.To

    If InStr(1, olMail.Subject, "Re: Add New UU Members to EF table", vbTextCompare) > 0 Then 'And olMail.UnRead = True 'Then
    Debug.Print Nz(olMail.Subject, "No Subject")
    Debug.Print Nz(olMail.SenderName, "No Sender name")
    Debug.Print Nz(olMail.ReceivedTime, "No received time")
    Debug.Print Nz(olMail.SenderEmailAddress, "No send email address")

    'olMail.UnRead = False
    ' From this point additional scripting needed to save to table. Could be like:
    ' CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3, field4) " & _
    ' VAlUES('" & olMail.Subject & "', '" & olMail.SenderName & "', #" & olMail.ReceivedTime & "#, '" & olMail.SenderEmailAddress & "')"
    End If
    intCt = intCt + 1



    NextMsg:


    Next olMail


    Cleanup:




    Set olApp = Nothing
    Set olNS = Nothing
    Set olMail = Nothing
    Set eFldr = Nothing



    Exit Sub


    ErrorHandler:
    MsgBox Err.Description & vbCrLf & _
    Err.Number & vbCrLf & _
    "Called By :" & CALLER & vbCrLf & _
    Err.Source, VbMsgBoxStyle.vbCritical, "Could not add new name to data base" & vbCrLf & _
    "Module Name: = " & MODULENAME



    'GoTo Cleanup







    End Sub


    [Code/]

    I never enter the code in the IF statement.

  5. #5
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124
    Quote Originally Posted by JrMontgom View Post
    I have tried to use the Outlook library and I attach the code I am using. The code compiles but after 10 loops through the For Each section it throws a runtime error 13, Type mismatch. The Debug.print statements seem to be OK until the 11th time throught the For Each. Any help would be appreciated. Also since there are 1667 entries to scan there must be a way I can just look at one subfolder in the Inbox folder but I can't seem to figure that out.

    [Code]


    Private Sub Command14_Click()
    Dim olApp As Outlook.Application


    Dim OLFolder As Outlook.Folder
    Dim olNS As Outlook.Namespace
    Dim olMail As Outlook.mailItem
    Dim strname As String
    Dim intCt As Integer
    'Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object




    Dim eFldr As Outlook.Folder
    Dim inCt As Integer


    On Error GoTo ErrorHandler
    Const CALLER As String = " Form_frmtestForm2:Command14_Click "




    ' Set the reference to outlook or create one
    Set olApp = CreateObject("Outlook.Application")
    ' Set the reference to the oulook MAPI namespace
    Set olNS = olApp.GetNamespace("MAPI")
    'why can't I reference just one folder in the Folders collection????
    Set eFldr = olNS.Folders("johnm1940@comcast.net").Folders("Inb ox")
    ' Cycle through the emails in the specified mailbox folder
    Debug.Print eFldr.Items.Count 'this shows about 1667 items


    For Each olMail In eFldr.Items
    Debug.Print Nz(eFldr.Name, "No flder name")
    Debug.Print Nz(olMail.Subject, "Nothing")
    Debug.Print intCt
    Debug.Print olMail.To

    If InStr(1, olMail.Subject, "Re: Add New UU Members to EF table", vbTextCompare) > 0 Then 'And olMail.UnRead = True 'Then
    Debug.Print Nz(olMail.Subject, "No Subject")
    Debug.Print Nz(olMail.SenderName, "No Sender name")
    Debug.Print Nz(olMail.ReceivedTime, "No received time")
    Debug.Print Nz(olMail.SenderEmailAddress, "No send email address")

    'olMail.UnRead = False
    ' From this point additional scripting needed to save to table. Could be like:
    ' CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3, field4) " & _
    ' VAlUES('" & olMail.Subject & "', '" & olMail.SenderName & "', #" & olMail.ReceivedTime & "#, '" & olMail.SenderEmailAddress & "')"
    End If
    intCt = intCt + 1



    NextMsg:


    Next olMail


    Cleanup:




    Set olApp = Nothing
    Set olNS = Nothing
    Set olMail = Nothing
    Set eFldr = Nothing



    Exit Sub


    ErrorHandler:
    MsgBox Err.Description & vbCrLf & _
    Err.Number & vbCrLf & _
    "Called By :" & CALLER & vbCrLf & _
    Err.Source, VbMsgBoxStyle.vbCritical, "Could not add new name to data base" & vbCrLf & _
    "Module Name: = " & MODULENAME



    'GoTo Cleanup







    End Sub


    [Code/]

    I never enter the code in the IF statement.
    Well I guess I finally stumped you all ;-) I really would appreciate some help on this problem. Any help would be appreciated. I can't seem to get the

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 12-15-2014, 08:18 AM
  2. Replies: 1
    Last Post: 10-10-2014, 10:06 AM
  3. Replies: 2
    Last Post: 10-24-2012, 02:09 AM
  4. How to e-mail from access to outlook
    By hogue@montana.com in forum Programming
    Replies: 5
    Last Post: 02-17-2012, 12:33 PM
  5. Replies: 16
    Last Post: 03-15-2010, 12:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums