Send email report of current data in a form

[dhersch]

Hello All,
I am new to this forum and have only created one Access Database before. I am definitely still learning and most of my problems come from the fact I don't know how to program in Vbasic very well. Anyway. . . the task at hand is to have a button on one of my data entry forms that when clicked will run a query (already created) and output to a report (already created) in an email. I am using the sendobject function in Access. My problem is that every time I click this button it includes every record in the report. I only want to report to generate the current record being viewed in the data entry form. I have tried getting help on this topic, however it seems as though most of the replies are too complicated. I am willing to work hard and learn, however I am still having trouble. Is there some basic way to do this only with

 

[PHV]

Have you tried to use a parameterized query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[onemiland]

I use the code below to send Form data in an e-mail. What this will do is transfer the current data and place it in the message body depending on what fields you have.

Make sure you have the Outlook reference


Private Sub M1_Click()
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


strEmail = ""

Home = Nz(Home_Phone, "Not On File")
Work = Nz(Work_Phone, "Not On File")
Cell = Nz(Cell_Phone, "Not On File")


strBody = strBody & "SM Name: " & Last_Name & " " & First_Name & " " & sMidInit & " " & sRank & Chr(13)
strBody = strBody & Left$(Social, 3) & "-" & Mid$(Social, 4) & " " & SMUnit & Chr(13) & Chr(13)
strBody = strBody & "Home Address" & Chr(13)
strBody = strBody & Address & Chr(13) & City & ", " & State & " " & Zip_Code & Chr(13)



strBody = strBody & "(H) " & Left$(Home, 3) & "-" & Mid$(Home, 4, 3) & "-" & Right$(Home, 4) & Chr(13)
strBody = strBody & "(W) " & Left$(Work, 3) & "-" & Mid$(Work, 4, 3) & "-" & Right$(Work, 4) & Chr(13)
strBody = strBody & "(C) " & Left$(Cell, 3) & "-" & Mid$(Cell, 4, 3) & "-" & Right$(Cell, 4) & Chr(13) & Chr(13)

strBody = strBody & "Work Address" & Chr(13)
strBody = strBody & WAddress & Chr(13) & WCity & ", " & WState & " " & WZip & Chr(13) & Chr(13)
strBody = strBody & AKO_eMail & Chr(13) & Chr(13)
strBody = strBody & Remarks



With objEmail
.To = strEmail
.Subject = "Soldier Data"
.Body = strBody
.Display
End With

Exit Sub

End Sub

 

[Dirtbike]

Try this.

Me.Refresh
DoCmd.OpenReport "Customer", , , "Key = " & Me.Key

Works every time!!

 

[dhersch]

I have completed this very successfully, thank you for all your help. I have now encountered another issue regarding the "to" command. How do I choose to send the email to a person identified in one of my tables? Can I send it to multiple people or one person depending on an input? Can someone give me an idea where to start? I would assume this might lie in VB of which I know only a little. I cannot find this type of ability out of access. All I have seen is filling out the "to" command using the SendObject as a text item and not a variable or variables. Please let me know if you can help. Thanks again for working with me. I have learned so much so far!