I have returned to this this morning but I think I am getting into a bit of a mess!!
In my previous approach the Reports were not saved as files, the attachments were simply the Reports that were created within Access and attached without being saved anywhere. That, to my mind, is the simplest approach if only I could get them attached to the emails!
I have however tried to incorporate Paul's last suggestion into my code but I am getting the message "The OutputTo action was cancelled". My code as it stands now is
Code:
Private Sub Command62_Click() Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strEmail As String
Dim outl As outlook.Application
Set outl = New outlook.Application
Dim mi As outlook.MailItem
Set mi = outl.CreateItem(olMailItem)
Dim myattachments As Variant
Dim strPath As String
strDelim = """" 'Delimiter appropriate to field type.
strDoc = "July Cheque Email"
'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
strWhere = ""
strDescrip = ""
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
strEmail = .Column(1, varItem)
End If
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Email: " & Left$(strDescrip, lngLen)
End If
End If
'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
strPath = "c:\Our Folders\BWMC"
DoCmd.OutputTo acOutputReport, strDoc, acFormatPDF, strPath
Set myattachments = mi.Attachments
myattachments.Add strPath
mi.Body = "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation."
mi.Subject = "Payment from Bangor Worldwide Missionary Convention"
mi.To = strEmail
mi.Send
Set mi = Nothing
Set outl = Nothing
'DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation. ", False
DoCmd.Close acReport, strDoc
Next
End With
DoCmd.Close acReport, strDoc
End Sub
Could someone untangle this for me or revert to the original approach?