Sending email with attachments within query/table?

[Number11]

Hi, so i am looking for a way to have the database rename the attachments from say photo1.* to customer account number like 4542122.* at the time the user attaches is this possible...

 

[Minty]

What happens when that customer has a second photo?
What would be the naming convention if that occurs?

 

[Minty]

It might be easier to have a network folder per customer and store just their photo's in the folder rather than using the attachment field.
You can store the filename, and use the account number as the folder name.

Then you can easily find that customers photos by calculating the path and listing all their files.
If your top-level network path ever changed it would be a simple global change to update all the paths for the stored files.

Using your current method would involve searching to find the last used _99 numbered file by customer then incrementing it.

 

[Minty]

Attachments in an email are rather different to attachments in a database.
Assuming you have Outlook as an email client, the best route would be to use Outlook Automation to create the email and add as many attachments as you require.

You can use automation to add either the Access attachment or any other file you can get to with a file path.

 

[Number11]

Ok so yes i have behind a button code that sends an email using a template, so now need to include all the photos within the same query? The field name within the query is "Attachments"

Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile

vFile = "J:\Test Template.xlsx"
Set rst = CurrentDb.OpenRecordset("Test")
If rst.RecordCount = 0 Then
    Dialog.Box "No Requests Today!", vbInformation, "Database Message"
    Call fncLogOTHER
Me.LastRefreshDateTime.Form.Recalc
    Else
   
   rst.MoveLast
   Dialog.Box "A Total Of: " & rst.RecordCount & " Requests Found And Will Be Emailed!", vbInformation, "Database Message"

   rst.MoveFirst


With XL
   .Visible = False
   .Workbooks.Open vFile
   .Sheets("Other").Select
   .Range("A4").Select
   .ActiveCell.CopyFromRecordset rst
   .ActiveWorkbook.SaveAs filename:=("C:\Test Notification " & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"), password:="Test"
   .ActiveWorkbook.Close
   .Application.Quit
 
Dim signature As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail ' This creates a blank email and captures the users default signature.
    .BodyFormat = olFormatHTML
    .Display
End With

signature = OutMail.HTMLBody
strBodyText = "Hi,<br>" & _
              "Please find attached picking requests.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<br><br>Best wishes,<br>"

With OutMail

    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Request Notification"
    .HTMLBody = strFntNormal & strBodyText & strTableBody & "<br><br>" & signature
    .Attachments.Add "C:\Test Notification " & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"
    If Dir("C:\Test Notification " & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx") <> "" Then
    'Send Email
.Send
'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing


Kill " C:\Test Notification " & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"


Dialog.Box "All Data has been exported and has been sent", vbInformation, "Task Complete"
Call fncLogOTHER
Me.LastRefreshDateTime.Form.Recalc



Else
'Don't sent email
Dialog.Box "email was not sent as attachment was missing - please try again", vbInformation, "Something went wrong!"

End If
End With


End With
End If
End Sub

 

[Number11]

any help would be grateful

 

[Number11]

any help would be grateful

 

[theDBguy]

any help would be grateful

Have you seen this?

Work with attachments in DAO

Office VBA reference topic

docs.microsoft.com

 

[Cronk]

The code to rename a file is

Name "C:\temp\aaa.txt" as "C:\temp\bbb.txt"

 

[Number11]

Have you seen this?

Work with attachments in DAO

Office VBA reference topic

docs.microsoft.com

Thanks yes this does help ,so i would need to adapt this code to 1st save the photo to a disk folder and then change my send email code to attach all photos within the folder and the send email ummmm

 

[Number11]

cant get this to work..

Recordset.Fields("FileData").LoadFromFile ("Attachments")
Set rsPhotos = db.OpenRecordset("Send_Photo")
Set rsAttachments = rsPhotos.Fields("Attachments").value
While Not rsAttachments.EOF
rsAttachments.Fields("FileData").SaveToFile "C:\Test\Zip\Photo"
rsPhoto.MoveNext
Wend

The query name i have the data along with the attachment is called Send_Photo

 

[theDBguy]

cant get this to work..

Recordset.Fields("FileData").LoadFromFile ("Attachments")
Set rsPhotos = db.OpenRecordset("Send_Photo")
Set rsAttachments = rsPhotos.Fields("Attachments").value
While Not rsAttachments.EOF
rsAttachments.Fields("FileData").SaveToFile "C:\Test\Zip\Photo"
rsPhoto.MoveNext
Wend

The query name i have the data along with the attachment is called Send_Photo

The first line in that code snippet looks wrong, and I didn't think you would need to use LoadFromFile here.

 

[Number11]

The first line in that code snippet looks wrong, and I didn't think you would need to use LoadFromFile here.

yeh removed that also same issue does'nt seem to work

 

[theDBguy]

yeh removed that also same issue does'nt seem to work

Doesn't seem to work as in no file showed up in the folder?

 

[Number11]

Doesn't seem to work as in no file showed up in the folder?

Getting run-time error 424 - Object Required...

then in debug this is highlighted in yellow

Set rsPhotos = db.OpenRecordset("Send_Photo")

 

[theDBguy]

Getting run-time error 424 - Object Required...

then in debug this is highlighted in yellow

Set rsPhotos = db.OpenRecordset("Send_Photo")

How did you declare rsPhotos? How did you set db?

 

[Number11]

How did you declare rsPhotos? How did you set db?

i didt so maybe thats the issue?

 

[theDBguy]

i didt so maybe thats the issue?

Please post your complete code.

 

[Number11]

this is all i have on a button..

Private Sub Command48_Click()
Set rsPhotos = db.OpenRecordset("Send_Photo")
Set rsAttachments = rsPhotos.Fields("Attachments").value
While Not rsAttachments.EOF
rsAttachments.SaveToFile "C:\Test\Zip\Photo"
rsPhoto.MoveNext
Wend
End Sub

 

[Minty]

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.