Access Form to email completion to email in field of record

[DocBlack4444]

Hello,

I am working a database that I want to have a very easy way for people notify that they have completed a record. let me better explain:

My database has one table and two forms. The first form is for people requesting a change, they put in the information and click save. The second form is for those that are completing the work and will make updates of their own and then will save as well.

I am rather new to Access and have done a few things in it but having a some troubles refining the job.

I have set up for the second form's save button to open an email with a PDF of the record but the problem I am having is that the To: field needs to have the email address from one of the fields in the record. I cant seem to figure out how to do that.

any help would be greatly appreciated. Thank you!

[twinnyfo]

If you use the DoCmd.SendObject Method, one of the parameters is the "To" field of the E-Mail. If you start typing that method into your VB Editor, it will prompt you for all the parameters.

You can also use Named arguments (e.g. To:="[e-mail]").

Hope this hepps!

[DocBlack4444]

I have no idea what the VB editor is.......I was changing the properties of the save button and found that the Macro builder on the save record line gave me the ability to send a copy of the object to an email. I was hoping that I could tell that command to use the email address that was in one of the fields of the same record that it was sending.

[twinnyfo]

First, please abandon Macros altogether. I can't list a single expert on this forum who wouldn't recommend the same thing.

Instead of using Macros, in the Event properties, select [Event Procedure], which will take you to the Visual Basic Editor.

This may be new to you, but you will find that you can do so much more with VB than with Macros. We are also willing to help walk you through this.

We've all been in your shoes. Just be willing to learn and seek to push yourself to greater skill in these areas. There are also plenty of VBA tutorials out there--unfortunately, this forum is not one such place to find them.

Standing by assist.

[DocBlack4444]

Thank you so very much. I am so thankful that I found this forum, you guys are all really awesome!!

OK, so Visual Basic Editor......here we go.

I found the button for visual Basic under the create tab. it opens something that looks like the code builder from the properties sheet. so now that I am here, let me tell you what I want to tell this thing to do for me. The second form gives the tech the information that needs to be completed. once they complete the request, they put their name and date completed in the form and click save. once they click save, I want the form to open an outlook new mail and address that mail to the original requestor at their email that they put into the first form that is currently stored in a field labeled "Provider Email".

as you pointed out, I tried to do this through the Macro builder without success. I will learn this, but I need a little help to get there. Code is not something that I ever learned to type, let alone figure out what it says. If someone could send me a link to a website that I can read up on this, or simply tell me what this code lines need to say, I would appreciate it very much.

twinnyfo - Thank you, and I do mean that. you are one of the few that can put this in lamens terms for my little pea brain to understand.

Thank you,
-Tony

[twinnyfo]

You will quickly learn on this site that if you are willing to learn, we are more willing to hepp.

Hold on a few so I can put some things together for you.

[DocBlack4444]

Thank you so very much. I am really looking forward to working through this database. I see the end result in my head and just don't know how to get to where I want.

I am always willing and wanting to learn, I want to know how to do something so I can repeat it in the future and also share the knowledge with others as I go.

Thank you again,
-Tony

[twinnyfo]

OK - here we go. Your first lesson in VBA.

I've created a generic sample form. Please pay attention to the various details I describe, as you will save yourself many headaches if you follow these guidelines.

If you take a look at the image above, you will see that it probably looks a lot like yours. It's just a simple form with several Controls on it. Forms have Controls, Tables have Fields. A Form may refer to a Table's Fields via a Control. The Controls you see here are Labels, which have a Caption to tell you what is in the Control that follows, and Text Boxes, which hold data that you can use. My Form is not linked to a Table, so there are no Fields underlying these Text Boxes (which is why they say "Unbound"). There is also a Command Button, which is a control that the user clicks to activate something in the DB.

The first thing you should learn is that you should ALWAYS spend the few extra minutes it takes to rename all of your Controls using a standard naming convention. Do a Google Search for Database Naming Conventions. Choose one and stick with it. I prefer simplicity. Labels begin with lbl, Text Boxes begin with txt, Command Buttons begin with cmd. OK - I'll just post my whole convention:

