VBA - create email and add attachment from a table

[cookm75]

Hi,

So I have a procedure that creates an email and I would like to attach a document that is held in a table (tablename: tblImages, fieldname: Image) as an attachment type.

Currently the only way I can do this is to save the attachment to the hard drive, then attach it to the email and then delete it from the hard drive as per the below code:


Set db = CurrentDb
Set rsParent = db.OpenRecordset("tblImages")
rsParent.MoveFirst
Set rsChild = rsParent("Image").Value

Set fld = rsChild("FileData")
strPath = CurrentProject.Path & "\" & rsChild("Filename")
On Error Resume Next
Kill strPath
On Error GoTo 0

fld.SaveToFile strPath
Set myOlApp = CreateObject("Outlook.Application")
Set myEmail = myOlApp.CreateItem(olMailItem)


With myEmail
.SentOnBehalfOfName = sOnBehalf
.To = sRecipient
.Subject = sSubject
.Attachments.Add strPath, olByValue, 0
.HTMLBody = sBody
.Display
End With
Kill strPath



Does anyone know if there is a way to attach the file from the table without saving to the hard drive first?

I am using MS Access 2013.

[OBP]

Where is the file stored if not on your hard drive?
I am not familiar with Access 2010/2013/2016 table fields that store "files", I have always used the old fashioned method of storing the file path to the file in the table as you are currently using.

ps there appears to be an Access addin recommended by Luke Chung a Microsoft MVP that does what you want, see this
http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Although he is the President of FMS :)

pps Attachment fields appear to be frowned upon by Access experts.

[cookm75]

HI OBP,
The file is stored in a table in the same database, in an Attachment type field.

[OBP]

Then your code for saving it as a file and then attaching it is the only way that I have found to do what you want VBA wise.

[gmayor]

HI OBP,
The file is stored in a table in the same database, in an Attachment type field.Surely it is the file path that is stored in the database and not the file itself. Access is only on the periphery of my sphere of expertise but it appears that the attachment field contains a link to the original file. That being the case you can attach the original file from its stored location.

[cookm75]

OK, I thought that might have been the case.
Thanks