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

Send Object PDF to send current record only

P: 27
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
  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
  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)
  24. End Sub
  26. Private Sub Form_Open(Cancel As Integer)
  28.       Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
  29.       Me.FilterOn = True
  31. End Sub
  33. Private Sub Form_Timer()
  35. MsgBox ("Refreshing Now, Press OK to continue")
  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.
Oct 3 '18 #1
Share this Question
Share on Google+
4 Replies

P: 27
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
  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
  50.      Call DoCmd.SendObject(ObjectType:=acSendNoObject, _
  51.                            To:=strEmail, _
  52.                            Subject:="Clinic Change Request", _
  53.                            MessageText:=strMessage, _
  54.                            EditMessage:=True)
  56. End Sub
  58. Private Sub Form_AfterUpdate()
  60. End Sub
  62. Private Sub Form_Open(Cancel As Integer)
  64.       Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
  65.       Me.FilterOn = True
  67. End Sub
  69. Private Sub Form_Timer()
  71. MsgBox ("Refreshing Now, Press OK to continue")
  73. End Sub
Oct 3 '18 #2

Expert Mod 5K+
P: 5,285
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
Oct 13 '18 #3

P: 27
Looking for a small bit of guidance. everything is working just fine with the VBA codes that I learned. the only thing that I need help with is for the VBA code to close the form once the email finishes sending. any ideas?
Oct 31 '18 #4

Expert Mod 2.5K+
P: 2,878
Technically should be a new question, but such a simple one, I'll just throw it out here. Right after the e-mail sends, add this line:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close
Simple enough!

Hope this hepps!
Oct 31 '18 #5

Post your reply

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