By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Send single record to email

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub xEmail_Click()
  2. On Error GoTo Err_xEmail_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stEmail As String
  6.     Dim stSubject As String
  7.  
  8.     stDocName = "New Production Log"
  9.     stEmail = "email@email.com"
  10.     stSubject = "Production Log " & Me![Name] & ", " & Me![Date]
  11.  
  12.     DoCmd.SendObject acSendForm, stDocName, acFormatPDF, stEmail, , , stSubject, , , False
  13.  
  14. Exit_xEmail_Click:
  15.     Exit Sub
  16.  
  17. Err_xEmail_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_xEmail_Click
  20.  
  21. End Sub
  22.  
Sep 27 '12 #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,397
Line 12, change the query refered to by "ObjectName": http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
Sep 27 '12 #2

P: 4
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?
Sep 28 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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...
Sep 28 '12 #4

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub xEmail_Click()
  2. On Error GoTo Err_xEmail_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim strDocName As String
  6.     Dim stWhere As String
  7.     Dim stEmail As String
  8.     Dim stSubject As String
  9.  
  10.     If Me.Dirty Then Me.Dirty = False
  11.  
  12.     stDocName = "New Production Log"
  13.     strWhere = "[ID]=" & Me!ID
  14.     stEmail = "email@email.com"
  15.     stSubject = "Production Log " & Me![Specialist Name] & ", " & Me![Reporting Week]
  16.  
  17.     DoCmd.SendObject acSendForm, stDocName, acFormatPDF, stEmail, , , stSubject, , , False
  18.  
  19. Exit_xEmail_Click:
  20.     Exit Sub
  21.  
  22. Err_xEmail_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_xEmail_Click
Sep 28 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
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.
Sep 28 '12 #6

P: 4
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.
Oct 1 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
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):

Expand|Select|Wrap|Line Numbers
  1. Dim zdb As Database
  2. Dim zSQL As String
  3. Dim zQueryName As String
  4. Dim zqryDef As QueryDef
  5. Dim z_txtboxval As String
  6. '
  7. 'Set initial error trap
  8. On Error GoTo Z_Errtrap
  9. '
  10. 'set variable values
  11. Set zdb = CurrentDb
  12. zQueryName = "qry_CHANGETHISNAME"
  13. 'Delete old query first - we want fresh data!
  14. '(if the query doesn't exsist, this will toss error: 3265
  15. dbs.QueryDefs.Delete strQueryName
  16. '
  17. 'Build the SQL.
  18. zSQL = "SELECT field1, field2, field3 " _
  19.         & "FROM TABLEorQRYofINTEREST" _
  20.         & "WHERE (((Field3)='" & z_txtboxval & "'));"
  21. '
  22. 'Create query definition
  23. Set zqryDef = dbs.CreateQueryDef(zQueryName, zSQL)
  24. '
  25. 'More VBA Here
  26. '...
  27. ' *** End of Code ***
  28. '>>>
  29. '
  30. Exit_Click:
  31. Set zdb = Nothing
  32. Exit Sub
  33. '
  34. Z_Errtrap:
  35. If err.number = 3265 then 
  36.    resume next
  37. else
  38.    Msgbox Err.Number & " - " & Err.Description
  39.    Resume Exit_Click
  40. end if
  41. End Sub
Oct 2 '12 #8

zmbd
Expert Mod 5K+
P: 5,397
!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:
Expand|Select|Wrap|Line Numbers
  1. SELECT field1, field2, field3
  2. FROM TABLEorQRYofINTEREST
  3. WHERE (((Field3)= tempvars![ValueFromForm] ));
Then the VBA code in Post #8 would change to
Expand|Select|Wrap|Line Numbers
  1. Dim zdb As Database
  2.  Dim z_txtboxval As String
  3.  '
  4.  'Set initial error trap
  5.  On Error GoTo Z_Errtrap
  6.  '
  7.  'tempvars will not take an object so a workaround
  8.  ztextboxval = Me!ID
  9.  '
  10.  'set tempvar values
  11.  if (tempvars![ValueFromForm] & "") = "" Then
  12.    tempvars.Add "ValueFromForm",ztextboxval
  13.  else
  14.    tempvars![ValueFromForm] = ztextboxval
  15.  end if
  16.  '
  17.  'you can then do something like this:
  18.     DoCmd.SendObject _
  19.       objecttype:=acSendQuery, _
  20.       objectname:="QryForEmail_Example", _
  21.       OutputFormat:=acFormatPDF, _
  22.       To:="z@z.z", _
  23.       Subject:="Requested Query Results", _
  24.       MesageText:="The results from your latest data request are attached"
  25.  
  26.  '...
  27.  ' *** End of Code ***
  28.  '>>>
  29.  '
  30.  Exit_Click:
  31.  
  32.  Exit Sub
  33.  '
  34.  Z_Errtrap:
  35.  If err.number = 2501 then 
  36.     'the email sent was canceled by the user
  37.     resume next
  38.  else
  39.     Msgbox Err.Number & " - " & Err.Description
  40.     Resume Exit_Click
  41.  end if
  42.  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.
Oct 13 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.