Email attachments from subtable / recordset

[Flint2048]

Hello, Can anyone help with this at all...

I have a Form (FActions) that shows a record from a table (TActions).
The form also shows a subform (TLinks_subform) that relates to a "linked files" table (TLinks).

The User can associate reports (TReport) with the Action record (one to many) by adding Report references to the Linked files table. This table simply holds associations between the TActions and TReports records. The Linked Table field that brings the two together is [ARef] or Action Reference number.

The report table lists the reports but these also physically exist as files in a directory e.g. R0001.pdf, R0002.pdf etc.

TLinks looks like this...

ID ARef Link
1 A0001 R0004
2 A0001 R0017
3 A0005 R0023


I have a routine that opens an new email and fills out To, From and Body text with appropriate data contained in the Action Form / record.

What I need is some sort of VB loop that examines the linked files table for all associated records and then checks a directory for the presence of the physical files and (if found) attaches each one in turn to the newly created email. In the example above, an email for Action A0001 would open and attach two files (R0004.pdf and R0017.pdf).

There are a number of posts found around email and attachments but nothing I can find that covers this specific requirement.

All help would be appreciated.

Thanks

Flint2048

 

[arnelgp]

if there is a button to open a new mail,
use the Recordset of TLinks_subform, snippet
would look like this:

Private Sub button_Click()
' other routine to setup email here
'
'
Dim rs As DAO.Recordset
dIM strFile As String
Set rs = [TLinks_subform].Form.RecorsetClone
With rs
	IF Not (.BOF And .EOF) Then .MoveFirst
	While Not .EOF
		' put the correct path here
		strFile= Dir("D:\Files\" & ![Link] & ".pdf")
		If strFile <> "" Then
			' add the attachment
			' replace ObjMailItem with your mail item Object
			ObjMailItem.Attachments.Add strFile
		End If
		.MoveNext
	Wend
	.Close
End With
Set rs = Nothing
'
' rest of code to send the email
'

 

[Orthodox Dave]

What I need is some sort of VB loop that examines the linked files table for all associated records and then checks a directory for the presence of the physical files and (if found) attaches each one in turn to the newly created email. In the example above, an email for Action A0001 would open and attach two files (R0004.pdf and R0017.pdf).

A few considerations that I had to think about in my database:

1. If a file is missing in the folder, will you just go ahead and only add the files that are there, or do you want the code to tell you which file is missing and ask you to save the file before carrying on?

2. Are any of the files so large that sometimes you couldn't send them all in one email?

3. Are the files always in the same format (e.g. PDF)?

My code became very long and complicated, but as a result, if the files add up to more than a constant size (e.g. 10Mb), it can either split the email into "Subject - email 1 of 5" etc, or give an option to create a folder for dragging the files to an FTP site or similar. And another option to include either pdf files or non-pdf files or both.

 

[Flint2048]

Thanks very much for the replies.

The suggested VB looks like a great start but there is one modification required I think.
Not all the items in the subform / recordset should be attached. Only those where TLinks [ARef] matches the current Action Record on the main form (FActions). (This is a text box with field named "Ref").

If this check could be added then I think we would be very close to testing.

In terms of files...

The files are not huge so I think we can get a way with adding the anticipated 4 or 5 to a single email.

You're right that the files might (on some occasions) be .doc or other format so some form of file extension wildcard would be useful.

If a file is missing, I am happy to ignore. It's up to other team members to ensure that files are where they should be.

All good suggestions !

If the code could be tweaked a little as suggested I can give it a go.

Many thanks.

 

[arnelgp]

If the subform is linked via Link Master/Child Field, then the code is correct and only those records showing on the subform (filtered through link) are included.

 

[Flint2048]

Of course !! ( I forget how helpful Access is at doing some of the hard work for you).

I'll give this a go today and let you know how it goes.

Thanks for your time.

 

[Flint2048]

Hi.

I am trying the code above but am getting a runtime error 2465 "Application defined or Object defined error" in the following line...

Set rs = [TLinks_subform].Form.RecorsetClone

I have also tried...

Set rs = Forms![FActions]![TLinks_subform].Form.RecordsetClone

Same result.

 

[Flint2048]

Solved it !!!

Spotted Typo in original code "RecorsetClone" ( I read this lots of times and missed it)

Modified code also includes wildcard to attach multiple file types (not just PDF).

Code now reads:

' Add attachments for each any every linked file
Dim rs As DAO.Recordset
Dim strFile As String
Dim Defaultfilepath As String
Defaultfilepath = Me.Text148 & "" 'where text148 (on the form is) =DLookUp("[path]","[tsettings]")
Set rs = [TLinks_subform].Form.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
' set file and path
strFile = Dir(Defaultfilepath & ![Link] & ".*")
If strFile <> "" Then
' add the attachment
olNewEmail.Attachments.Add (Defaultfilepath & strFile)
End If
.MoveNext
Wend
.Close
End With
Set rs = Nothing



Thanks to arnelgp for providing the code and all other contributions.

Flint2048

 

[Minty]

As a tip, two things - Always make sure you have Option Explicit at the top of your code modules, it forces variable declaration and makes most typo's obvious. You can make this happen automatically in the editor options.

Secondly, its not a bad idea to compile the code before trying to run it - it would have highlighted this error immediately.

 

[Flint2048]

Thanks very much Minty.

Everyday is a school day.

Cheers

 

[Mark_]

One other trick, Have an "Error String" added to your routine that attaches files.

Do the attachments before you fill in the .body for the Email.

In your loop to add files, check to see if they exist. If not,

ErrorString = ErrorString & Chr(10) & "Could not attach " & [Link]

When you get ready to add the body

if ErrorString = "" then
   .Body = [Body]
Else
   .body = [Body] & ErrorString
End If

That way you let the recipient know what files were not attached.

 

[Flint2048]

Nice touch Mark !

May have a play with this.

Thanks

Flint2048