Send email attachment located in a table from a form.

[msasteel]

I have Access 2007. I have a form where I can attach PDFs, Word documents, etc with an command button. This atachment is then put into a field in a table which has the email address and works fine. The attachments are there in the field with the paper clip in the field title.

I have a command button with an embedded macro that opens OUTLOOK and the email form with the email address and all the other information inserted perfectly. How do I get the OUTLOOK email to grab the assoiated attachment(s) from the record in the table and send them with the email? I can send the email without the attachment just fine.

The attachments cannot be located in a common path. They must be in the field in the associated record and each attachement will be different from one record to the next.

The emails are sent one by one, not as part of a group.

The form name is TASK DETAILS.
The email command buttin is called E-mail
The table is called TASKS
The attachment command buttin is called Attachments

What do I need to do this? I have searched many forums , but none have addressed sending an attachment that is is in a field in a record.

Thanks so much. This has been painful!

[June7]

Only way I know to do this is to save the item to external location, attach that file to email, send email, then delete the file.

[msasteel]

There has to be a better way, but thanks for the response. Seems no one else wants to tackle it either.

[ItsMe]

The solution is in post #2. The better way is to not store files in an attachment field if you will need to email said files.

[msasteel]

You didn't give any direction on how to do that, but how do the files remain with the associated record if they are not a part of the recod? I can't put them into a common location because they change and there are many records with all different required attachments. If I move the file to an alternate location, then I am storing it twice unless I then go back an erase it after it's sent. That might be an alternative, but I don't know how to write that code as part of the embedded macro I already have and I will lose traceability of that attachment to it's associated record. It's not an easy problem, for sure.

[ItsMe]

I am not sure what you are trying to say in post #5 so

Using Access to manage files stored on a network is a walk in the park compared to managing files that are stored in Attachment fields.

Here is a sample DB that will add files to and remove files from Attachments fields. If you want to interact with multiple files in a single field, you will have to add code to iterate the field.

This sample is the result of helping others and was not designed as an "add and remove" tool. It provides examples of VBA. Refactor to suit your needs.

[June7]

Suggestion in post 2 is to copy the file to external location. The file is still in the record. Use the external file for attaching to email then can delete the external file.

[msasteel]

It would appear that we are not commuicating at all. I'm not explaining myself well and I don't understand the answers offered. I apologize. When it is suggested that I put the attacments in another location, I assumed that meant on the desktop, or in another folder somewhere on the hard drive or server. Hence my question about how does the attachment stay with the Access record. You folks know among yourselves what you are trying to say, but it may not be clear to those of us who don't do this everyday what you mean. It's no one's fault - it's just experience. It sounds like I need a tutorial with someone looking over my shoulder. I do apperciate the effort by everyone, though.

[ItsMe]

If you were to ask, "Can I email a file in an attachment field?" My answer would be, “I do not believe so."

If you were to ask "How can I email I file that is in an attachment field?" I would answer, "You must use code to first extract the file from the attachment field."

I did not make it very clear in post #6 that you can use code to copy the file in the attachment field vs. deleting the file in order to extract it. The unfortunate reality is that nobody posting here knows of a way to email a file in an attachment field without first making a copy of the file and saving it to a temp folder.

So, the most appropriate question would be, "I have some code that I am trying to use to copy a file from an attachment field and I can not get it to work properly." Then provide an example of what you have tried.

It is not that we do not want to help. It is only that emailing from an attachment field is not simple. It is complex. It requires the right questions to be asked in order for the proper communications to take place.

[June7]

I think we do understand what you want and the solution is explicitly described. Not the first thread for this topic. Cannot include a file directly from attachment field as an attachment to an email. The file must reside in a location external from the db. Code can extract a copy of the file from record, save to external location, attach to email, send email, delete the external file.

Are you saving only one file in the attachment field for each record?

[Geoff_G]

I think the attached database contains VBA code that solves your problem.

The Quick Access toolbar has a green, right-pointing arrow, which runs Macro1, which in turn runs the VBA function, SendEmailUsingPlainText(), in Module1.

I'm away for a day but post back if there is anything you don't understand or if the code doesn't run the way you want and I'll review on my return.

[msasteel]

Thanks again to all that replied. I will be out of the office for a few days, but will definitely give this a try when I return. Your help is appreciated. I never dreamed this would be such a difficult request. I'll let you know when I return how I made out.

[msasteel]

I did get to try this, but apparently the DB link was developed on Access 2010 - I have 2007. I get an error that says I need version 14.0.0000.0000 when I try ot look at the macro. I attempted an update, but that didn't work either. I will keep at it and get back to you.

[June7]

You are able to open the database? Probably need to change the Outlook library reference in VBA editor.

The macro isn't even needed. All it does is call the SendEmailUsingPlainText() custom function which can be called by other methods, such as from code behind a button click event.

[ItsMe]

The example in post #11 uses a macro. The only thing the macro seems to do is call a function named SendEmailWithAttachments

You can do the same thing in a form. Use the click event of a button and place the following VBA in it.

call SendEmailWithAttachments()

IMO deleting the macro would be beneficial. Using VBA to call the function qould allow you to pass arguments to the function SendEmailWithAttachments. Having said that, refactoring the code to fit your app may be a little daunting.