CODEWITHSUNDEEP
ms-access-2007
caryw
caryw

Reputation: 23

Access VBA save object Report.pdf to a specific path with a unique name

I'm familiar with VBA but I am not a programmer so any help I can get in this matter is greatly appreciated. I have a report object that is mailed as a .pdf file. This portion of the code works fine but I would like to be able to save a copy of this file to a specific location with a unique name that includes the date and time the file was created. The first set of code is the SendObject that works the second set of code does not work, it is a separate procedure I have been testing to save the object. Once I can get it working I was going to integrate it into first. I would appreciate any help.

Private Sub Command21_Click()

DoCmd.SetWarnings (False)
Dim mailto As String
Dim ccto As String
Dim bccto As String
    mailto = "[email protected]"
    ccto = ""
    bccto = ""
emailmsg = "The attached document is the updated Case Log." & vbNewLine     
& "Please review the report, contact me and you find any discrepancies. "&vbNewLine & vbNewLine & "Thank You, " & vbNewLine & vbNewLine & vbNewLine & "Cary S. WInchester" & vbNewLine & "American Commercial Barge Line" & vbNewLine & "Safety Department"

mailsub = "Updated Case Log Report"

On Error Resume Next

DoCmd.SendObject acSendReport, "rpt_CaseLog-CurrentYear", acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
DoCmd.SetWarnings (True)

End Sub

This is the second set of code to attempt to save the object to a specific path with a unique name.

Private Sub Command23_Click()

On Error GoTo Command23_Click_Err
  Dim filePath As String
  filePath = "C:\Work\ACBL\Access Dbase\DayCount" & "CaseLog" _
        & Format(Date, " yyyy/mm/dd") _
        & Format(Time, " hh:MM:ss") & ".pdf"
  DoCmd.OutputTo acOutputReport, "rpt_CaseLog-CurrentYear", _
        "PDFFormat(*.pdf)", filePath, _
        False, "", , acExportQualityPrint
Command23_Click_Exit:
  Exit Sub
Command23_Click_Err:
  MsgBox Error$
  Resume Command23_Click_Exit

End Sub

Upvotes: 0

Views: 4860

Answers (1)

caryw
caryw

Reputation: 23

Thanks Bit Accesser but that was not the problem, the code was laid out as it should be; however, the Date and Time formats were using characters that could be used for a file name, specifically, the colons and the backslashes were causing it to fail. Below is the corrected code. There are a few other spots I tweaked but this works great.

Private Sub Command25_Click()
On Error GoTo Command25_Click_Err

Dim filePath As String

filePath = "C:\Work\ACBL\Access Dbase\DayCount\Reports\"

  DoCmd.OutputTo acOutputReport, "rpt_CaseLog-CurrentYear", acFormatPDF, _
  filePath & " Case Log Update" & Format(Now(), " dd-mm-yyyy hhmmss") & ".pdf"

Command25_Click_Exit:
  Exit Sub

Command25_Click_Err:
  MsgBox Error$
  Resume Command25_Click_Exit

End Sub

Upvotes: 1

Related Questions