Send Email with CDO 3
By Richard Rost
16 months ago
Send Email with CDO in Access, without Outlook Part 3
In this Microsoft Access tutorial, I will show you how to use CDO for sending emails instead of relying on DoCmd.SendObject and Outlook. We'll cover error handling, validating email fields, and adding visual feedback for email status on your forms. This is part 3.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Send Email Using CDO in Microsoft Access, Microsoft Access CDO email, CDO email tutorial, Microsoft Access VBA, SendObject alternative in Access, Validate email data Access, Debugging CDO email Access, Error handling CDO email, Access vba on error, Access email function boolean return, Access email workflow improvement
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. In today's video, we're continuing with our CDO email series. This is part three. If you haven't watched parts one and two yet, you know what to do. Go watch those, then come on back.
All right, we've got the email working. It should send. But what happens if someone just comes in here and hits the button and validates it? No, you get all kinds of errors and stuff. Well, let's do some work to fix that. Let's do some checks first.
So I'm going to come in here. I'm going to close this Project Explorer. We don't need that. Let's check the values. If is null, no email, or is null subject, or is null body, then, and you can make these separate messages if you want, I'm just going to say message box, missing data, and then exit sub. All right, you want to do, you know, three separate checks. That's fine. Tell them they're missing the email. Tell them they're missing the subject. You can also check for if they're empty strings, because sometimes if the user had an email in there, and then let's say they went back and then deleted it, sometimes it'll get saved as an empty string. You can check for that as well. That's up to you.
Want to learn more about null and is null and all that stuff? Check out this video. I'll put a link down below. All right, now down here we got it done, but we don't know if it succeeded or failed. In fact, let's put above it, let's put a status sending email like this, and then it'll do its thing, and then done. But I want to know whether or not it actually went out or not, whether it was actually successful. So let's turn simple send email into a function that will return a true or false, whether it was successful or not.
So let's go into here, right-click, definition. Here we are. The first thing I'm going to do is change this from a sub to a function. Remember, a function returns a value. We're going to return as boolean. We're going to make a boolean come back. That's true or false value. And right down here, let's initialize the value of it, right? In simple send email, let's initialize it to false, okay? And if we get down to here without an error, let me scroll up here a little bit so you can see better. Sometimes between videos I play with stuff and I make my window bigger and I forget to shrink it for you guys. We're going to initialize it to false, but if we get down to here without any errors and this finishes, then we know we're good. So we can set it back to true down here.
So down here, return a success. We're going to say, I'm just going to copy it from up here. Where are you? Simple send email equals true. If we get this far without an error, okay? Now, how do we know if there's an error? Well, we can do that with a little error handling. And up at the very top, I'm going to say, here's our error handling. I'm going to say, on error, go to, let's call it my error, just like that. In other words, if an error is occurred in here somewhere, it's going to jump to a location called my error. Where's that? Well, let's come down to the bottom.
All right, way down here at the end, we're going to set up an area called my error. This is where it's going to jump to if there's an error, okay? Now, in here, I'm going to say, simple send email is false, okay? We're going to clear the error level, so error.clear. That basically says, okay, I know that there was an error message, clear it, and then we're going to continue on. We still have to clean up. We still have to release these objects here and exit the function, right? So I'm going to say, resume, clean up. And where's clean up? Well, we're going to put clean up right here. Clean up like that. And then when clean up is done, exit the function.
I know it looks a little weird, and I know it's kind of jumping around, but it makes sense when you look at it in the broad picture, all right? From the top, if an error is encountered, we're going to jump to my error. So if we're doing this, doing this, doing this, doing this, for any reason, it can't send. It's immediately going to jump down here to my error and say, simple send email is false. That means we got a problem. All right, we know there's a problem. Clear the error message so we can continue and then go to the clean-up spot. The clean-up spot is right here. It frees up the memory and exits the function, okay?
Now, if we do go through all this next run and it is able to send successfully, what's going to happen? It's going to say simple send email is true, and then it goes right into the cleanup, which just clears the memory and exits the function. See that? So it only makes this little detour down here if there's an error. This is the easiest way to handle this in Visual Basic. Some other languages have more intuitive error handling, but this is how it works in VB. I do have another video on error handling where I talk about this in more detail, and I spend a lot of time on this in my Access Developer 15 class. We go over a lot with debugging.
All right, so now this function should be able to return a success or failure. So let's save this. Let's debug compile always. All right, let's go back to our form now and right-click design view. We'll come back into here, build event. All right, so now we can get this status level. So let's put that in a variable. You don't have to, but I like to. Dim, send okay as a Boolean. And here we're going to say send okay equals, and this is now a function. So we're going to put parentheses around it. Okay, if it's a function, you got to have those parentheses. So now we know if it was okay or not if it's sent or not.
And now we can say if send okay, then status success, right? And I mean, you could do stuff like change the background color of the status box, right? So status box dot back color equals, let's go RGB, 255, 100, 100, that's going to be like green. And I like to put a little pause in here so that the user sees the green change before we close the form. So let's go sleep three seconds. And the sleep function is in this database, it's in my TechHelp free template. You'll find it right here. All right, or you can use sleep seconds, either one, I got a whole separate video that explains how that one works. It's just basically it allows you to introduce a little delay into your code. You want to have something show on the screen so the user can see it. And if you use sleep sec, what it'll do is say sleep seconds three, it'll allow do events to run every second. That's why I wrote the sleep sec one as well.
Otherwise, oh, and then we can close the form, right? Do command close will close the form. AC form, which form? me dot name, the name of the current form. And then I always put AC save yes in here. That's for you, the developer, in case you've made some changes and you didn't save your form, and you run the code, it'll save your changes for you.
Otherwise, if it's a fail, we'll bring this guy up here then. And we'll put in here fail, like that. And then we'll make the status box, let's go red. So we'll change this to actually that one is green. This one is not green. This one is, let's do this 100 255 that's RGB. So you want the big number to be in the green here. So this one is red. My bad. Okay. And then for this one, we won't close the form. We'll just leave the form open. So the user can see it's a failure. All right. So save that. Let's debug compile. And let's test it. Send mail, blah, blah, blah, blah, blah, blah, send. And success. And there's my color change. Same. And it closes the form after three seconds.
And if you want to simulate a fail, all you got to really do is come in here and do this. Just come in here and say right before you do anything. We're just going to say simple mv equals false, and then exit function. This is just going to force a failure. Right. If you want to test your fail code. Blah, blah, blah, blah, blah, blah, fail. Same. We'll get rid of that. Okay. Save it. Actually, let's put that back real quick. Put that back.
What you could also do if you want to know why it failed. Okay. You can come down here. And if it errors out, you can take the error level, which is a number like error 400 or whatever error 503, and you can put that in a, let's put it in a temp var. We'll do that for fun. We'll put it in a temp var. Right. So we'll say here temp vars, temporary memory variable. I can't talk tonight. Let's call it, I don't know, send email error level equals, and this will be error dot number. And then we'll do the same thing for the description. Error dot description. And that's going to go in instead of error level. It'll go in error description, just like this. See that? That'll go into a temporary memory variable.
I like temp vars. They're very versatile. They work across the entire database. You can use them in forms and your code and your queries everywhere. They're better than just regular variables. And then, you know what? I'm not going to simulate it. Just trust me, it works. If you get a real error message, you can test it by like unplugging your router, and then that's going to time out, and you won't see anything. But if we go back over here now, we could say status fail, and then we can just status that stuff. Whoops, we can status. I've got it in my clipboard here. We're going to copy this, and we'll just put that right there. Status error level, and then put that temp vars. And then the description, and then that temp var. Same, if you get a failure. And just trust me, it works. We're not going to have to walk through all that.
So now you know whether it was successful or not. And if it's not successful, what the problem is. Is it a bogus email address that you're sending out, running all that stuff? All right, so debug compile. Everything looks good. Come back over here. Close this. Save changes. Send email. One more email, blah, blah, blah, blah, blah, blah. And click sending email, success. And we're done. And there you go. Let me check my inbox. And there it is. One more email came through.
All right, there's one more thing we're going to do in this series, and that is take that email that we just sent and store it in the contacts. And we'll do that in the next lesson. And so yeah, you know the drill, tune in tomorrow, blah, blah, blah, blah. Members, you can watch it right now. So that's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part four.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
Want to learn more? Click the show more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the video is up here. There's a little show more down there, right down the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons and lots more. And YouTube no longer sends out email notifications when new videos are posted that they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. And you can pick how frequently to get emails from me, either as they happen, daily, weekly or monthly.
Now, if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.
Now, if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free, four hours, go watch it.
And okay, okay, a lot of you have told me that you don't have time to sit through a four-hour course. So I do now have a quicker Microsoft Access for beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward, but I'll put a link to this down below as well. Now, if you like Level 1, Level 2 is just a dollar. That's it, one dollar. And that's another whole like 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2, it's free.
Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered.
And while you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started. Now, let's talk more about those member perks if you decide to join as a paid member. There are different levels, silver, gold, platinum, and diamond.Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos.
You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them. And if I like your question, you have a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, and lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones.
In addition, once you finish the expert classes, you get one free developer class per month, so lots of training. And finally, you can also become a diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shoutout in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.
TOPICS:
CDO email series continuation
Error handling for missing data in email forms
Checking for null or empty strings in VBA
Using message boxes for missing data alerts
Turning a VBA subroutine into a function
Returning Boolean values in VBA functions
Initializing and updating function return values
Implementing error handling with on error goto
Creating a custom error handling section in VBA
Clearing errors and executing cleanup code
Using resume cleanup to manage end-of-function tasks
Displaying email status to the user
Changing form elements based on email status
Implementing a sleep function for delays in VBA
Using temporary variables (tempvars) for error details
Debugging and testing email sending functionality
Simulating success and failure for testing purposes
Storing errors in temporary variables for troubleshooting
COMMERCIAL:
In today's video, we continue our CDO email series with part three. We'll troubleshoot common errors when sending emails, ensuring all fields are validated before proceeding. You'll see how to turn our `SimpleSendEmail` into a function that returns a true or false, indicating success or failure. We'll walk through implementing error handling to manage any issues that arise and visually show the email's status with color feedback and delays. Finally, you'll learn tricks like using `tempvars` for storing error details and see the complete process in action. You'll find the complete video on my Youtube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What should be checked first before sending an email to avoid errors?
A. Whether the user has an administrator role
B. If the email, subject, and body fields are not null or empty
C. The date of the email
D. The recipient's IP address
Q2. In the video, how does Richard suggest handling errors in the `SimpleSendEmail` function?
A. By entirely skipping the operation if an error is detected
B. By using a dedicated error handling block that sets a Boolean value
C. By recording errors in an external log file
D. By displaying an immediate error dialog and stopping the program
Q3. What kind of value does the modified `SimpleSendEmail` function return?
A. String
B. Integer
C. Boolean
D. Float
Q4. Where does the function jump to when an error occurs, as shown in the video?
A. To the end of the function
B. To a `myError` label defined at the end of the function
C. To a global error handler
D. To a new function
Q5. What is the main purpose of the `Resume Cleanup` statement in the error handling block?
A. To restart the function from the beginning
B. To delete all temporary files
C. To free up memory and exit the function properly
D. To ignore errors and proceed with the next line of code
Q6. Why does Richard use a temporary variable (`TempVars`) for storing error details?
A. It helps in persisting error details across the whole database for later use
B. It makes the error details visible to the user directly
C. They can be used only once and then discarded
D. They are synchronized with the cloud automatically
Q7. What specific Visual Basic feature does Richard suggest using to introduce a delay?
A. `Wait` function
B. `Pause` function
C. `Sleep` function
D. `Delay` statement
Q8. In this tutorial, what visual feedback does Richard suggest giving to users to indicate success or failure?
A. Playing a sound
B. Changing the status box background color to green for success and red for failure
C. Sending a confirmation email
D. Logging the status in a text file
Q9. Which method does Richard use to close the form after a successful email send operation?
A. `Form.Close()`
B. `Application.Shutdown()`
C. `DoCmd.Close()`
D. `Form.Exit()`
Q10. What is the next topic Richard plans to cover in the following video?
A. Advanced error handling techniques
B. Storing the sent email in the contacts database
C. Adding attachments to emails
D. Setting up email server configurations
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-A; 7-C; 8-B; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on continuing our series on sending emails using CDO in Microsoft Access. This is the third part in the series, so if you have not reviewed the first two lessons yet, I encourage you to catch up on those before you proceed with this material.
At this stage, the email sending feature is already up and running. However, there are still potential issues if users attempt to send emails with missing or incomplete information. For example, if someone tries to send an email without specifying the recipient address, subject, or body content, errors will occur. To address this, it is necessary to introduce validation checks before attempting to send anything.
The first step is to review the user input fields and verify that each necessary piece of information is present. If any of these fields are missing or empty—including the possibility of empty strings left behind after a user deletes prior entries—a message should notify the user about the missing data, and the process should halt immediately. This approach ensures that incomplete emails are not attempted.
If you are interested in a more in-depth discussion about handling null values or about the IsNull function in VBA, I have more resources available and will provide links for further reading on my site.
Moving forward, it is also important to determine if the email was actually sent successfully. To do this, we need a process that provides clear feedback on the operation's outcome. The solution is to convert the existing SendEmail subroutine into a function that returns a Boolean value to indicate success or failure. This way, you can check the result of the operation and act accordingly.
When you change a sub to a function, you must define the type of value it will return—in this case, Boolean, which represents true or false. You should initially set the function's result to false. As long as no errors occur and the code completes successfully, you can update the return value to true just before finalizing the function.
Error handling is also essential. In VBA, you can use the "On Error GoTo" statement to direct the code to jump to an error handling section if any errors are detected. In this section, the result of the function should be set to false, the error cleared, and any necessary cleanup code executed. The cleanup ensures that all objects are released and memory is cleared properly before exiting the function. While this kind of structure might seem convoluted at first, it is a typical way to manage errors in Visual Basic. If you are interested in error handling in greater depth, I have separate lessons and courses that cover this topic extensively.
Once the function is in place and error handling is working, the form that calls this function can check its result. You can store the function's return value in a Boolean variable—for instance, SendOK. If the operation was successful, you can display a "success" status to the user and even add visual feedback, such as changing the background color of a status textbox to green. You might also want to introduce a short pause (using a sleep function) so the user can see the confirmation before the form closes. There are different implementations of a sleep routine available in my templates, and I have a whole lesson dedicated to that as well.
If the function returns false, you should indicate failure to the user, perhaps using red to highlight the error. The form can then remain open, allowing the user to see the outcome and correct any issues.
To help with troubleshooting, you can also capture the error number and error description in temporary variables using TempVars, which are accessible across your database in code, forms, and queries. This technique is useful for debugging and for providing additional information to users or developers.
Testing is always important. To simulate a failure, you can temporarily force your function to return false right at the start—for example, by simply setting the return variable and instantly exiting the function. Do not forget to remove this line after your test. For real-world error testing, deliberately removing your internet connection can help trigger error conditions.
After setting everything up, make sure to save your work, perform a debug compile to detect any coding issues, and then test both the success and failure scenarios to see the visual feedback in action. If all is working, successful emails will trigger the confirmation display and then close the form; failures will provide error details and prompt the user to make corrections.
In our upcoming lesson, the final part of this CDO email series, I will cover how to log each sent email in your contacts list, providing a full audit trail of communications.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Error handling for missing email form data
Checking for null and empty string values
Displaying message boxes for missing fields
Converting a sub into a function in VBA
Returning Boolean values from functions
Initializing function return values
Implementing On Error GoTo in VBA
Creating a custom error handling section
Clearing errors with Error.Clear
Executing cleanup code after errors
Using Resume to jump to cleanup section
Displaying sending status to the user
Changing status box background color
Pausing code execution with Sleep
Using temporary variables to store error number
Using temporary variables to store error description
Displaying specific error details to the user
Simulating failure to test error handling
Closing forms programmatically after sending email
|
