unable to attach a report to access email

[zoona]

Hi, getting to grips with the code a bit more now, but now I cannot get the report which is generated to attach to the email hope you can help many thanks

running ms access 2010

 

[ted.martin]

This will do it for you. (put the code into your app and then use the intellisense to understand the syntax. I have copied my code exactly from a working app.

Firstly output the report as a .PDF

DoCmd.OutputTo acOutputReport, "R-Property Brochure", acFormatPDF, conBrochures & lngRef & ".pdf", False, , , acExportQualityPrint

then Call this from your own module (note mine is in a loop for sending the same brochure to multiple eMail addys hence the arAddy(i) for the recipients eMail address)

Call SendeMail("Brochure", Nz(DLookup("[BodyText]", "eMail Bodies"), "BodyType = 'Brochure'"), "Property Brochures from " & DLookup("[Company]", "Company Details"), arAddy(i), conBrochures & lngRef & ".pdf")

Here is the Outlook Module

Public Sub SendeMail(myDoc As String, myBody As String, mySubject As String, myAddy As String, Optional myFile As String)  ' e.g  Brochure, Addy, Path.pdf

On Error GoTo Err_Send

DoCmd.Hourglass True

'// requires Outlook Object Library \\

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem) 'need to re-create Set each cycle.

MyMail.To = myAddy
MyMail.Subject = mySubject


Select Case myDoc

Case Is = "Brochure"
    MyMail.Body = myBody

Case Else
    MyMail.Body = "Good morning" & vbNewLine & vbNewLine & "Please our attachment following your recent enquiry." & _
    vbNewLine & vbNewLine & "Please do not hesitate to contact us should you require any further information." & vbNewLine & vbNewLine

End Select


MyMail.Body = MyMail.Body & vbNewLine & vbNewLine & _
"Kind Regards " & vbNewLine & vbNewLine & DLookup("[User]", "Users", "UserID = '" & Environ("UserName") & "'") & _
vbNewLine & DLookup("[Company]", "Company Details") & vbNewLine & _
"tel:   " & DLookup("[Telephone]", "Company Details") & vbNewLine & _
"fax:   " & DLookup("[Fax]", "Company Details") & vbNewLine & _
"eMail: " & DLookup("[eMail]", "Company Details") & vbNewLine & _
"www:   " & DLookup("[www]", "Company Details")


' Is there an Attachment
If Len(myFile & "") > 0 Then
    MyMail.Attachments.Add myFile, olByValue, 1
End If


MyMail.Send  'This sends it in Standard Outlook


Set MyMail = Nothing
Set MyOutlook = Nothing

Exit_Send:
DoCmd.Hourglass False

Exit Sub

Err_Send:
    DoCmd.Hourglass False
    MsgBox err.Description, vbCritical, "eMailing_" & err.Number
    Resume Exit_Send
End Sub