e-mail report based on data entered into form

[Funkbuqet]

I have a form that I am using to collect information about complaints we receive from the public. Once we receive the complaints it is my job to dispatch one of our feild teams to respond to the complaint. What i would like to do is have a button at the bottom of the entry form that will send the data I just entered to the appropriate feild team (Designated in the "AssignedTo" box on the form). From what I have read I think I will need to export the data from the form to a report and then e-mail the report out. But I am having a hard time finding a way to automate this with the press of a button.
it would be nice if I could get the data I enter into the form to create the body of the e-mail. Any suggestions are appreciated. Thank you.

 

[Trevor G]

Go to my downloads page on my website I have a database sample you could look to use. You will have to look behind the form to adjsut one or two things. Look for the line that says send emails from access

http://www.wuit.co.uk/downloads.htm

 

[Funkbuqet]

Excellent thank you. This has a lot of the coding in it I can use to adapt to my situation. Is there a way via VBA to influence an existing report based on an existing query? In other words, if I have a query that feeds a report can I in VBA code generate said report for a particular record. So that after I fill in the form and hit the e-mail button it will either attach or insert into the body of the e-mail the corresponding report? I hope I am explaining this clearly.

 

[Trevor G]

In your form I assume you are looking at a record so if it is the record then you would need to look to filter the ID field (If you have one) to the ID field in the Report so you only see a single record. You can then look to use the SendObject command.

Take a look at this thread as it gives you a sample.

http://www.access-programmers.co.uk/forums/showthread.php?t=154602

If you get stuck around the report then look to use a macro to open the report but add the criteria so it looks at the form ID field and is equal to the Table ID Field.

 

[Funkbuqet]

Ok i am getting closer. Thank you for all of your help so far. The link you posted lead me to realize that i needed to use a parameter query (had no idea that's what I was looking for was called). I wrote some test code into my form tied to a button click just to see if i could get it to produce the report I wanted. It kinda did.The code below runs the parameter query but it does not use the input I provided (Me.ComplaintID). when I click the e-mail button a box pops up asking me for the [ID]. If I enter the number it does print the correct report. Also, if I put a stop point into the code it recognizes Me.ComplaintID as the number it should from the form.

Private Sub e_mail_Click()
    
Dim db As DAO.Database
Dim qdf As QueryDef
    
Set db = CurrentDb
Set qdf = db.QueryDefs("ComplaintReportQuery")

qdf.Parameters("[ID]") = Me.ComplaintReportID

DoCmd.OpenReport ("ComplaintReportingReport")

End Sub

Is there something wrong with my syntax? The record source for the report is "ComplaintReportQuery". Do I need to do something special to the report record source when using a parameter query?

 

[Trevor G]

What I suggest is create a form based on the table or query, then create a MACRO which will preview the Report when you click a command button, but within it you add criteria in the Where Condition is at the bottom part of the window which will look at the record in the form and becomes equal to the ID field in the table, so the condition would be something like this

[Forms]![frmReport]![id]=[tblEmployee]![id]

Then you can drag the macro onto the form (in design view) and it will create a command button, then you can look to convert the macro into VBA code which will give you Error Handling and the code would look like this:

Private Sub Command2_Click()
On Error GoTo Command2_Click_Err
    DoCmd.OpenReport "rptEmployee1", acViewPreview, "", "[Forms]![frmReport]![id]=[tblEmployee]![id]", acNormal

Command2_Click_Exit:
    Exit Sub
Command2_Click_Err:
    MsgBox Error$
    Resume Command2_Click_Exit
End Sub

 

[Funkbuqet]

I will try something like that, but I do not really want to preview the report. In the end I want to click the button and it e-mails the report out. I was just using OpenReport in the code above as a proof of concept that the report would be produced properly. Do you know why the code I submitted isn't accepting the ID input I specify?

 

[Trevor G]

I would look to preview the report to make sure I see the right data.

You can use SendObject so if you preview the report and include the SendObject it should give a copy of the report in your email.

 

[Funkbuqet]

I got it to do what I wanted!! Thank you very much for all of your help. Instead of using a parameter query I just added a where clause making ComplaintReportID = [Forms]![ComplaintReportForm]![ComplaintReportID]. The query only works when the form is open, but that suits my needs just fine. I have included the code I used below in case it could be helpful to anyone with a similar question. Again Thank you very much.

Dim appOutlook As Object
Dim MailOutlook As Object

' This allowed me to output the PDF file with a specific name 
' (something I don't think you can do with SendReport)
DoCmd.OutputTo acOutputReport, "ComplaintReportingReport", acFormatPDF,  "c:\temp\Complaint Report " & Me.NearestCity & " - " &  Month(Me.ReportDate) & "-" & Day(Me.ReportDate) & "-" &  Year(Me.ReportDate) & ".pdf", False

Set appOutlook = CreateObject("outlook.Application")
Set MailOutlook = appOutlook.CreateItem(olMailItem)

With MailOutlook
    ' This allowed me to use the Assigned to feild on my form to designate which 
    'Outlook distribution list to send the e-mail to
    .Recipients.Add ("Complaint Reports " & Me.AssignedTo)
    If Me.InNPS = True Then
        .Recipients.Add "Optionalemail@gmail.com"
    End If
    .Subject = [Subject goes here]
    .Body = [Body goes here]
    .Attachments.Add ("c:\temp\Complaint Report " & Me.NearestCity  & " - " & Month(Me.ReportDate) & "-" &  Day(Me.ReportDate) & "-" & Year(Me.ReportDate) & ".pdf")
    .Display
    ' Display will bring the e-mail up so you can view or add to it. if you want it to just send use .Send
End With

Set appOutlook = Nothing
Set MailOutlook = Nothing

'This deletes the PDF
Kill ("c:\temp\Complaint Report " & Me.NearestCity & " - " &  Month(Me.ReportDate) & "-" & Day(Me.ReportDate) & "-" &  Year(Me.ReportDate) & ".pdf")

 

[Trevor G]

Pleased to read you have a working solution and also for sharing the final set of code.