Send pdf with outlook without saving actual file

[107mb]

I want to send an e-mail with a report as attachment.

On the internet i have found two options, and both are not the best solutions.

1. option

DoCmd.SendObject acSendReport, sReport, acFormatPDF, sMailAdres

With this construction I don't have enough control over the mail, and worse, I get an Outlook message when the mail is sending which I have to answer.

2. option

Dim oMail As MailItem
Dim oAtt As Attachment

Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)

DoCmd.OpenReport ReportName:=sReport, View:=acViewPreview, WindowMode:=acHidden
Set rpt = Reports(sReport)

oMail.Body = "Body of the email"
oMail.Subject = "Test Subject"
oMail.To = "107mb@mail.nl"
[B]oMail.Attachments.add rpt[/B]
oMail.Send

with this construction I have to enter an existing report.pdf on my filesystem (bold line), but I want to pass the rpt object straight to the mail as an attachment. How can I build this?

 

[AOB]

Why not use the .SendObject command to save the pdf to a temporary location, then use that saved file as your attachment, then simply delete the temporary file?

 

[AOB]

Dim appOutlook As Object
Dim objMailItem As Object
Dim strOutputPath As String
Dim strReportName As String
 
strReportName = "<Insert Report Name>"
 
strOutputPath = CurrentProject.Path & "\" & strReportName & ".pdf"
 
    ' Customise to suit - can use SpecialFolders(0) to use local temp location
 
DoCmd.OpenReport "rptMyReport", acViewReport, , , acHidden
 
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, strOutputPath
 
DoCmd.Close acReport, "rptMyReport", acSaveNo
 
Set appOutlook = CreateObject("Outlook.Application")
Set objMailItem = appOutlook.CreateItem(0)  ' olMailItem
 
With objMailItem
  .Subject = Replace(Dir(strOutputPath), ".pdf", "")
  .Attachments.Add strOutputPath
  .Close (0)  ' olSave
  .Display
End With
 
Kill strOutputPath
 
Set objMailItem = Nothing
Set appOutlook = Nothing