Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    extract email attachment, append to mdb (2003)

    On a regular basis, I will be getting 36 emails from different senders, each one of which will have a csv attachment. The data in each attachment is to be appended to an Access table. All the attachments have the same format.

    Do I need to construct an Outlook solution for this?

    Is there a better way? All suggestions welcome.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract email attachment, append to mdb (2003)

    You can automate Outlook from Access, i.e. create an Outlook Application object in Access, and use it to loop through the MailItem objects in an Outlook folder, and read their attachments. See <!profile=WendellB>WendellB<!/profile>'s website for a short tutorial on Automation with useful links (look for Support > Tutorials.)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    I have an interest here as well. When you say "read their attachments", do you mean you read the attachment line by line or can you simply copy the attachment to a file on disk.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract email attachment, append to mdb (2003)

    You would use the SaveAsFile method of the Attachment object to save the attachment to a file on the hard disk, then process it. Since it is a CSV file that is to be appended to a table, the processing would be to import or link the csv file, then to execute an append query, and finally to delete the CSV file and the imported or linked table.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract email attachment, append to mdb (2003)

    Thanks Hans

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Thanks Hans.

    Peter, after you work out the code to save the attachment, it would be nice to see it.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract email attachment, append to mdb (2003)

    i agree.

    However, it looks like it will be "a while" because the client is using Groupwise and is expecting to move to Outlook in Q4. Since Groupwise is going away, i'm not going to look at development in this area for a few months.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Have you an example of code to extract an attachment (can be PDF or DOC) from an email to a folder on disk?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract email attachment, append to mdb (2003)

    See <post:=332,408>post 332,408</post:> for a simple example, and <post:=443,421>post 443,421</post:> for an elaborate one.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Thanks Hans, I will check them out.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Can this be done from Access?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract email attachment, append to mdb (2003)

    You can use Automation to control Outlook from Access; if you search this forum for Outlook.Application you'll find examples.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Thanks Hans, I found a post of yours in <post#=276423>post 276423</post#>, this seems to do what i want.

    Sorry to be a nit Hans, but is my assumption right that it is Access code?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract email attachment, append to mdb (2003)

    The code in <post:=276,423>post 276,423</post:> uses Automation to run Outlook code from another application; it does not contain anything specific to Access, so it could be run from any application that has VBA: Access, Word, Excel, or PowerPoint.

    BTW I note that the code in that post contains an error. Both instances of

    If oOutlook = Nothing Then

    should be

    If oOutlook Is Nothing Then

    The code needs a reference to the Microsoft Outlook n.0 Object Library in Tools | References, where n corresponds to the version of Outlook you're using:

    n = 9 for Outlook 2000
    n = 10 for Outlook 2002
    n = 11 for Outlook 2003
    n = 12 for Outlook 2007

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: extract email attachment, append to mdb (2003)

    Thank you Hans, for the info and corrections.

    I notice there is a reference to the Inbox in Outlook, viz:
    Set oFldr = oNs.GetDefaultFolder(olFolderInbox)

    how do you reference another folder in outlook. eg. if I had an outlook folder called BranchReturns, how would i interrogate thet folder?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •