Access Form to email completion to email in field of record

[DocBlack4444]

Ok, I guess the I forgot about this part. Could you also help me with adding a PDF document of the Form displaying the record that we "finished" in the email?

I just need the knowledge on what I believe it the sendobject command.....

-Tony

[twinnyfo]

The SendObject Method has additional parameters: View this article that will help explain these. Just add these parameters to your expression:

Call DoCmd.SendObject(ObjectType:=acSendReport, _
                      ObjectName:="YourReportName", _
                      OutputFormat:=acFormatPDF, _
                      To:=strEMail, _
                      Subject:="This is a Test Email", _
                      MessageText:=strMessage, _
                      EditMessage:=True)

Over time many of these methods will become second nature and you will look for ways to become more elaborate. While these methods are a bit limited in what they can and can't do, they are a good starting place for sending reports or just emails to your customers.

Hope this hepps!

[DocBlack4444]

Twinnyfo,

Your knowledge worked!

here is the code that I put in:

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 OR [Work Comments] is Null"
      Me.FilterOn = True

End Sub

I also added my own Dim line up top to make the email have a signature line from the person completing the work.


It works beautifully, And no I can play with my new knowledge and share as well.

Thank you, Thank you, Thank you,

-Tony
P.S. - Twinntfo, I marked your first post as correct answer.

[NeoPa]

DocBlack:
Again,
Please know that I did not mean to come across as not willing to learn or not showing the respect that your knowledge and work obviously deserves. If I did do those thing, I apologize.

I've been tied up for a while so have just found this.

Please know that you weren't out of line, and furthermore seem to be progressing nicely since that post.

We are confident that we can deal with those that become more of a burden than they should. I don't believe you were ever in or near that category. From what Twinny's posted for you I would guess he's found you quite pleasant to deal with.

So, please relax and be comfortable asking for help here. We're happy to offer it.

[DocBlack4444]

The Team helped me out tremendously. When I replied that, I thought I had upset someone. I have since found out that the other Mods and fixers were helping me learn.

This has been a great project for me to get my feet wet on with simple code in VBA, honestly I did not even know that VBA existed before I started this project.

I have since dropped the PDF part of the email and simply brought over information from the view of the form.....I learned about the String elements and how to use the naming convention that Twinnyfo suggested, it was A LOT easier to identify fields that I wanted to target that way.

Everything is working great in this DB, with only one little thing that I think I want to ask for help on.

I looked it up for the last few days and found only how to make Bold the information that I place in the quotation marks. how to I make the String data bold when the VBA code is putting it into an Email?

here is a copy of the code that I am working with:

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
     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 & "The 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

[twinnyfo]

A bit more advanced, but if you want professional looking e-mails, I would recommend using VBA to create an e-mail message in Outlook (assuming you use that as your default e-mail application.

Oddly enough, now that you ask, there happens to be an article on Bytes that addresses sending e-mails via Outlook.

Kind of off topic from the main thread, but I think I'll allow it as a follow-on....

Hope this hepps!

[DocBlack4444]

Twinnyfo,

Great information you linked to.

The code that I posted is creating an outlook message and it is working great, my next idea would be to "BOLD" some of the string elements so they standout in the email that I send to the original requestor.

I tried two different things that I found. on line 22 I tried the following things:

strclinic = Me.txt_clinic_name.bold = True

and

strclinic = Me.txt_clinic_name.bold = True

and

strclinic = Me.txt_clinic_name.fontbold = True

and

strclinic = Me.txt_clinic_name.fontbold

All of these did not produce the desired effect. matter of fact, the first two caused an error and the second two produced the information as a "0" in the email.

if by any chance, there is a different way to do this, i am all ears and want to learn.

Please just point me in the right direction.
-Tony

[DocBlack4444]

sorry, on the second example, remove the " = True" argument and that is what I tried.

[twinnyfo]

Don't worry about the controls--they only hold text.

However, if you want to send with bold, you need to send the e-mail in HTML format (see the posted link).

Then, get familiar with HTML tags.

If you wanted to bold the word "now" in the sentece, "I need help now!", your text string for html would be:

"I need help <B>now</B>!"

which would come out looking like this:

I need help now!

[DocBlack4444]

and that is going to be the problem, our company sets the default outlook message as plain text, not HTML.....so if I did force it, then each of my users would have to click that they accept the switch from plain text to html.

ok, that makes sense, so that would be a no go.

Thank you for the education, another questions marked complete.

-Tony

[twinnyfo]

Our office forces plain text, too. But we can send in HTML and then they can convert back. It’s just the way of the world....