Consulting

Results 1 to 6 of 6

Thread: VBA - create email and add attachment from a table

  1. #1
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location

    VBA - create email and add attachment from a table

    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.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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/MicrosoftAcces...ree-trial.html

    Although he is the President of FMS

    pps Attachment fields appear to be frowned upon by Access experts.
    Last edited by OBP; 09-05-2019 at 08:31 AM.

  3. #3
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    HI OBP,
    The file is stored in a table in the same database, in an Attachment type field.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    Quote Originally Posted by cookm75 View Post
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    OK, I thought that might have been the case.
    Thanks

Tags for this Thread

Posting Permissions

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