I want to create .pdf document and save it in attachment control

[MacroScope]

Hi- I have a report that is generated from data on an Access 2007 form. I am already emailing that report as a .pdf attachment. What I'd like to do is to also save a .pdf copy in an Attachment control on the form that generates the report in the first place.

Currently I have to go through multiple steps of opening the email, saving the file to hard drive, and then going back to Access form's Attachment control, opening it, and directing the newly saved file to it.

Is there a way to accomplish what I'm trying to do without so many intervening steps?

All help is always appreciated.

 

[MajP]

I know of no way to directly enter the report into an attachment field as PDF. So I save it to the harddrive, read it into the attachment field, then delete it if you want.
So you need something like this.

Private Sub cmdAttach_Click()
  Dim tempLocation As String
  Dim fldName As String
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  fldName = "Attachments"
  tempLocation = OpenReportAndSave("rptProductsByCategory")
  loadAttachFromFile tempLocation, rs, fldName
  'Could add code here to send and email with the outlook attachment using the saved file
  'if you want to remove the temp file at this point
  'VBA.Kill tempLocation
End Sub

Public Function OpenReportAndSave(strReportName As String) As String
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim myMessage As String
    On Error GoTo ErrorHandler
    DoCmd.OpenReport strReportName, acViewPreview
    myCurrentDir = CurrentProject.Path & "\"
    myReportOutput = myCurrentDir & strReportName & Format(Date, "YYYYMMDD") & ".pdf"
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myReportOutput, , , , acExportQualityPrint
    OpenReportAndSave = myReportOutput
    Exit Function
ErrorHandler:
    MsgBox Error$
End Function

Public Sub loadAttachFromFile(strPath As String, rsAll As DAO.Recordset, attachmentFieldName As String)
  'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
  'Add a new record to the tables recordset
  Set rsAtt = rsAll.Fields(attachmentFieldName).Value
  rsAll.Edit
    rsAtt.AddNew
     'This is the confusing part.  The value property of an attachment field returns a recordset of attachments
     'All recordset of attachments has a field named filedata which holds the data.
     'The loadfromfile data loads an attachment from a path
     rsAtt.Fields("FileData").LoadFromFile (strPath)
    rsAtt.Update
  rsAll.Update
End Sub

 

[MajP]

BTW there is also a "SaveToFile" method for an attachment field. This would let you go the reverse method. For example assume you have attachments already in a database and you want to send them as emails. In code you then save the database attachment to disk, create an email, attach the temp file to the email, and then delete the file off the hard drive.

 

[MacroScope]

Thank you. I'll give that a try and let you know how it works out! It'll probably be later today.

 

[MacroScope]

Thank you so much. It works perfectly. I had to modify the code for my own application, of course, but it does exactly what I need it to do, and because of your help I've saved about 3 of 4 minutes on each attachment.

Happy New Year, MajP. That was a Major time saver!

 

[MajP]

Sounds good. Glad it helped.