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

Access Form to email completion to email in field of record

P: 27
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!
Sep 27 '18 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. cbo    Combo Box
  2. chk    Check Box
  3. cmd    Command Button
  4. db     DAO Database Object
  5. dbl    Variable with Double floating point decimal Data Type
  6. dt     Variable with Date/Time Data Type
  7. f      Variable with Boolean Data Type
  8. frm    Form
  9. fsub   Sub-Forms (embedded in other Forms)
  10. g      Global Variable
  11. img    Image
  12. int    Variable with Integer Data Type
  13. lbl    Label
  14. ln     Line
  15. lng    Variable with Long Integer Data Type
  16. mod    Visual Basic Module
  17. obj    Various decalred database Objects
  18. opt    Option Group
  19. pg     Page on a Tab Control
  20. qapp   Append Query
  21. qdel   Delete Query
  22. qry    Select Query
  23. quni   Union Query
  24. qupd   Update Query
  25. qxtb   Crosstab Query
  26. rct    Rectangle
  27. rpt    Report
  28. rst    DAO Recordset Object
  29. rsub   Sub-Report (embedded in other Reports)
  30. str    Variable with String Data Type
  31. tabctl Tab Control
  32. tbl    Table
  33. tgl    Toggle Button
  34. txt    Text Box
  35. 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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. strMessage = "Hi, " & strName & "," & _
  2.     vbCrLf & "This is a test email for " & _
  3.     "the work you did on " & _
  4.     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:

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SendObject(To:=strEMail, _
  2.                       Subject:="This is a Test Email", _
  3.                       MessageText:=strMessage, _
  4.                       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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSave_Click()
  5.     Dim strName     As String
  6.     Dim dtDate      As Date
  7.     Dim strEMail    As String
  8.     Dim strMessage  As String
  9.  
  10.     strName = Me.txtName
  11.     dtDate = Me.txtDate
  12.     strEMail = Me.txtEMail
  13.     strMessage = "Hi, " & strName & "," & _
  14.         vbCrLf & "This is a test email for " & _
  15.         "the work you did on " & _
  16.         Format(dtDate, "dd mmm yyyy") & "."
  17.  
  18.     Call DoCmd.SendObject(To:=strEMail, _
  19.                           Subject:="This is a Test Email", _
  20.                           MessageText:=strMessage, _
  21.                           EditMessage:=True)
  22.  
  23. 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!

Share this Question
Share on Google+
25 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,705
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:="abc@mailserver.com").

Hope this hepps!
Sep 27 '18 #2

P: 27
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.
Sep 28 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,705
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.
Sep 28 '18 #4

P: 27
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
Sep 28 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,705
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.
Sep 28 '18 #6

P: 27
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
Sep 28 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,705
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:

