Send single record to email

[eltigreferoz]

Hello. I've been trying to create a button on my ACCESS 2007 form that will send a pdf via email of a single record. At the moment (and using code from a previous forum) I have been successful in getting the function to work; however, at the moment, the report sent contain each and every record from the form. How can I limit it to just one record? I believe the path is through my record ID key (unique to every record), but don't know how to write that into code. Can you help? Thank you in advance.

Here's the code so far:

Private Sub xEmail_Click()
On Error GoTo Err_xEmail_Click
 
    Dim stDocName As String
    Dim stEmail As String
    Dim stSubject As String
 
    stDocName = "New Production Log"
    stEmail = "[e-mail]"
    stSubject = "Production Log " & Me![Name] & ", " & Me![Date]
 
    DoCmd.SendObject acSendForm, stDocName, acFormatPDF, stEmail, , , stSubject, , , False
 
Exit_xEmail_Click:
    Exit Sub
 
Err_xEmail_Click:
    MsgBox Err.Description
    Resume Exit_xEmail_Click
 
End Sub
 

[zmbd]

Line 12, change the query refered to by "ObjectName": http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[eltigreferoz]

Thanks, zmbd. You mean to change the sendobject to, say, a query or report where I have the criteria I need?

However, is there a way to update the current code to have criteria in place that it would only send a form of the current record, as identified by the individual record ID?

[zmbd]

the send object is just that... you're sending the entire object; thus, in a query, set the conditions to return the record(s) of interest.

I've not used a parameter based query with this method... might work... I usually have a generic query that I delete and re-create via vba. I have a thread here with an example along those lines... should be an easy search...

[eltigreferoz]

Well, here's what I've tried. Yet I can't get it to recognize the specific ID. Should I be using sendobject at all? It's the only function that appears to work with this specific code.

I'm very much a newbie, so pardon my ignorance if everything I've done makes no sense.

Private Sub xEmail_Click()
On Error GoTo Err_xEmail_Click
 
    Dim stDocName As String
    Dim strDocName As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
 
    If Me.Dirty Then Me.Dirty = False
 
    stDocName = "New Production Log"
    strWhere = "[ID]=" & Me!ID
    stEmail = "[e-mail]"
    stSubject = "Production Log " & Me![Specialist Name] & ", " & Me![Reporting Week]
 
    DoCmd.SendObject acSendForm, stDocName, acFormatPDF, stEmail, , , stSubject, , , False
 
Exit_xEmail_Click:
    Exit Sub
 
Err_xEmail_Click:
    MsgBox Err.Description
    Resume Exit_xEmail_Click

[zmbd]

OK,
I think you're going to have to use the workaround method similuar to what I've done with the transferworksheet method. Alot of the send-this and transfer-that methods in access require a "final" resolved object.

What I do in the workaround is check for the exsistance of my generic query, delete it if it exsists, and then re-create the query. I then use this as the object.

You do this using the querydef collection....
I just posted code to do something like this within the last few weeks I'll post the the thead link here in a little while (have to find it :) )if you don't find it thru a search first.

Busy day.

[eltigreferoz]

That would be fantastic. I did look around for the "querydef" but was unable to find the exact post you were referring to. I see now that there is no way to limit criteria on the SendObject, so your idea of creating a query with the current record ID-specific parameter sounds like a good bet. Thanks.

[zmbd]

Well, that is very annoying... I can't seem to find it either and I posted it around the 9/17ish... who knows.


Here's are the basic concepts... (I haven't debugged this and it's from memory):

Dim zdb As Database
Dim zSQL As String
Dim zQueryName As String
Dim zqryDef As QueryDef
Dim z_txtboxval As String
'
'Set initial error trap
On Error GoTo Z_Errtrap
'
'set variable values
Set zdb = CurrentDb
zQueryName = "qry_CHANGETHISNAME"
'Delete old query first - we want fresh data!
'(if the query doesn't exsist, this will toss error: 3265
dbs.QueryDefs.Delete strQueryName
'
'Build the SQL.
zSQL = "SELECT field1, field2, field3 " _
        & "FROM TABLEorQRYofINTEREST" _
        & "WHERE (((Field3)='" & z_txtboxval & "'));"
'
'Create query definition
Set zqryDef = dbs.CreateQueryDef(zQueryName, zSQL)
'
'More VBA Here
'...
' *** End of Code ***
'>>>
'
Exit_Click:
Set zdb = Nothing
Exit Sub
'
Z_Errtrap:
If err.number = 3265 then 
   resume next
else
   Msgbox Err.Number & " - " & Err.Description
   Resume Exit_Click
end if
End Sub

[zmbd]

!Updated!
At the time of this thread the TempVars Object (Access) collection had just been added to Access2007; however, not much was known about it at the time; however, this thread has popped back up in my message que; thus, an update.

Create your stored query such that it uses the Tempvars collection for the Where Clause:

SELECT field1, field2, field3
FROM TABLEorQRYofINTEREST
WHERE (((Field3)= tempvars![ValueFromForm] ));

Then the VBA code in Post #8 would change to

Dim zdb As Database
 Dim z_txtboxval As String
 '
 'Set initial error trap
 On Error GoTo Z_Errtrap
 '
 'tempvars will not take an object so a workaround
 ztextboxval = Me!ID
 '
 'set tempvar values
 if (tempvars![ValueFromForm] & "") = "" Then
   tempvars.Add "ValueFromForm",ztextboxval
 else
   tempvars![ValueFromForm] = ztextboxval
 end if
 '
 'you can then do something like this:
    DoCmd.SendObject _
      objecttype:=acSendQuery, _
      objectname:="QryForEmail_Example", _
      OutputFormat:=acFormatPDF, _
      To:="z@z.z", _
      Subject:="Requested Query Results", _
      MesageText:="The results from your latest data request are attached"
 
 '...
 ' *** End of Code ***
 '>>>
 '
 Exit_Click:
 
 Exit Sub
 '
 Z_Errtrap:
 If err.number = 2501 then 
    'the email sent was canceled by the user
    resume next
 else
    Msgbox Err.Number & " - " & Err.Description
    Resume Exit_Click
 end if
 End Sub

I've been using a variation on this for quite awhile now for simple report emails and it saves on the database bloating that would happen with the previous workaround.