Ms Access Send Email with Report as Attachment

Question

Using VBA code builder in MS Access, I have been able to write code that opens Outlook and send me an email with the click of a button. I am having problems with adding an attachment. Most code I have found adds files outside the MS Database as an attachment, I would like to add a report created in my database as an attachment.

Private Sub EmailReport_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem

'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
oEmail.To = "[email protected]"
oEmail.Subject = "Training Roster"
oEmail.Body = "Roster Information"

With oEmail
    .Send
    MsgBox "Email Sent"
End With

I have been looking into a command similar to

oEmail.Attachments.Add Me.

..But, I cannot find the correct combination for adding my report. Thanks!!

Covering

Export your report externally in a .PDF and then add as attachments referencing full file path. – Delanty

I don't think this will work for me. There will be multiple users of this database and I cannot anticipate the full path on multiple work stations. – Covering

Use the database's current location: Application.CurrentProject.Path &"\" & "filename.pdf" so PDF is saved wherever database is located. – Delanty

Answer 1

As mentioned, export your report into an external file such as .pdf in order to attach to your outgoing email. Remember a report is an internal Access object and not readily in a file format for email. With DoCmd.OutputTo, you can dynamically create the pdf on the fly date-stamped and in same location as the database for a generalizeable solution for all your users.

Private Sub EmailReport_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As string, todayDate As String    

'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.Path & "\ReportName_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "ReportName", acFormatPDF, fileName, False

'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add "[email protected]"
    .Subject = "Training Roster"
    .Body = "Roster Information"
    .Attachments.Add fileName
    .Send        
End With

MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"

Delanty

Answer 2

You can export your report as PDF by email with this:

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc,Subject, MessageText, EditMessage, TemplateFile)

Alfano

You cand see more in this web: msdn.microsoft.com/en-gb/library/office/ff197046.aspx – Alfano

Answer 3

With using DoCmd.SendObject you need to update Outlook Programmatic Access to turn off warnings about auto sending emails. In my case, administrator of domain enabled this options to change. More information here.

Negotiate