acOutputReport to PDF

[sdel_nevo]

Hi All

I have been trying without luck to change the output path for the PDF file when the e-mail button on a form is clicked.

the code i use is below

Private Sub E_Mail_Invoice_Click()
'This Command Opens the report in hidden mode so that the calculations work on the Report when it's output as a PDF
DoCmd.OpenReport "Invoice", acViewPreview, "", "", acHidden
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, "C:\Users\Steve\SkyDrive\Access\Invoice.pdf"


Dim OlApp As Object
Dim objMail As Object


On Error Resume Next 'Keep going if there is an error
Set OlApp = GetObject(, "Outlook.Application") 'See if Outlook is open
If Err Then 'Outlook is not open
Set OlApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If


'Create e-mail item
Set objMail = OlApp.CreateItem(olMailItem)


'Set objOutlookAttach = .Attachments.Add("C:\Users\Steve\SkyDrive\Access\I nvoice.pdf")
With objMail


'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Me.EMail.Value
.Subject = "Forest PC Repairs Invoice Attached"
.HTMLBody = "Dear" & " " & Me.Full_Name.Value & " " & Me.Email_Body_text.Value
.Attachments.Add ("C:\Users\Steve\SkyDrive\Access\Invoice.pdf")
.Display


End With
DoCmd.Close acReport, "Invoice", acSaveYes


Me.Order_Sent_Date = Date
End Sub



on a form called "Company Details" i have a text box that is bound to a field in the Company Details table called "FilePath" i would like this to be used for the acOutputReport

how can i reference the "FilePath" in the "Company Details" Table into the code above?

any help would be awesome.

[pbaldy]

If it's on the form and contains the full path:

DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, Me.FilePath

[sdel_nevo]

Hi Paul

on it's not on the main form, can i replace me.FilePath with [Company Details]![FilePath]

[Company Details]![FilePath] is the table and feild name that holds the file path location c:\testdata\invoice.pdf for example

Steve

[pbaldy]

Not like that; you'd have to use a recordset or DLookup() if it's not in the form's source.

[sdel_nevo]

Hi Paul that works

DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, DLookup("FilePath", "Company Details", "CompanyID = 1")

many thanks for the heads up

is there a way that the end users just need to enter a folder path and have access add the "invoice.pdf" to the end?

just to make everything simple

many thanks in advance

steve

[pbaldy]

Sure, this type of thing:

DLookup("FilePath", "Company Details", "CompanyID = 1") & "invoice.pdf"

As long as it results in a valid path.

[sdel_nevo]

Hi Paul

You are a star

just want to say, how good this site is, compared to some PHP forums i visit this is miles more friendly and great well done

[pbaldy]

Happy to help, and thanks, that's nice to hear! Others created that atmosphere, I'm just trying to help out.