Sending email with attachments within query/table?

[Number11]

I'm not sure where to start with that lot.

You haven't declared any variables.
You can't set a recordset (rsAttachments) to a field value, so rsAttachments is being assigned as a variable of some type with the value of the AttachmentField
If you could there would only be one value so you couldn't loop through it
Even if you could, you never move the rsAttachments to a next record.

I think you need to explain in simple terms what you are trying to do, and how your data is stored.

Ok thanks, so i am trying to have the database export the photos attached to a table and then zip them up and include in the email i want to sent, i have created a query called Send_Photo which is just the customer account number and order details that i will save as an attachment also

 

[Number11]

so i have this working it will not work running from the query so it saves all photos not within the data ranges i wanted, anyone know how to use this method


Dim dbs As DAO.Database
Dim rst As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld As DAO.Field2
Dim savePath As String

savePath = "C:\Test\Zip\" 'Folder path to save files

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Photos") 'Photos is table name
Set fld = rst("Attachments") 'Attachements is Field name to table with attachment data type.

Do While Not rst.EOF 'Loop through all records of table.
Set rsA = fld.value
On Error Resume Next 'Omit errors if file already exist
Do While Not rsA.EOF 'Loop through all attachment of single record
rsA.Fields("FileData").SaveToFile savePath 'Save file to disk
rsA.MoveNext
Loop
rst.MoveNext
Loop

rst.Close
dbs.Close
Set fld = Nothing
Set rst = Nothing
Set dbs = Nothing

 

[Number11]

If i change to the query Send_Photo i get runtime error 3420 Object invalid or longer set?

 

[theDBguy]

so i have this working it will not work running from the query so it saves all photos not within the data ranges i wanted, anyone know how to use this method

Hi. I'm a little confused by what you just said. Is it working or is it not?

 

[theDBguy]

If i change to the query Send_Photo i get runtime error 3420 Object invalid or longer set?

Can you post the SQL statement of your query Send_Photo?

 

[Number11]

Can you post the SQL statement of your query Send_Photo?

SELECT [All Orders Jobs].[Requesters Name], [All Orders Jobs].[Account No], [All Orders Jobs].Title, [All Orders Jobs].[Customers First Name], [All Orders Jobs].[Customers Surname], [All Orders Jobs].[Telephone Number], [All Orders Jobs].[Email Address], [All Orders Jobs].[House Name or Number], [All Orders Jobs].[Address Line 1], [All Orders Jobs].[Address Line 2], [All Orders Jobs].Town, [All Orders Jobs].Postcode, [All Orders Jobs].[PC Look Up], [All Orders Jobs].Order Ref, [All Orders Jobs].MPAN, [All Orders Jobs].E_MSN, [All Orders Jobs].E_SSD, [All Orders Jobs].[STOCK Checked], [All Orders Jobs].SKID, [All Orders Jobs].G_MSN, [All Orders Jobs].G_SSD, [All Orders Jobs].[WAREHOUSE Checked], [All Orders Jobs].Kit, [All Orders Jobs].[Orders Job Requested], [All Orders Jobs].[Reason For Exchange], [All Orders Jobs].[Additional Information], [All Orders Jobs].[ID Required (Commercial only)], Photos.Attachments

FROM [All Orders Jobs] LEFT JOIN Photos ON ([All Orders Jobs].[Date Request Added] = Photos.[Date Request added]) AND ([All Orders Jobs].[Account No] = Photos.[Customer Account No]);

 

[Number11]

Hi. I'm a little confused by what you just said. Is it working or is it not?

when i have it looking at the table it works but when i have it looking at the query is not not work

 

[theDBguy]

when i have it looking at the table it works but when i have it looking at the query is not not work

If you create a query based on just one table, does it work?

 

[Number11]

If you create a query based on just one table, does it work?

If i have it looking at the table that contains the attachments - it works
If i create a query looking at the same table - it also works
does not work with my original query which is looking at 2 tables

 

[theDBguy]

If i have it looking at the table that contains the attachments - it works
If i create a query looking at the same table - it also works
does not work with my original query which is looking at 2 tables

If you open your query with two tables, are you able to go through the attachments?

 

[Number11]

If you open your query with two tables, are you able to go through the attachments?

yes they appear in the last Colum that has a paperclip

 

[Number11]

Ok got this working by using separate queries

 

[theDBguy]

Ok got this working by using separate queries

Good job!