How to attach several files to e-mail message (objMessage.AddAttachment)

[CostasM]

I want to send several files attached to one e-mail message using MS Access 2007 (CDO.Message).
I have query where all files are listed and all file's pathes stored in a separate cell as Hyperlinks.

Here is below image which shows what I need:
[IMGnothumb]https://dl.dropbox.com/u/37289693/attach%20severa l%20files.jpg[/IMGnothumb]

and VBA code which I use:

Private Sub btn4Pay_Click()
On Error GoTo btn4Pay_Click_Error
     
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim sFilePath As String
Set rst = New ADODB.Recordset
     
strSQL = "[qryDocsToSend]" 'source of recordset
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
sFilePath = Trim(rst(8)) ' file's path DocHyperLink
     
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "DOCUMENTS" 
objMessage.From = """Sender"" <sender@gmail.com>"   
objMessage.To = """Recepient"" <recepient@gmail.com>"
     
Do While Not rst.EOF 
objMessage.TextBody = Trim(rst(0)) & "  " & Trim(rst(1)) ' documents listed
objMessage.AddAttachment sFilePath' here I need several files to be attached, not only first from rst
rst.MoveNext
Loop
  
rst.Close
Set rst = Nothing
     
objMessage.BodyPart.Charset = "windows-1251"
     
'===SMTP server configuration extracted ===

objMessage.Send

Using this code I can compose textbody as I need, but can not attach ALL files from recordset. I have attached only first file (document1) but 3 times, depends on quantity of lines in my recordset, if 2 lines - 2 times, but always first file.

Any idea to solve it, please

[NeoPa]

Costa,
Your line #11 sets the value of sFilePath, but it sets it only once and outside of the loop. If you want the values of each record to be reflected then you need to change the value of sFilePath within the loop.

You may find that moving line #11 so that it comes after the current line #19 will solve your problem.

PS. I like that you have posted such a clear question. Good work!

[CostasM]

NeoPa,
Thanks a lot, I moved the 11 line inside the loop, now it works!
I was need to get clear answer so I tried to ask clear question ;)

[NeoPa]

CostasM:
I was need to get clear answer so I tried to ask clear question ;)

Very good thinking. It makes such a difference.