Form button save report as a pdf, then email 'named' file from disk

[mib1019]

Thanks in advance to anyone who can help me here.

I have a button on a form that runs a report for the current record. I want to keep that button as it is for reviewing what the report looks like before doing the next step(s).

I envision a second button (Save & Send) that will:



1. save the report to .pdf format on user's OneDrive in a designated folder,
2. attach that named file to an Outlook email (the recipient's email address is on the form), and wait for user to add any comments to the body of the email message.

I appreciate you!
MIB1019

[June7]

DoCmd.SendObject can attach report as PDF to email without having to first save PDF to folder.

Can save PDF with DoCmd.OutputTo method.

[mib1019]

I can do it that way, I suppose, but would rather attach the 'named' file from the folder after it's been saved.

Also, on another little issue... The form has a DLookup for the email address of the 'Account Exec' on the current record, on which the report is generated. It displays as just the email address, but when I hover over it it shows 'mailto:' and the address. So when the email is prepared, it looks like this name@email.com#mailto:name@email.com#.

How can I fix that?

Thanks for your help!

[June7]

Then will need to use Outlook automation code, not SendObject. This is a very common topic and many discussions with examples.

Is the email address saved into a hyperlink type field? Hyperlink is composed of 3 parts separated by # character. I don't use hyperlink type field. Is the address not showing properly on email?

[Gicu]

Instead of two buttons you can prompt the user if to display the report in print preview or email it:

'ask if to email
On Error Resume Next
If MsgBox("Do you want to email this report as a PDF attachment?", vbYesNo, "Email report?") = vbNo Then
    DoCmd.OpenReport "rptCurrentRecord_Email", acViewPreview ', "[ID]= " & Me.ID 'updated recordsource to only include the current record
Else
    'DoCmd.SendObject acSendReport, "rptCurrentRecord_Email", acFormatPDF, "", , , "Your subject here" 'without saving the report as PDF


     Dim sFileName As String,sEmail as string
     'sFileName=.......'set your file name here
     sEmail=Left([EmailAddress],Instr([EmailAddress],"#"))  'name@email.com#mailto:name@email.com#
     DoCmd.OpenReport "rptCurrentRecord_Email", acViewPreview, , , acHidden
     DoCmd.OutputTo acOutputReport, , "PDF Format (*.pdf)", sFileName
     vcSendEmail_Outlook_With_Attachment ("Your subject",sEmail,,sFileName,"Your email body here")
End if


Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
 
  
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
  
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody  'pr20111227
'    Debug.Print sAttachment
'   Stop
    OutMail.Attachments.Add (sAttachment)


    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

For your second question please try sEmail=Left([EmailAddress],Instr([EmailAddress],"#")) 'name@email.com#mailto:name@email.com#

Cheers,

[mib1019]

I solved the hyperlink email address problem with a function to strip off the mailto: part. So I'm good there.

Will look into Outlook automation on the forums.

Thanks again for your help!

MIB1019

[mib1019]

Vlad, thanks for that advice.

On the second question, I did strip away the mailto: hyperlink from the email address, so that's working fine now.

Will try your suggestion on the saving and emailing the report. Thanks for your help!!

MIB1019