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 = "myemailaddress@email.com"
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!!

Export your report externally in a .PDF and then add as attachments referencing full file path. — Parfait, Nov 25 '15 at 14:04
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. — Stacy, Nov 25 '15 at 14:12
Use the database's current location: `Application.CurrentProject.Path &"\" & "filename.pdf"` so PDF is saved wherever database is located. — Parfait, Nov 25 '15 at 14:23

score 10 · Accepted Answer · answered Nov 25 '15 at 17:23

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 "myemailaddress@email.com"
    .Subject = "Training Roster"
    .Body = "Roster Information"
    .Attachments.Add fileName
    .Send        
End With

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

score 3 · Answer 2 · answered Nov 25 '15 at 14:09

3

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

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

answered Nov 25 '15 at 14:09

David

354
6
19

You cand see more in this web: https://msdn.microsoft.com/en-gb/library/office/ff197046.aspx – David Nov 25 '15 at 14:11

score -1 · Answer 3 · edited Jul 05 '18 at 08:13

-1

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.

edited Jul 05 '18 at 08:13

Bugs

4,491
9
32
41

answered Jul 05 '18 at 08:10

user3627268

9

Ms Access Send Email with Report as Attachment

3 Answers3

Linked

Related