Send Object PDF to send current record only

[DocBlack4444]

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

Private Sub cmd_save_Click()
     Dim strName      As String
     Dim dtDate       As Date
     Dim strEmail     As String
     Dim strMessage   As String
     Dim strmsa       As String
 
     strName = Me.txt_prov_name
     dtDate = Me.txt_completion_date
     strEmail = Me.txt_prov_email
     strmsa = Me.txt_who
     strMessage = "Hello " & strName & "," & _
         vbCrLf & "Your request has been completed.  Please review the changes made to your clinic and reply to this email if more changes are needed." & _
         "There is no need to reply if the changes are correct." & "    " & "-" & strmsa
 
     Call DoCmd.SendObject(ObjectType:=acSendForm, _
                           ObjectName:="Request - MSA", _
                           OutputFormat:=acFormatPDF, _
                           To:=strEmail, _
                           Subject:="Clinic Change Request", _
                           MessageText:=strMessage, _
                           EditMessage:=True)
 
End Sub
 
Private Sub Form_Open(Cancel As Integer)
 
      Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
      Me.FilterOn = True
 
End Sub
 
Private Sub Form_Timer()
 
MsgBox ("Refreshing Now, Press OK to continue")
 
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.

[DocBlack4444]

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!!

Private Sub cmd_save_Click()
     Dim strName      As String
     Dim dtDate       As Date
     Dim strEmail     As String
     Dim strMessage   As String
     Dim strmsa       As String
     Dim strclinic    As String
     Dim strwhat      As String
     Dim strcx        As String
     Dim strcomments  As String
     Dim strcompletion As String
     Dim strdate      As String
 
 
     strName = Me.txt_prov_name
     dtDate = Me.txt_completion_date
     strEmail = Me.txt_prov_email
     strmsa = Me.txt_who
     strclinic = Me.txt_clinic_name
     strwhat = Me.txt_what
     strcx = Me.txt__CX_MO_RE
     strcomments = Me.txt_comments
     strcompletion = Me.txt_completion_date
     strdate = Me.txt_date
     strMessage = "Hello " & strName & "," & _
         vbCrLf & _
         vbCrLf & "Your change request you made on " & strdate & " has been completed.  The details of the requested change are below." & _
         vbCrLf & _
         vbCrLf & _
         vbCrLf & _
         vbCrLf & _
         vbCrLf & "The Clinic you requested to change was: " & strclinic & _
         vbCrLf & _
         vbCrLf & "This was a request to preform a " & strcx & " action" & _
         vbCrLf & _
         vbCrLf & "The requested change was made on: " & strcompletion & _
         vbCrLf & _
         vbCrLf & "The changes that were requested are: " & strwhat & _
         vbCrLf & _
         vbCrLf & "Adjusters Comments: " & strcomments & _
         vbCrLf & _
         vbCrLf & _
         vbCrLf & _
         vbCrLf & "Please review the changes made to your clinic and reply to this email if more changes are needed." & _
         vbCrLf & _
         vbCrLf & "There is no need to reply if the changes are correct." & _
         vbCrLf & _
         vbCrLf & "    " & "-" & strmsa
 
     Call DoCmd.SendObject(ObjectType:=acSendNoObject, _
                           To:=strEmail, _
                           Subject:="Clinic Change Request", _
                           MessageText:=strMessage, _
                           EditMessage:=True)
 
End Sub
 
Private Sub Form_AfterUpdate()
 
End Sub
 
Private Sub Form_Open(Cancel As Integer)
 
      Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null"
      Me.FilterOn = True
 
End Sub
 
Private Sub Form_Timer()
 
MsgBox ("Refreshing Now, Press OK to continue")
 
End Sub
 

[zmbd]

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

[DocBlack4444]

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?

[twinnyfo]

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:

DoCmd.Close

Simple enough!

Hope this hepps!