Sending out report (1 Viewer)

[ChristopherL]

Hi there, I am in need of a solution for sending out my report with a date variable..

I have two reports that are being generated through a macro.

What I want to do is send out these reports as PDF-files with names like:
Counterparty report as of =date()
Counterparty 0 report as of =date()

And I want to save these in a folder let's say:
"C:\Documents and Settings\All Users\Desktop"

Is the solution making a VBA macro for this or is there any other way you would recommend?

I am quite a rookie at VBA, if VBA is the solution, anyone got a code like this inhand? Or maybe a guide somewhere of how to complete it?

Thanks for reading
Regards,
Chris

 

[pr2-eugin]

You can use DoCmd.OutputTo method, using acFormatPDF as the argument.

PS: Can use VBA or MArcro.

 

[ChristopherL]

You can use DoCmd.OutputTo method, using acFormatPDF as the argument.

PS: Can use VBA or MArcro.

Thank you for that, I'll see what I can do with it
What about the saving part?

/ Regards C

 

[pr2-eugin]

The method will export/save the report with the chosen format, in the chosen location. If left blank will prompt you to do so !

 

[pr2-eugin]

Is there a reason why you have used SendObject method where the method proposed to you was OutputTo?

 

[ChristopherL]

Is there a reason why you have used SendObject method where the method proposed to you was OutputTo?

A collegue of mine told me that it is what we should use for sending out the report..
My concern now is editing the title of the file instead..
Right now it's named after the query, I want to create my own name of the file as it's converted to pdf ?

Do you know if it is possible to this code?

Option Compare Database


Sub Fix1()
DoCmd.SendObject acSendReport, "MarketRiskControl_HighestDiffs_AsOfCurrentDate", "PDFFormat(*.pdf)", "christopher.larsson@seb.se", "christopher.larsson@seb.se", , ("SD Counterparty Report as of " & Format(Now(), "yyyymmdd")), "Regards, Trading Risk Control", False
End Sub

 

[pr2-eugin]

Okay I thought you only wanted to save the Report as PDF. I think you need a mixture of DoCmd.OutputTo and some VBA code. As DoCmd.SendObject cannot rename the object. Try the following.

Option Compare Database

Sub Fix1()
    Dim fPath As String
    fPath = "C:\Documents and Settings\All Users\Desktop\SD Counterparty Report as of " & Format(Now(), "yyyymmdd") & ".pdf"
    DoCmd.OutputTo acOutputReport, "MarketRiskControl_HighestDiffs_AsOfCurrentDate", acFormatPDF, fPath
    
    sendEmail fPath
End Sub

Sub sendEmail(attachPath As String)
[COLOR=Green]'Modified CODE.
'Orignal : http://www.rondebruin.nl/win/s1/outlook/signature.htm
' Working in Office 2000-2013[/COLOR]

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Regards, Trading Risk Control"

    On Error Resume Next

    With OutMail
        .Display
        .To = "christopher.larsson@seb.se"
        .Attachments.Add attachPath
        .Subject = "This is the Subject line"
        .HTMLBody = strbody & "<br>" & .HTMLBody
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

PS: The code can be made a bit more robust !

 

[ChristopherL]

Woho, this seems to be a script that I can definetly use!
Thank you for this so much

One thing tho, how do I setup the attachPath?

Regards,
Chris

 

[pr2-eugin]

attachPath is the argument fPath that you pass from the Fix1 Sub !

 

[ChristopherL]

attachPath is the argument fPath that you pass from the Fix1 Sub !

Ohhh I am stupid!
This works perfect, truly thankful!