attach multiple files to an email

[SUZYQ]

I am trying to send multiple small pdf files from Access 2003 via
Outlook to different districts. The problem is, that I don't know the
number of attachments -- it varies by district and by date sent. It
could be 1 or it could be up to 30.

In my email code I tried to loop through a recordset and then keep
attaching the files, but it's not working.

My code is as follows:

Dim cnnLocal as new adodb.recordset
Dim rs As New ADODB.Recordset

Set cnnLocal = CurrentProject.Connection
rs.Open "select Path from tblTemp_ToDistrict where DistrictID = " &
rsDistrict!OfficeID & " and [Path] is not null;", cnnLocal,
adOpenForwardOnly, adLockReadOnly
rs.MoveFirst
Do While Not rs.EOF
mItem.Attachments.Add rs!Path
rs.MoveNext
Loop

Any thoughts?

 

[Guest]

What do you mean by "not working"? What exactly are you getting or not
getting as the case may be?

 

[SUZYQ]

Here's my full code: I'm passing in the SQL statement to pull the path
for the attachments

Public Sub SendAnEmail(strEmailAddr$, strEmailSubj$, strEmailBody$,
Optional _
strAttachment$, Optional strSQL$)
On Error GoTo Err_SendAnEmail

' Dimension of variables
Dim mOutlookApp As Object
Dim mNameSpace As NameSpace
Dim mFolder As MAPIFolder
Dim mItem As MailItem
Dim strRtnValue$

' Initialize variables
Set mOutlookApp = CreateObject("Outlook.application.10")
Set mNameSpace = mOutlookApp.GetNamespace("MAPI")
Set mFolder = mNameSpace.GetDefaultFolder(olFolderOutbox)
Set mItem = mOutlookApp.CreateItem(olMailItem)



With mItem
'From should be from the server address
.To = strEmailAddr
.Subject = strEmailSubj
.Body = strEmailBody
.Attachments.Add strAttachment 'add attachment

' 'for multiple files that need to be attached
Dim cnnLocal As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cnnLocal = CurrentProject.Connection
rs.Open strSQL, cnnLocal, adOpenForwardOnly, adLockReadOnly
rs.MoveFirst
Do While Not rs.EOF
.Attachments.Add rs!Path
rs.MoveNext
Loop
.Display
'.Send
End With

Exit_SendAnEmail:
Exit Sub

Err_SendAnEmail:
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly
Resume Exit_SendAnEmail
End Sub

When it gets to ".Attachments.Add rs!Path", I get the error number 438
(object doesn't support this property or method).