cbo    Combo Box
chk    Check Box
cmd    Command Button
db     DAO Database Object
dbl    Variable with Double floating point decimal Data Type
dt     Variable with Date/Time Data Type
f      Variable with Boolean Data Type
frm    Form
fsub   Sub-Forms (embedded in other Forms)
g      Global Variable
img    Image
int    Variable with Integer Data Type
lbl    Label
ln     Line
lng    Variable with Long Integer Data Type
mod    Visual Basic Module
obj    Various decalred database Objects
opt    Option Group
pg     Page on a Tab Control
qapp   Append Query
qdel   Delete Query
qry    Select Query
quni   Union Query
qupd   Update Query
qxtb   Crosstab Query
rct    Rectangle
rpt    Report
rst    DAO Recordset Object
rsub   Sub-Report (embedded in other Reports)
str    Variable with String Data Type
tabctl Tab Control
tbl    Table
tgl    Toggle Button
txt    Text Box
var    Variable with Variant Data Type

If you double-click the Command Button in Design View, you will bring up the Control's Properties.

In the Event Tab of the Properties, you can see that a particular event that happens to this Control can cause something else to happen. In the On Click Event, we are going to choose [Event Procedure] from the dropdown list and then click the three dots.

You should now see something like this (or very similar--I've changed my colors to suit my eyes). The first thing I want you to do is go to the Tools Menu, click Options... and then make sure that Require Variable Declaration is checked. This ensures that you must declare all the variables you use in your projects. Why this is not a MS default is beyond me. However, this will cause your Modules (one of which you just opened) to have two lines at the top:

Option Compare Database
Option Explicit

You always, always, always want these two lines at the top of your Modules.

Now, because you are probably new to this, I will explain what you see below:

You see several lines that begin with Dim, several other lines of what looks like a jumbled foreign language and then a Call to perform an action.

The Dim lines are declaring your variables (Sound familiar? We talked about it above). When you declare a variable you are assigning a name to that variable and telling the system what Data Type that variable contains. Notice the naming convention (see how things are coming together here?).

Then, now that we have the variables established, we assign Values to those variables. Look at the first line:

strName = Me.txtName

This may not make any sense to you, so I will explain. We want to assign the User's Name (that they have entered into the Form) to the variable strName (don't ever name a variable or a Control or Field in a Table "Name", as this is a reserved word in Access and the DB can get confused!). But, what is this "Me" stuff? Is it really all about Me? Well, Me. is a shorthand means to refer to the Object (the Form) that this module is assigned to. When you have the dot (".") it means you can refer to controls and values assigned to that Object. So, we are referring to the Text Box named txtName. So, whatever the user has entered into the Text Box named txtName, will be assigned to the Variable named strName. Make sense?

The next two lines follow this similar example. But, what about the next set of lines:

strMessage = "Hi, " & strName & "," & _
    vbCrLf & "This is a test email for " & _
    "the work you did on " & _
    Format(dtDate, "dd mmm yyyy") & "."

Well, if you think about it, what is strMessage? It is a variable of the String data type, which means we are going to add text to it. Let's break down the parts:

The first part is a simple greeting. We put it in quotes, because that signifies that it is text. Then we concatenate (using the "&") the user's name. But, why not just use "Hi, Tony!"? Because we want anyone to be able to add their name. And, since we have assigned the user's name to the variable strName, we include that in the formula by referring to it in the "equation".

That "_" character simply indicates that we are going to another line in the code, without a line break. Many coders set themselves a limit of lines no more than 80 characters wide. This makes things easier to read. For this forum, I keep it even more narrow, because of the limitations of the page.

Then, we have this weird "vbCrLf" thing. That is a global constant in Visual Basic and it represents a Carriage Return and Line Feed, indicating moving to a new line of text within a string. Then, we have some text, which is self-explanatory.

Then we add the date. But dates can be tricky in VBA. We want to make sure it looks like a date, and we can even change the format to suit our tastes or customers. This one will come out like this: "28 Sep 2018".

The last line is the solution to the question you first posed:

Call DoCmd.SendObject(To:=strEMail, _
                      Subject:="This is a Test Email", _
                      MessageText:=strMessage, _
                      EditMessage:=True)

We are calling for the SendObject method. You will notice several terms that end with ":=". These are named parameters for this method. As you can see, you can use variables for these parameters or hard code those parameters in directly.

Your final code will look like this:

Option Compare Database
Option Explicit
 
Private Sub cmdSave_Click()
    Dim strName     As String
    Dim dtDate      As Date
    Dim strEMail    As String
    Dim strMessage  As String
 
    strName = Me.txtName
    dtDate = Me.txtDate
    strEMail = Me.txtEMail
    strMessage = "Hi, " & strName & "," & _
        vbCrLf & "This is a test email for " & _
        "the work you did on " & _
        Format(dtDate, "dd mmm yyyy") & "."
 
    Call DoCmd.SendObject(To:=strEMail, _
                          Subject:="This is a Test Email", _
                          MessageText:=strMessage, _
                          EditMessage:=True)
 
