How to send different attachments to different recipients (1 Viewer)

Hi,

I currently have a code set up which sends an attachment from a drive on my computer to all the email addresses in a certain query. I would like to change the code so that I can add a button that will allow me to send an email with a specific attachment depending on their individual code in a table, e.g each person will receive a specific attachment. At the moment, the attachments are saved with the same file name as the person's specific code.

The code I am currently using is:

Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("People")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
.MoveFirst
Rem Do Until rsEmail.EOF
myRecipient = .Fields(3)
If IsNull(myRecipient) = False Then
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.to = myRecipient
.body = "See attached"
.Subject = "Test Email"
.Attachments.Add ("K:\DatabaseTest\buildingreport.docx")
.Send
End With
End If
.MoveNext
Rem Loop
End With
Set oMail = Nothing
Set oLook = Nothing
End Sub

Concatenate the file name with the field value from the recordset in the Attchments line.

Instead of dealing with attachements, is it something like a report, that is tailored to the individual code?

I have a list of managers, my code scans the list of managers, opens the report which
ONLY reports on that 1 manager, then sends it.
Then moves to the next manager.

Is this similar, or do you have a whole different thing?

they are individual attachments saved in a folder on the computer, not reports made from the database.

the files are created by a different program

usage: EmailAllFilesInDir1Code "cbr"

This will scan the folder, attach every file in the folder having the pvCODE in the filename.

thank you.

where would I put this in my original code?

The button click would call it.

thank you - this is really useful!

where do i enter the information in this code so it knows which query to pull the email addresses from?

also, if i want to add a standard text to go with each email, how do i do this?

Youd have a listbox, or combo box on the form.
it lists all emails.
scan the list to see what to send

I'd like it to work how my original code works whereby I press a button on a form and it automatically uses the email address field in the query I've named in the code and just sends the emails?

Each individual person has their own individual "ID" in the query and this ID has exactly the same name as the file that will need to be attached to their email.