attachment data type containing *.jpg file. SaveToFile error

**ManDownUnder** · 03-17-2025, 04:06 PM

Hi all,

1st post (v exciting...) and I am creating a database containing info, including a *.jpg image, for each machine we have on offer.

The images are all successfully loaded and I can see them when linked to a form and I flick through the records.

My aim is to have a button on the form to run some VBA which creates an Outlook email object. I put the request into ChatGPT and got the code below, which is returning a "Run-time error 438, Object doesn't support this property or method" error on the line "If rs!Machine_Image.Attachments.Count > 0 Then" (in bold from sub contained below)

The *.jpg image is in attachment Data Type Field, Field Name "Machine_Image" in DataBase "Machines"

Full code is below, called directly from a command button on a form.

There are 50 rows in the database and I want to extract all data on the row currently being shown on the form.

I also tried using a DAO.Attachment data type but couldn't find the Microsoft Access Data Access Object Library (I think that was the name) to reference so that also failed.

Hope that explains everything? I expect this is a basic error but I'm used to VBA in Excel (don't hate me...) so diagnosing this will hopefully be simple but it's outside what is immediately familiar to me.

Happy for any solution (referencing the Library or using the attachment as Object below) - just want t make this work and very grateful for any help

Cheers
Nigel

Code:

Sub SendEmailWithImage()


    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim rs As DAO.Recordset
    Dim attachmentPath As String
    Dim strSQL As String
    Dim HTMLBody As String
    Dim tempFolder As String
    Dim attachment As Object ' Use a generic object to reference the attachment


    ' Initialize Outlook Application
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 is for a new email


    ' Define the path to save the temporary image
    tempFolder = "C:\Temp" ' Adjust this folder path if needed


    ' Get the image from the "machines" table (attachment field "Machine_Image")
    strSQL = "SELECT Machine_Image FROM Machines WHERE ID = 3" ' Change "id = 1" as needed to get the correct record
    Set rs = CurrentDb.OpenRecordset(strSQL)


    If Not rs.EOF Then
        ' Loop through the attachment field to check if there are any attachments
        If rs!Machine_Image.Attachments.Count > 0 Then
            ' Get the first attachment from the field
            Set attachment = rs!Machine_Image.Attachments.Item(0) ' Get the first attachment
            
            ' Save the attachment to the temp folder
            attachmentPath = tempFolder & "image.jpg" ' Specify the path to save the image
            attachment.SaveToFile attachmentPath ' Save the image file


            ' Compose the email with HTML body
            HTMLBody = "<html><body>"
            HTMLBody = HTMLBody & "<p>Please find the image below:</p>"
            HTMLBody = HTMLBody & "<img src='cid:image1'>" ' Reference the image using cid
            HTMLBody = HTMLBody & "</body></html>"


            ' Compose the email
            With OutlookMail
                .Subject = "Subject of the Email"
                .BodyFormat = 2 ' olFormatHTML
                .HTMLBody = HTMLBody
                .To = "recipient@example.com" ' Specify the recipient's email address


                ' Attach the image inline (CID)
                .Attachments.Add attachmentPath, 1, 0, "image1" ' 1 means olByValue, "image1" is the CID used in the HTML


                .Display ' Show the email before sending (you can use .Send to send automatically)
            End With
        Else
            MsgBox "No image found in the attachment field.", vbExclamation
        End If
    Else
        MsgBox "No record found for the selected machine.", vbExclamation
    End If


    ' Clean up
    rs.Close
    Set rs = Nothing
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

**June7** · 03-17-2025, 04:12 PM

Please post code between CODE tags to retain indentation and readability. Can edit your post.

Review https://learn.microsoft.com/en-us/of...ile-method-dao

Here is a simplified example.

Code:

Sub SaveAtt()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset2
Set rs = CurrentDb.OpenRecordset("SELECT Img FROM Flags WHERE ID = 1")
If Not rs.EOF Then 
     Set rs2 = rs!Img.Value
     If Not rs2.EOF Then rs2("FileData").SaveToFile "C:\yourpath\" & rs2("FileName")
End If
End Sub

Will your attachment field have multiple files for each record?

There is a Microsoft DAO 3.6 Object Library but it is not necessary.

**ManDownUnder** · 03-17-2025, 04:26 PM

Thanks for the quick reply and my apologies - I didn't know of the code tags. Done now... and eminently more readable!

I have already reviewed that article and couldn't see how to apply it to the error at hand. I expect my understand around properties and methods for the objects in use aren't up to it.

Thanks again

**June7** · 03-17-2025, 04:56 PM

I made edits to previous post possibly after you read. Maybe review again.

I queried ChatGPT for code. Pretty much what you posted but nothing like MS docs example.

According to MS docs an Attachment object:

... corresponds to an attachment control. Use an attachment control when you want to manipulate the contents fields of the attachment data type.

**moke123** · 03-17-2025, 05:04 PM

Are you aware of the bloating issues with attachment fields?

**Micron** · 03-17-2025, 05:12 PM

Yes, it's advisable to stop, ditch this approach and start over. Here's how you can avoid a lot of the usual pitfalls that beginners make, and don't find out about until they're at the point where they don't want to start over.

https://www.accessforums.net/showthr...773#post521773

**ManDownUnder** · 03-17-2025, 05:21 PM

Just looked and implemented the change (adding in rs2) and it works fine.

Many thanks. I couldn't figure out the need for the second recordset

**ManDownUnder** · 03-17-2025, 05:22 PM

Cheers and agreed - I have some experience and am pretty sure I still have a few bad coding habits !

**Welshgasman** · 03-17-2025, 06:08 PM

Attaxhments are actually a second recordset linked to the main recordset.
Most people store a path to the files to avoid bloating the DB, which Access is prone to do.

attachment data type containing *.jpg file. SaveToFile error

Thread Tools

attachment data type containing *.jpg file. SaveToFile error

Similar Threads

Split form containing product info and another form containing notes

ShellExecute file association in Window 7 64-bit does not open the default program for jpg files

How can I select a file (jpg) and copy (and rename) it .

how to open access file whenever jpg image file is opened

jpg file in continuous form

Posting Permissions