How can I send more than one attachment to outlook in Access 2007

i use this way to send emails using VBA

Dim objol As New Outlook.Application
Dim objMail As MailItem
Set objol = Outlook.Application
Set objMail = objol.CreateItem(olMailItem)
With objMail
.To = "whoever@whoever.com"
.Subject = "TReport Update for " & Format(ddate, "dd.mm") & " data."
.Body = "Hi," & vbCrLf _
& "The Data has been updated, please remember that there may be" & vbCrLf & vbCrLf _
& "changes as this has been automatically generated." & vbCrLf & vbCrLf _
& "Regards and Thanks" & vbCrLf & vbCrLf _
& "Rob." & vbCrLf & vbCrLf
.Attachments.Add pname
.Display
End With
Set objMail = Nothing
Set objol = Nothing

you have to set references to outlook in Access,

you can repeat this line as many times as you like.

.Attachments.Add pname

I just usually change pname to pname2 etc.

pname is a variable that is set earlier in the coding.

Hope this is of use, Rob.

Hi Rob

Thanks for this but I dont seem to be able to set the Report variable. I put:

Dim pname As Report
Set pname = name of report

Do I need to set the report as an Object, eg:

Dim appAccess As Object
Set appAccess = report

Regards, Julie

This was written several years back but it might help. Not pretty.

Private Sub CmdEstart_Click()
On Error GoTo Err_CmdEstart_Click
Me.Refresh
Dim Atchfile As String
Dim IntFile As String
Dim Message As String
Dim App As Object
Dim ITM As Object
Dim DB As Database
Dim Regnme As Recordset
Dim Esubject As String
Dim SendTo As Variant
Dim Ebody As String
Dim NewFileName As String
Set Xfile = GetObject(, "Excel.application")
With Xfile.ActiveWorkbook
Atchfile = .Path
End With
If Me!Check15 <> -1 Then
Message = MsgBox("You Have Not Put A Check In The All Jurisdictions Selected Box Yet.", vbOKOnly)
GoTo Exit_CmdEstart_Click
End If
SendAuditPack
Insert54
CommonDialog1.CancelError = True
CommonDialog1.InitDir = Atchfile
CommonDialog1.Flags = &H1000& Or &H800&
CommonDialog1.Filter = "All PDF Files (*.pdf)|*.pdf|Word Files (*.doc)|*.doc"
CommonDialog1.DialogTitle = " Select The Interest Report. "
CommonDialog1.ShowOpen
IntFile = CommonDialog1.Filename
Set DB = CurrentDb
Set Regnme = DBEngine(0).Databases(0).OpenRecordset("qrypickJuris")
With Regnme
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
Const ctime = 1500
Call sSleep(ctime)

SendTo = !EMailAddr 'SendTo & !EMailAddr & ";"

Esubject = "IFTA Audit" & " " & !JurisID
Ebody = " The attached Audit Summary is for" & " " & Me.TxtTpName & ", " & "their license number is " & Me.TxtLinNo & " and covers the years " & Me.TxtYrs & "." _
& vbCrLf & vbCrLf & vbCrLf _
& " EMAIL Confidentiality: " _
& "This communication may contain privileged information. " _
& "If you are not the intended recipient, or believe that you may have " _
& "received this communication in error, please reply to the sender " _
& "indicating that fact and delete the copy you received. In addition, " _
& "you should not print, copy, retransmit, disseminate or otherwise use the information in this communication."

NewFileName = Atchfile & "\" & "IFTAJuris" & " " & !JurisID & ".pdf"

Set App = CreateObject("Outlook.Application")
Set ITM = App.CreateItem(olMailItem)

With ITM
.Subject = Esubject
.To = SendTo
.Body = Ebody
.Attachments.Add NewFileName
.Attachments.Add IntFile
.Display 'This opens the e-mail for viewing
' .Send 'This sends without opening the e-mail (you will get the prompt)
End With
Call sSleep(ctime)

SendKeys "%{s}", True 'This sends the e-mail (won't work if you are stepping through)
.MoveNext
Loop
End If
End With

RemoveFiles

Set Xfile = Nothing
Set ITM = Nothing
Set App = Nothing
Regnme.Close
Set Regnme = Nothing

End

Thanks for this code, it looks too 'scary' for me. Am I right in thinking this code attaches an Excel Doc for emailing? I need to attach 2 Access Reports.

Rob's code nearly works. I just need to set the variable for the Report. Not sure how to do this, any ideas

Thanks
Julie

Why not simply send a single report embedding the 2 reports as subreports ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Thanks for your suggestion, but I need them as separate Reports to enable user to choose which report to print.

So, send 2 mails.