Filter report before emailing

[Neobeowulf]

Team,

I'm trying to figure out how to get access to filter a report based off my selection in a combo box before emailing.

So, when I pick "The boss" in my combo box then hit my email button, it would filter all my reports by whats with the boss then email it to him. I'm pretty sure I need to use Forms!Main.Namecmb, but i'm not sure how/where to put it in the code.

Lastly, .Send is not working at the end of the code. Suggestions?

Private Sub Command82_Click()
Dim strRep As String
Dim strDPath As String
Dim strFName As String
' What report to send
strRep = "Pending Reports"
' Initial Path
strDPath = "S:\CCEA\Dashboard\Daily EPR-OPR-DEC Slides/"
' Filename
strFName = "Evaluations.pdf"
' Output report as pdf
DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
' Send the report to whoever
Send_Email (strDPath & strFName)
End Sub
Private Sub Send_Email(strDoc As String)
Dim sTo As String
Dim sCC As String
Dim sBCC As String
Dim sSub As String
Dim sBody As String
Dim strCC As String
Dim OutApp As Object
Dim OutMail As Object
Dim varPress As Variant
    ' Get the email address from the current form control
    sTo = "[EMAIL="Russell.Huffstetler@afcent.af.mil"]Email name[/EMAIL]"
 
    '  Set the subject
    sSub = "Evaluations Report"
    ' Build the body of the email
    sBody = "Sir," & vbCrLf & vbCrLf
    sBody = sBody & "This is a test of my database email system.  Hope it works!"
 
    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""
 
 
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSub
        .Body = sBody
        .attachments.Add (strDoc)
        .SEND
 
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[pbaldy]

One way, using the form reference you noted:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[Neobeowulf]

I didn't understand that at all. I'm starting to think since I only have 3 people to email these reports to, that it might be easier just to make 3 different reports that are already filtered vs. trying to filter via a combo box & VBA.

 

[Neobeowulf]

Hah, had a "EUREKA!" moment and Filter on load makes sense now.

Next question, why isn't .Send working in the code? I get an error 424 Object Required.

I also tried OutlookMessage.Send and same thing happens.

 

[pbaldy]

Looks okay offhand. Does .Display work?

 

[Neobeowulf]

.Display works just fine. The email will display even if I have just .Send

 

[pbaldy]

Odd. Does it have everything it needs to send, like a valid email address? If you use .Display can you just click send and have it go out?

 

[Neobeowulf]

The email is completely filled out when the button is pressed and also has a valid email address.

The email will display, but the error still comes up with .Send highlighted & the error 427.

It wouldn't kill us to have to push the send button, but i'm just really curious why out of everything .Send & OutlookMessage.Send don't work.

 

[pbaldy]

Is Outlook open when you run this? Can you post the db here?

 

[Neobeowulf]

I've tried it with outlook open & closed, the result is the same. It will display the email completely ready to go, but it just won't send it. I don't get any errors from Outlook at all. Just the db error 427 saying .Send doesn't work.

I cannot post the db.

Maybe change Dim Outmail as string instead of as object?

Just incase it helps anyone else reading this thread, I didn't need to do the OnLoad VBA code since I have my filter set in the criteria of my report query. [Forms]![Main].[Cmbbox] All I had to do is turn on Filter OnLoad and it pulled the report pre-filtered and ready to go in the email.