sendObject report based on Query in Access 2007

[Minddumps]

SEE SOLUTION IN BELOW REPLIES:

For the longest time I couldn't figure out how to save/e-mail a report based on a Query. After playing around with different options in the Embedded Macro section I figured out [OpenReport] in hidden window mode with the Filter Query inputted allows the SendObject to email only those records opened in the queried report. I soon realized though, that doesn't solve my issue as I have various queries that open with that same form/report. Is there a way to VBA code to save and/or email using the same query that the form opened with?

Also:
1. Is there a way to change the name of the attached file that shows once the save prompt/email opens?

2. Is there a way to email/save all queried records with each as it's own individual pdfs instead of all merged into one?

 

[Minddumps]

I haven't figured out how to change the email attachment filename yet, however, I was given the idea that the report would have to be saved to the computer, then emailed from that location, then delete the saved file. Does anyone have VBA code that has succesfully pulled that off? If so please share

I did find out how to save records (that use the same query that the form opened with) to the computer at a certian path (desktop for example) and with a certain filename. For those who are looking for this here's the code I use:

Private Sub SaveAllComp_Click()
Dim MyPath As String
Dim MyFilename As String
Dim ShowPdf As Boolean
 
'I place the file on the user's desktop
MyPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
 
'State the filename. Here I use YYYY-DD-MM-RECORDID GROUP--MYREPORT.pdf
MyFilename = Format(Date, "yyyy") & _
"-" & Format(Date, "mm") & "-" & Format(Date, "dd") & _
"-" & [RECORDID] & " Group--MYREPORT" & ".pdf"
 
'Open the report using the same query used to open the form.
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "MYREPORT", acViewReport, , Me.Filter
End If
 
'Save the report group.
ShowPdf = False
DoCmd.OutputTo acOutputReport, "MYREPORT", acFormatPDF, MyPath & "\" & MyFilename, True
 
'Let's close our previewed report
DoCmd.Close acReport, "MYREPORT", acSaveYes
End Sub

 

[Minddumps]

I recieved help on this in another thread. Here's the code for anyone else looking for the same solution.

Private Sub MailReports_Click()
    On Error GoTo Err_Email_Click
  
    Dim strReport As String
    Dim MyFilename As String
    Dim MyPath As String
    Dim ShowPdf As Boolean
 
    strReport = "rpt-INSPECTION-WORKSHEETS"
    Me.Refresh
 
'I place the file on the user's desktop
MyPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
 
'State the filename. Here I use YYYY-DD-MM-RecordID Group--Inspection Checklist.pdf
MyFilename = Format(Date, "yyyy") & _
                 "-" & Format(Date, "mm") & "-" & Format(Date, "dd") & _
                 "-" & [RecordID] & " Group--Inspection Checklist" & ".pdf"
 
'Open the report using the form's query and save.
    If Me.Filter = "" Then
        MsgBox "Apply a filter to the form first."
    Else
        DoCmd.OpenReport "rpt-INSPECTION-WORKSHEETS", acViewReport, , Me.Filter
    Reports(strReport).Visible = False
    End If
 
ShowPdf = False
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, MyPath & "\" & MyFilename, False
 
 
'Starting Email code
'Here is where the code will attach the previously created form on the desktop to an email
  
Dim appOutLook As Object
    Dim MailOutLook As Object
        
    'create the report temporarily
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, MyPath & "\" & MyFilename, False
    
    'assign our object references
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
        
    With MailOutLook
        'set the recipient list
        .To = ""
        .CC = "email"
        'set the subject
        .Subject = "Inspection Sheet for " & Me!RecordID & " group"
        
        'set the body text
        .body = "Please view the attached Inspection Sheet Group for " & Me!RecordID & "."
        
        'add the reports we created
        .attachments.Add MyPath & "\" & MyFilename
        
        'Display the email before sending
        .Display
    End With
    
    'tidy up..
    
    'get rid of our object references
    Set appOutLook = Nothing
    Set MailOutLook = Nothing
    'delete our temporary files
    Kill MyPath & "\" & MyFilename

   
  
Exit_Email_Click:
    Exit Sub
  
Err_Email_Click:
    MsgBox Err.Description
    Resume Exit_Email_Click
 
'Finally, close our previewed report
DoCmd.Close acReport, strReport, acSaveYes
End Sub