Expand|Select|Wrap|Line Numbers
  1. cbo    Combo Box
  2. chk    Check Box
  3. cmd    Command Button
  4. db     DAO Database Object
  5. dbl    Variable with Double floating point decimal Data Type
  6. dt     Variable with Date/Time Data Type
  7. f      Variable with Boolean Data Type
  8. frm    Form
  9. fsub   Sub-Forms (embedded in other Forms)
  10. g      Global Variable
  11. img    Image
  12. int    Variable with Integer Data Type
  13. lbl    Label
  14. ln     Line
  15. lng    Variable with Long Integer Data Type
  16. mod    Visual Basic Module
  17. obj    Various decalred database Objects
  18. opt    Option Group
  19. pg     Page on a Tab Control
  20. qapp   Append Query
  21. qdel   Delete Query
  22. qry    Select Query
  23. quni   Union Query
  24. qupd   Update Query
  25. qxtb   Crosstab Query
  26. rct    Rectangle
  27. rpt    Report
  28. rst    DAO Recordset Object
  29. rsub   Sub-Report (embedded in other Reports)
  30. str    Variable with String Data Type
  31. tabctl Tab Control
  32. tbl    Table
  33. tgl    Toggle Button
  34. txt    Text Box
  35. 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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. strMessage = "Hi, " & strName & "," & _
  2.     vbCrLf & "This is a test email for " & _
  3.     "the work you did on " & _
  4.     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:

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SendObject(To:=strEMail, _
  2.                       Subject:="This is a Test Email", _
  3.                       MessageText:=strMessage, _
  4.                       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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSave_Click()
  5.     Dim strName     As String
  6.     Dim dtDate      As Date
  7.     Dim strEMail    As String
  8.     Dim strMessage  As String
  9.  
  10.     strName = Me.txtName
  11.     dtDate = Me.txtDate
  12.     strEMail = Me.txtEMail
  13.     strMessage = "Hi, " & strName & "," & _
  14.         vbCrLf & "This is a test email for " & _
  15.         "the work you did on " & _
  16.         Format(dtDate, "dd mmm yyyy") & "."
  17.  
  18.     Call DoCmd.SendObject(To:=strEMail, _
  19.                           Subject:="This is a Test Email", _
  20.                           MessageText:=strMessage, _
  21.                           EditMessage:=True)
  22.  
  23. 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!
Attached Images
File Type: png Form1.png (8.5 KB, 265 views)
File Type: png Form2.png (7.9 KB, 263 views)
File Type: png Form3.png (15.5 KB, 265 views)
File Type: png Form4.png (25.5 KB, 268 views)
Sep 28 '18 #8

P: 27
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3.      Me.Filter = "[Work Completed By] is Null AND [Date Completed] is Null AND [Work Comments] is Null"
  4.      Me.FilterOn = True
  5.  
  6. 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.
Sep 28 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 2,705
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.
Sep 28 '18 #10

NeoPa
Expert Mod 15k+
P: 31,121
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.
Sep 29 '18 #11

P: 27
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
Oct 1 '18 #12

P: 27
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmd_save_Click()
  5.     Dim strName      As String
  6.     Dim dtDate       As Date
  7.     Dim strEmail     As String
  8.     Dim strMessage   As String
  9.  
  10.     strName = Me.txt_prov_name
  11.     dtDate = Me.txt_completion_date
  12.     strEmail = Me.txt_prov_email
  13.     strMessage = "Hello,"
  14.        vbCrLf & "Your request has been completed.  Please review the changes made to your clinic and reply to this Email if more changes are needed." & _
  15.  
  16.     Call DoCmd.SendObject(To:=strEmail, _
  17.                           Subject:="Clinic Change Request", _
  18.                           MessageText:=strMessage, _
  19.                           EditMessage:=True)
  20.  
  21.  
  22. End Sub
  23.  
  24. Private Sub Form_Open(Cancel As Integer)
  25.  
  26.      Me.Filter = "[Work Completed By] is Null AND [Date Completed] is Null AND [Work Comments] is Null"
  27.      Me.FilterOn = True
  28.  
  29.  End Sub
  30.  
  31.  
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?
Oct 1 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 2,705
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.
Oct 1 '18 #14

P: 27
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:
Expand|Select|Wrap|Line Numbers
  1.      strMessage = "Hello " & strName & "," & _
  2.          vbCrLf & "Your request has been completed.  Please review the changes made to your clinic and reply to this Email if more changes are needed." & _
  3.  
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.
Oct 1 '18 #15

P: 27
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
Oct 1 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 2,705
The SendObject Method has additional parameters: View this article that will help explain these. Just add these parameters to your expression:

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SendObject(ObjectType:=acSendReport, _
  2.                       ObjectName:="YourReportName", _
  3.                       OutputFormat:=acFormatPDF, _
  4.                       To:=strEMail, _
  5.                       Subject:="This is a Test Email", _
  6.                       MessageText:=strMessage, _
  7.                       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!
Oct 1 '18 #17

P: 27
Twinnyfo,

Your knowledge worked!

here is the code that I put in:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmd_save_Click()
  5.      Dim strName      As String
  6.      Dim dtDate       As Date
  7.      Dim strEmail     As String
  8.      Dim strMessage   As String
  9.      Dim strmsa       As String
  10.  
  11.      strName = Me.txt_prov_name
  12.      dtDate = Me.txt_completion_date
  13.      strEmail = Me.txt_prov_email
  14.      strmsa = Me.txt_who
  15.      strMessage = "Hello " & strName & "," & _
  16.          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
  17.  
  18.      Call DoCmd.SendObject(ObjectType:=acSendForm, _
  19.                            ObjectName:="Request - MSA", _
  20.                            OutputFormat:=acFormatPDF, _
  21.                            To:=strEmail, _
  22.                            Subject:="Clinic Change Request", _
  23.                            MessageText:=strMessage, _
  24.                            EditMessage:=True)
  25.  
  26. End Sub
  27.  
  28. Private Sub Form_Open(Cancel As Integer)
  29.  
  30.       Me.Filter = "[Work Completed By] is Null OR [Date Completed] is Null OR [Work Comments] is Null"
  31.       Me.FilterOn = True
  32.  
  33. 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.
Oct 1 '18 #18

NeoPa
Expert Mod 15k+
P: 31,121
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.
Oct 4 '18 #19

P: 27
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:

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

twinnyfo
Expert Mod 2.5K+
P: 2,705
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!
Oct 4 '18 #21

P: 27
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:
Expand|Select|Wrap|Line Numbers
  1. strclinic = Me.txt_clinic_name.bold = True
and
Expand|Select|Wrap|Line Numbers
  1. strclinic = Me.txt_clinic_name.bold = True
and
Expand|Select|Wrap|Line Numbers
  1. strclinic = Me.txt_clinic_name.fontbold = True
and
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 4 '18 #22

P: 27
sorry, on the second example, remove the " = True" argument and that is what I tried.
Oct 4 '18 #23

twinnyfo
Expert Mod 2.5K+
P: 2,705
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!
Oct 4 '18 #24

P: 27
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
Oct 4 '18 #25

twinnyfo
Expert Mod 2.5K+
P: 2,705
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....
Oct 5 '18 #26

Post your reply

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