Results 1 to 10 of 10
  1. #1
    Lusitan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5

    Import data from Outlook e-mails to table

    Hello.

    I've been looking for an easy to use helpdesk DB and I found one on the Office templates site. (I've uploaded it here)

    It's pretty simple but it suits my needs. I was wondering if someone can help with my question. How can I setup the DB that each time I open it, it transfers data from my outlook account to the "cases list"?

    Example: I have received some e-mails with some helpdesk request. For each new e-mail received, I need that, on the Case List table, one record is added with the information on the e-mail (time of record, subject, sender...) to the matching field. Hope it makes sense to you.




    Thank you in advance.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Outlook can export to Access. Review http://techtips.salon.com/feed-micro...base-1591.html

    Access can also import with wizard.

    Google: Access VBA extract email data. Not finding much example VBA to programmatically extract data and they usually involve Excel but could adapt to instead save to Access table.
    http://stackoverflow.com/questions/5...into-excel-wor
    http://windowssecrets.com/forums/sho...k-mail-folders

    Could check out this for-purchase add-in http://www.email2db.com/.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Lusitan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5
    Thank you for your answer, June7. I will try your suggestions an leave a feedback.

  4. #4
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    Outlook can export to Access. Review http://techtips.salon.com/feed-micro...base-1591.html
    I would HIGHLY advise following instructions from a salon-based website. They are supposed to be experts in hairstyles, not technology.


    (June, in case you are wondering, I kinda thought this was funny. but my assumptions can be off sometimes. did I get it right today? do we have similar tastes in humor, me wonders???)

  5. #5
    Lusitan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by help_me_with_access View Post
    I would HIGHLY advise following instructions from a salon-based website. They are supposed to be experts in hairstyles, not technology.


    (June, in case you are wondering, I kinda thought this was funny. but my assumptions can be off sometimes. did I get it right today? do we have similar tastes in humor, me wonders???)

    Even I thought it was fun...

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, I did not notice the site name and I get the humor, but look at the content and consider:

    From Dictionary.com

    sa·lon
    1. a drawing room or reception room in a large house.
    2. an assembly of guests in such a room, especially an assembly, common during the 17th and 18th centuries, consisting of the leaders in society, art, politics, etc.



    In other words, an old-fashioned word for 'thinktank' or 'brain trust'.
    Last edited by June7; 10-22-2012 at 03:24 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Lusitan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5
    OK, back from holidays and tackling this issue again. The suggestions above didn't work for me. Maybe I didn't explain what I want correctly. What I want to do is (in an automated way), when I open my database that it looks up the Outlook's inbox folder and then checks only on new messages, certain lines for the content (1st line, name; 2nd line, address;... ) and then import that specific content to determined fields on a specific table (customer's name, customer's address,...).

    Too confusing?!?!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why didn't they work - error messages, wrong results, nothing? Did you even attempt adaptation? The windowssecrets site example even shows code that checks the subject of email and imports data from only these that meet specific criteria. The code should be able to grab From info but might only be the email address itself. Don't know if it can grab the address alias (company name?).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Lusitan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5
    I'm not a really good programmer and this article seems to point to an Excel solution, not an Access one. Do you mean I could use some of this code to import to excel and then to access?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, I am suggesting that the code could be adapted to import directly to Access tables. The example code is good for either Excel or Access because it stops short of giving the code that actually writes the data to Excel spreadsheet or Access table. The code shown is good for Excel and Access as it just shows how to open the Outlook folder, loop through the items in folder, grab email elements - which is applicable to VBA in both Excel and Access.

    These might not be the best nor most comprehensive examples, but as I said, wasn't finding much. Maybe you will have better luck with a web search.

    I did some experimenting with the example code and got this much to work:
    Code:
    Sub EmailImport()
    'Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object
    Dim olApp As Outlook.Application, olNS As Outlook.Namespace, olMail As Outlook.MailItem, eFldr As Outlook.Folder
    On Error GoTo eHandler ' Set error handling
    ' Set the reference to outlook or create one
    'Set olApp = GetObject(, "Outlook.Application")
    Set olApp = CreateObject("Outlook.Application")
    ' Set the reference to the oulook MAPI namespace
    Set olNS = olApp.GetNamespace("MAPI")
    Set eFldr = olNS.Folders("Mailbox - whatever your Outlook shows").Folders("Inbox")
    ' Cycle through the emails in the specified mailbox folder
    For Each olMail In eFldr.Items
        If InStr(1, olMail.Subject, "some text here", vbTextCompare) > 0 And olMail.UnRead = True Then
            Debug.Print olMail.Subject
            Debug.Print olMail.SenderName
            Debug.Print olMail.ReceivedTime
            Debug.Print olMail.SenderEmailAddress
            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
    NextMsg:
    Next olMail
    eHandler:
    Set olApp = Nothing
    Set olNS = Nothing
    Set olMail = Nothing
    Set eFldr = Nothing
    End Sub
    However, the SenderEmailAddress property doesn't retrieve what I expected.

    The Dim line that declares object variables by reference to Outlook is early binding and requires setting a VBA reference to Microsoft Outlook 12.0 Object Library. This allows intellisense popup tips.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Import from Outlook
    By funi.t in forum Import/Export Data
    Replies: 1
    Last Post: 12-20-2011, 03:17 AM
  2. Import Outlook email message
    By crowegreg in forum Import/Export Data
    Replies: 5
    Last Post: 09-27-2011, 11:10 PM
  3. import excel spreadsheet though outlook
    By bopsgtir in forum Import/Export Data
    Replies: 0
    Last Post: 03-18-2011, 09:07 AM
  4. Send multiple e-mails through Outlook based on query
    By dataphile in forum Programming
    Replies: 3
    Last Post: 12-30-2009, 12:04 AM
  5. import custom form data from Outlook
    By bigsteve520 in forum Import/Export Data
    Replies: 0
    Last Post: 11-13-2009, 12:18 PM

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