423,485 Members | 1,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,485 IT Pros & Developers. It's quick & easy.

Send Object PDF to send current record only

P: 26
Good Afternoon,

I have searched for a few days and found older fixes to this problem but nothing that is current to my version of Access (365) or current to the type of code that i am working with.

I have used this site for a few questions with this database, and have had amazing results. i am learning to find my own answers and figuring out how to do this while i have a smarter than me person guiding me through.

I am working with a single table and two form database.
the first form fills out a request and submits to request through clicking the save button.

the second form works the request and puts in their own information followed by sending a confirmation email to the original requester.

here is a copy of the Sendobject code that i have for my second form:

Option Compare Database
Option Explicit

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_save_Click()
  2.      Dim strName      As String
  3.      Dim dtDate       As Date
  4.      Dim strEmail     As String
  5.      Dim strMessage   As String
  6.      Dim strmsa       As String
  7.  
  8.      strName = Me.txt_prov_name
  9.      dtDate = Me.txt_completion_date
  10.      strEmail = Me.txt_prov_email
  11.      strmsa = Me.txt_who
  12.      strMessage = "Hello " & strName & "," & _
  13.          vbCrLf & "Your request has been completed.  Please review the changes made to your clinic and reply to this email if more changes are needed." & _
  14.          "There is no need to reply if the changes are correct." & "    " & "-" & strmsa
  15.  
  16.      Call DoCmd.SendObject(ObjectType:=acSendForm, _
  17.                            ObjectName:="Request - MSA", _
  18.                            OutputFormat:=acFormatPDF, _
  19.                            To:=strEmail, _
  20.                            Subject:="Clinic Change Request", _
  21.                            MessageText:=strMessage, _
  22.                            EditMessage:=True)
  23.  
  24. End Sub
  25.  
  26. Private Sub Form_Open(Cancel As Integer)
  27.  
  28.       Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
  29.       Me.FilterOn = True
  30.  
  31. End Sub
  32.  
  33. Private Sub Form_Timer()
  34.  
  35. MsgBox ("Refreshing Now, Press OK to continue")
  36.  
  37. End Sub
As you can see i have a few parts to this, the first part is that the form opens to only those requests that are not completed yet. Another part refreshes the query when i leave the form open to display a new request.

the largest part uses the record information to send an email with a snapshot of the completed form to the original requester's email in a PDF format.

this worked just fine, until i had two requests to complete.

both were filtered to view in the second form and when i hit save, the PDF displayed both of them in the email.

what i need is for the PDF to only send the single record that i am working on.

i found ACformatSNP instead of PDF, but that did not work at all and broke the form when i hit save......

I read that i need to create a report based on the specific record that i am working on and then set that as the send object.

I know next to nothing about Access, except what others on this forum have helped me learn.....so Green, New and multiple other things that i am, i need just a little bit more help on this before i go live.

Please do NOT do this for me, please help point me in the right direction, and guide my hands to type the correct line of code, so I can learn.

Right now I am understanding that I need to create a report that shows the current record and i need to export that to PDF......I don't even know the first thing about report.

Guiding Hands please teach me what i need to know.
1 Week Ago #1
Share this Question
Share on Google+
2 Replies


P: 26
So after think about this for a while, i decided that i was going about this all wrong.

I decided to change the object that i was sending to just an email and import the data from the current record that i was viewing.

This is what i ended up changing the code to. I don't know if this even qualifies to leave this thread open or close and delete it, but this solution works great for me!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_save_Click()
  2.      Dim strName      As String
  3.      Dim dtDate       As Date
  4.      Dim strEmail     As String
  5.      Dim strMessage   As String
  6.      Dim strmsa       As String
  7.      Dim strclinic    As String
  8.      Dim strwhat      As String
  9.      Dim strcx        As String
  10.      Dim strcomments  As String
  11.      Dim strcompletion As String
  12.      Dim strdate      As String
  13.  
  14.  
  15.      strName = Me.txt_prov_name
  16.      dtDate = Me.txt_completion_date
  17.      strEmail = Me.txt_prov_email
  18.      strmsa = Me.txt_who
  19.      strclinic = Me.txt_clinic_name
  20.      strwhat = Me.txt_what
  21.      strcx = Me.txt__CX_MO_RE
  22.      strcomments = Me.txt_comments
  23.      strcompletion = Me.txt_completion_date
  24.      strdate = Me.txt_date
  25.      strMessage = "Hello " & strName & "," & _
  26.          vbCrLf & _
  27.          vbCrLf & "Your change request you made on " & strdate & " has been completed.  The details of the requested change are below." & _
  28.          vbCrLf & _
  29.          vbCrLf & _
  30.          vbCrLf & _
  31.          vbCrLf & _
  32.          vbCrLf & "The Clinic you requested to change was: " & strclinic & _
  33.          vbCrLf & _
  34.          vbCrLf & "This was a request to preform a " & strcx & " action" & _
  35.          vbCrLf & _
  36.          vbCrLf & "The requested change was made on: " & strcompletion & _
  37.          vbCrLf & _
  38.          vbCrLf & "The changes that were requested are: " & strwhat & _
  39.          vbCrLf & _
  40.          vbCrLf & "Adjusters Comments: " & strcomments & _
  41.          vbCrLf & _
  42.          vbCrLf & _
  43.          vbCrLf & _
  44.          vbCrLf & "Please review the changes made to your clinic and reply to this email if more changes are needed." & _
  45.          vbCrLf & _
  46.          vbCrLf & "There is no need to reply if the changes are correct." & _
  47.          vbCrLf & _
  48.          vbCrLf & "    " & "-" & strmsa
  49.  
  50.      Call DoCmd.SendObject(ObjectType:=acSendNoObject, _
  51.                            To:=strEmail, _
  52.                            Subject:="Clinic Change Request", _
  53.                            MessageText:=strMessage, _
  54.                            EditMessage:=True)
  55.  
  56. End Sub
  57.  
  58. Private Sub Form_AfterUpdate()
  59.  
  60. End Sub
  61.  
  62. Private Sub Form_Open(Cancel As Integer)
  63.  
  64.       Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
  65.       Me.FilterOn = True
  66.  
  67. End Sub
  68.  
  69. Private Sub Form_Timer()
  70.  
  71. MsgBox ("Refreshing Now, Press OK to continue")
  72.  
  73. End Sub
  74.  
1 Week Ago #2

zmbd
Expert Mod 5K+
P: 5,283
DockBlack4444:
Your original post pop the older thread back into my message que... I've added an update there that may be of value to you
home > topics > microsoft access / vba > questions > send single record to email >Post#9
I've used the query as the object in the example; however, a report based on the query works just as well. As I noted in the update to the thread, I've been using this method for quite awhile now using acSendReport and acFromatPDF for the DoCmd.SendObject to send out reports to certain individuals.

Best of luck with your project
3 Days Ago #3

Post your reply

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