extract email attachment, append to mdb (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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 WendellB‘s website for a short tutorial on Automation with useful links (look for Support > Tutorials.)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks Hans.

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks Hans.

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

Reply | Quote

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

Reply | Quote

See post 332,408 for a simple example, and post 443,421 for an elaborate one.

Reply | Quote

Thanks Hans, I will check them out.

Reply | Quote

Can this be done from Access?

Reply | Quote

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

Reply | Quote

Thanks Hans, I found a post of yours in post 276423, this seems to do what i want.

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

Reply | Quote

The code in post 276,423 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

Reply | Quote

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?

Reply | Quote

You can use something like this:

Set oFldr = oNs.Folders(“Personal Folders”).Folders(“BranchReturns”)

where Personal Folders is the name of the “root” folder (the top level in the navigation pane in Outlook).

Reply | Quote

Thanks Hans, you have been an invaluable help on this.

One more question, what if that folder was also under folder Inbox? would it lok like:
Set oFldr = oNs.Folders(“Personal Folders”).Folders(“Inbox”).Folders(“BranchReturns”)

Reply | Quote

Yes, you could use

Set oFldr = oNs.Folders(“Personal Folders”).Folders(“Inbox”).Folders(“BranchReturns”)

but alternatively, you could also use

Set oFldr = oNs.GetDefaultFolder(olFolderInbox).Folders(“BranchReturns”)

You can use GetDefaultFolder with the appropriate constant to refer to any of the standard, built-in Outlook folders. The following constants are available:

olFolderCalendar
olFolderContacts
olFolderDeletedItems
olFolderDrafts
olFolderInbox
olFolderJournal
olFolderNotes
olFolderOutbox
olFolderSentMail
olFolderTasks
olPublicFoldersAllPublicFolders
olFolderJunk

Of these, olPublicFoldersAllPublicFolders only makes sense when you’re on Exchange Server; the rest should be available in all circumstances.

Reply | Quote

Brilliant Hans, thank you very much.

Reply | Quote

Hey Hans, i have just been testing this and it works beautifully.

Thank you, i also understand the code and how it works.

Reply | Quote

Great, good to hear that. Glad to have been able to help.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks Hans

Reply | Quote

Thanks Hans

Reply | Quote

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 WendellB‘s website for a short tutorial on Automation with useful links (look for Support > Tutorials.)

Reply | Quote