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?

 

[Sue Mosher [MVP-Outlook]]

That looks reasonable, but you didn't show where mItem comes from or whether
you're saving it.

FYI, there is a newsgroup specifically for general Outlook programming
issues "down the hall" at microsoft.public.outlook.program_vba or, via web
interface, at
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.outlook.program_vba

 

[SUZYQ]

Here's my full code with the hope it'll shed more light for you: 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).