End Sub

One thing you will have to keep in mind is that you will have to make sure that the user enters a date in the txtDate text box. Just change its format to date and make sure that the Date Picker option is set for dates.

There you have it. Your first walk into the world of VBA!

Hope this hepps!

[DocBlack4444]

This is great. Thank you. I am working on getting this updated, per your suggestions and I ran into a snag.... I already have a few lines of code in the second form that automatically filters incomplete requests first for display.

Private Sub Form_Open(Cancel As Integer)
 
     Me.Filter = "[Work Completed By] is Null AND [Date Completed] is Null AND [Work Comments] is Null"
     Me.FilterOn = True
 
End Sub

SO I am not sure where to start putting in the code that you worked for me into this....does it go with it, before it or after it.

I tried after it and the program had a heart attack, and I don't know how to read the issue that it had so I removed the code and figured that I would ask.

[twinnyfo]

Please re-read my post as to where the code would go. Your code refers to the Form’s On Open Event, You need to add this to your command button’s On Click event.

[NeoPa]

Hi Doc.

We appreciate that Twinny's post is very full of information, and this can be hard to deal with in one go.

What we ask is that you retrace your way back to that and do the best you can from there. There is much in there that you don't want to overlook. Also a great deal of effort has gone into making that post for you in such a way that you can understand it. Please persevere with it to the extent that you can.

If, after a revisit, you still need this to be expressed in more simple language then please let us know. In general we're happy to do what it takes to help you to step up. We would want to be clear that you've made a solid effort first, and aren't using our efforts simply as a crutch, but if you can convince us that you're making the effort you need to, and that you'll certainly benefit from, and that after that work you still need extra help, then we'll give that help.

PS. Asking is not a problem. Our response is based on our experienced understanding of the best approach for you at this point in your progress.

[DocBlack4444]

Twinnyfo & NeoPa,

Please know that I meant no disrespect when asking the follow up question. I overwhelmingly appreciate what Twinnyfo put together for me, by far, it was so much more than the basics of a few lines of code.

As you said, it is A LOT of information. I spend the weekend away from this project so I could get a fresh mind when I came back to it this morning. My plan was to reread all of the information that was put up to see if I could figure this out. I will be playing with this today, and if I still run into snags, I will reach out tomorrow morning.

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.

-Tony

[DocBlack4444]

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
 
    strName = Me.txt_prov_name
    dtDate = Me.txt_completion_date
    strEmail = Me.txt_prov_email
    strMessage = "Hello,"
       vbCrLf & "Your request has been completed.  Please review the changes made to your clinic and reply to this Email if more changes are needed." & _
 
    Call DoCmd.SendObject(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 AND [Date Completed] is Null AND [Work Comments] is Null"
     Me.FilterOn = True
 
 End Sub
 
 

Alright.....After rereading and changing some names, as you suggested. (I chose a naming convention with an underscore after the Naming followed by the shorthand of the item.)

I typed my first real lines of code.... ay first I was not understanding what I was typing but it started to make sense, ran into some errors and was able to fix them with your knowledge.

once I hit save and went into use the new code, I received a Syntax Error for line 14......you didn't cover that, so I looked it up. And I am not getting this one. Any thoughts?

[twinnyfo]

Tony,

You may hate me for this, but I'm not going to give the answer straightaway. Part of growing as a coder is being able to troubleshoot your code. Sometimes, the errors are right in your face and you can't see them (the other experts on this forum can give you plenty of examples in which this has happened to me).

Sometimes, you are just not familiar enough (such as your stage) with coding, that mistakes aren't going to pop out at you.

So......... Look very closely at your lines 13-14. Then look even more closely at my Post #8, particularly with regard to my discussion of strMessage. It's about 1/3 of the way up from the end of that post. I discuss the line continuation character "_" and show you how they are used. First, see if you can analyze similarities and differences between how I've used them and how you have used them. Then see if you can come up with a solution.

This method of learning is called, "Don't give them a fish, teach them how to fish!"

Let me know what you find.

[DocBlack4444]

Twinnyfo,

Thank you for not giving me the answer. I agree that I need to learn this in order to use and share.

So, I found that in line 13 I forgot to type the strName & "," & _ after the Hello. I added that and now an getting the Syntax Error on 13 and 14. prior, I was only getting the error on 14.

Currently, this is what I looks like:

     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." & _
 

I went back over your tutorial and noticed that I needed to remove the & _ after line 14 as there was not going to be a continuation of this message text.

I ran a test and it worked PERFECTLY.

Thank you for your help and your wisdom.

[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....