Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Making e-mail attachments directcly from a BLOB/OLE data field using VBA/MS Access

I have the e-mail attachments working in my built in e-mail module, but the user has to identify the attachment by file location, so to attach any embedded documents requires them to open and save them to their hard drive first.

The client wants to attach documents already embedded by means of a file loaded through MS Access/OLE and stored in the BLOB field of a MySQL database.  The client naturally wants to just identify that document in the software and have the software make the attachment to the e-mail without having to export and identify it in a file location first.

Since the app can have any manner of file types embedded, although most are MS Word documents, are there any viable options to improve the user experience around making e-mail attachments directly from the OLE field, or by pulling it out of the MySQL database and orchestrating the attachment behind the scenes based on having the user just identify the document in the application?

I think loading them through OLE puts a wrapper around the data in the BLOB field so it has to come out via OLE as well?

If they were linked, I imagine we could just use the same path, but this isn't a viable solution as embedding the documents solves other issues for the client, and we don't want to throw the baby out with the bathwater... :)

Thanks!
0
David Smithstein
Asked:
David Smithstein
  • 3
  • 3
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can writeout the contents of a BLOB file to disc. See this EE question for more information:

http://www.experts-exchange.com/Database/MS_Access/Q_25021628.html#a26201744

I'm not sure how you'd identify it, however. A BLOB file is just a bunch of bytes, so unless you're storing the name and type somewhere else, the user would very likely have to know something about the file before they could determine which one they want to use.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The client wants to attach documents already embedded by means of a file loaded through MS Access/OLE and stored in the BLOB field of a MySQL database. >>

 I would not do this.  Leave them on disk and have a path stored in the DB.   You can use the follow hyperlink method then to activate the appropriate software for the file.

<<If they were linked, I imagine we could just use the same path, but this isn't a viable solution as embedding the documents solves other issues for the client, and we don't want to throw the baby out with the bathwater... :) >>

 That's too bad, because that is standard practice for a number of reasons.  Note that you can secure a directory in such a way that it is only available to the user through the application.   So if one of the issue embedding solves is security, there are other ways to handle that.

<<I think loading them through OLE puts a wrapper around the data in the BLOB field so it has to come out via OLE as well?>>

 That's only true if you do it via a bound OLE control  Otherwise it's all up to you.

Jim.
0
 
David SmithsteinCEOAuthor Commented:
The other advantages of embedding are 1) the single backup capability through MySQL of the entire system, 2) the risk of broken links between the database and the documents is eliminated.  Granted there are other ways to do these things, etc.  I was hoping for something a little easier, but it looks like it's not going to be easy.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Curious why you graded this a B? The link I provided to the EE solution is the way to write out contents to a disc file. I realize it's not the simplest thing to do, but if you needed help implementing this, you should write back here and ask.
0
 
David SmithsteinCEOAuthor Commented:
Hi Scott, didn't mean to short change anyone, it's just that a functional solution would need to address all the functionality built into the OLE Field, such as what type of file it is. The code assumes the file type is known, which in my current app is handled by the OLE functionality.   The needed solution, if one exists, would have to start with an embedded Word document, automatically take it out, place it in a folder and pass the path with filename and extension to the e-mail code to include as an attachment.  Even this is a partial solution, as it might not be a Word document as it could be a .doc instead of a .docx, or a .pdf, or .xls or .xlsx, etc, etc.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My point is this: Without responding to Expert comments to give us a chance to help you flesh this out, you instead closed this with a B grade. That's not the way EE works - you're expected to post back here and continue working with the Experts to come to a more complete solution. If you don't do that, then we don't know you need extra help.

As far as identifying what's in a BLOB - that can be very difficult to do, and is somewhat of a moving target. There are "magic numbers" stored in the first 20 characters or so, but these can be deceiving as well, and they sometimes change - so you cannot reliably use those to determine the file type. The only tried-and-true way to do this is to store the FileName and FileType data when the BLOB data is inserted.
0
 
David SmithsteinCEOAuthor Commented:
Thanks Scott, I understand the intent of EE.  It was just that for this particular issue, I basically had to determine if there was an easy solution, or if the solution was going to be too much work to tackle right now, as I would have to redesign a major component of the software.  I guess it was counter intuitive to give an A for "there's no easy answer, and you'd have to build it yourself," even though that is probably the correct answer, and maybe deserves an A nonetheless.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Yup, sometimes "there is no answer" or in this case, the fact that it's an answer you don't like is the correct answer.

 As the old saying goes...don't shoot the messenger<g>

Jim.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now