Send Email with CDO 4
By Richard Rost
2 years ago
Send Email with CDO in Access, without Outlook Part 4 In this Microsoft Access tutorial, I will show you how to log sent emails in your contacts table using a record set. You will learn how to log email details such as customer, description, contact date, and notes in your database after sending the email. This is part 4. MembersIn the extended cut, we will cover additional features for sending emails from Access. I will show you how to add cc, bcc, and reply-to fields, send HTML-formatted emails, add attachments, and export a PDF from your database to attach to an email. 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!
PrerequisitesLinksRecommended CoursesCode Vault
Keywords TechHelp Access, send email using CDO in Microsoft Access, CDO email Microsoft Access, DoCmd.SendObject alternative, bypass Outlook in Access, log email in Access database, email customer form Access, Access VBA email logging, SMTP server in Access, add CC and BCC CDO Access, send HTML email Access VBA, Access email attachments, bulk email Microsoft Access
Intro In this video, we continue with part 4 of Sending Email with CDO in Microsoft Access, focusing on logging sent emails directly into your contacts table. I'll show you how to use a record set to add new email records, capture key details like customer ID, subject, date, and body, and discuss why this method is easier than using SQL statements. You'll also see how to test and debug the process for a reliable email logging system, all without needing Outlook or another email client. This is part 4.Transcript Welcome to another TechHelp video brought to you by AccessLearningZones.com. I'm your instructor, Richard Rost. Today's part 4 of my Sending Email with CDO, and not having to use Outlook series. So if you haven't watched parts 1 through 3, go watch those and then come on back.
Okay, so everything's working great. We got our customer form with the Send Email button on it. I can type in some stuff here and here, hit Send, and it sends it out, and we see a success, and everything works great.
Now, the only thing we have left to do is to log that in my contacts, right, when it's done going out. We're going to put it right here in our contacts. We have a history of everything we sent, kind of like a sent box. And yeah, if you're using Gmail in one of those programs, it'll also show up in your sent box there. But it's also nice to have a copy here in your database.
So we're going to use a record set, which is the best way. You could use an SQL statement to do that, but then you got to worry about, you know, escaping quote characters and all that. I just think it's easier to use a record set for something like this. If it's like adding a real quick number or like a short text or something, sure, I'll use an SQL statement, but record sets are much, much easier and better. If you want to learn more about record sets, I covered them in this video. They're really easy to use.
So let's come into here. Let's go to our email form again. Click Design View, and let's go into that button code build event. I know it's off the screen, but you should know by now. Click build event.
Okay. What do we got to do? Well, right here, if it's success, if it's success, try saying that 10 times fast. If it's a success, somewhere in here, we're going to log it. So we'll do it. Maybe right after we set the screen color here, we're going to say log sent email. And that's it. We're done. I'm just kidding. We got to still write that subroutine.
Okay. And here, private, private sub log sent email. And it's going to use all the same data on this form, this email form, so we don't have to pass any parameters to it. We do need a record set object, so dim rs as a record set. We're going to set rs equals current db.open record set.
And then where are we putting it? We're putting it in the contact table. All right. We're going to open a connection to the contact table in the current database. Now let's take a real quick look at the fields just to familiarize ourselves with ourselves. I can't talk today. What's going on? With the contact table.
So we've got the customer ID. We've got the description, the contact date, and then the notes for the body of the email. And if you want to make it a follow-up, you can, you know, you can put a box on there and say, hey, follow up with this guy in, you know, two days or whatever. That's up to you. Now, those are your Legos. You play with them however you want to.
So I'm going to say rs.addnew, that adds a new record. Then we're going to set the field. So rs, customer ID. Now we don't have the customer ID on the email form. If we look at it, we just have the email address. Now there's a lot of ways you could get the customer ID, all right? You could take the email address and do a look up in the customer table. That's one way. You could pass the customer ID to this form when you open it, just like we did with the email address. That's another way. You could make this form modal so that they can't change the customer and they can only get to it if the customer form is open. That way they can't, you know, you're guaranteed that this is there. So there's lots of different ways you can do it. That's up to you.
I'm just going to come in here and say, give me forms, customer F, customer ID. I'm going to assume it's open. There's all kinds of checks. You can check to see if it's loaded. All kinds of things you can do here. RS description equals, how about sent, email, and then the subject, how does that sound? Then we've got RS contact date equals now. I think that's a default in the table, but it never hurts to put it in here just in case, right? RS notes equals body, and that's where it's good to use a record set because you don't got to worry about escaping characters at all. If I had typed into my notes, or my body field here, any double quote characters. I would have to change those double quotes to double double quotes if I'm going to include this in an SQL statement. Where I'm saying insert into contact T and then the fields. Well, if you got double quotes, now it's going to break that string. If you do it in a record set, you don't have to worry about that. So that's why I like record sets.
I honestly learned record sets before I learned SQL because record sets are more like programming to me. SQL is database language. I started out as a basic programmer, so this just made more sense when I was learning how to do this stuff. All right? We're all done updating the fields. So RS update that saves the record to the table, RS close, closes the record set, and then we got to free up our memory. Set RS equals nothing. We don't have to, but it's a good idea too, okay?
And then when we're all done, status, oh, I got a glitch with my mouse. See, I moved my mouse over here and once in a while it just randomly clicks. I think I need a new mouse. Okay, so right down here, status logged in contacts, and that's it. We're done. Save it. Debug compile, make sure you got everything right.
Let's go back out meow, close it, and open it, and test, blah, blah, blah, blah, blah, with some quotes just to make sure it works. You can put some stuff in here with some single quotes. It's still going to work. Send email, send it out, log in contacts, and it closed. And now if we go to contacts, you can see it's right there. Then that purdy. And if you want to make it a follow-up, you can make a follow-up. I got a whole separate videos on how to handle that stuff.
So that's pretty much it. Now we have a fully functioning way to send email to our mail server directly, and we don't need a mail program on this machine. It doesn't require outlook. It doesn't require anything, except a direct connection to an SMTP server. If you want to learn more in the extended cut, I'm going to show you how to add a cc, bcc, and reply to field. We're going to see how to send HTML formatted email, so you can use either a rich text field in here. We can put colors and all kinds of stuff. Or if you know HTML and you want to write actual HTML in here and all that stuff, you can do that. I'm going to show you how to add attachments to your email, so you hit a little pick button here. You pick a file, and it puts the file name in there. And then to put it all together, I'm going to show you how to export a PDF from your database and then attach that to the email. So on the order form, we'll make an email button. It'll export the PDF, the invoice, and then open up an email, fill all this in for you, and then attach the file right there. Then all you got to do is hit send. You can either type in some notes for the user, or the customer, whatever.
That is all covered in the extended cut. Silver members and up get access to all of my extended cut videos. This extended cut alone is worth the price of admission, folks, just about a half an hour video. So it's a lot of good stuff if you want to learn how to send email from access. And a lot of this is stuff that I cover in my email seminar, but if you really want to learn about sending emails from access, the email seminar covers lots of stuff, including all the stuff that I just talked about a minute ago.
I talk about how to create a mail merge using access and word. We talk about broadcasting bulk email batches. I show you how to do a send delay because some email providers, like Gmail, they limit you to like 400 or whatever emails per day, so you can put a delay in there so you don't send more than you're allowed to. You can put like a two minute delay between sends or whatever. We can assign customers to mailing lists so you can have your prospects, your long time customers, whatever. I'll show you how to build a mail server database so you can have a database sitting in the corner that does all the mail sending and receiving for you. So all the people on your network just send through that instead of every person having to send their own emails. It's much more efficient for big groups.
The email seminar, I show you how to acquire data from incoming emails. That's pretty cool. How to build a web form and collect survey data. All kinds of stuff. This email seminar is one of my favorite seminars. It's got all kinds of stuff in it. We do mail merge right inside of access like that. All kinds of cool stuff.
But that is going to be your tech help video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and this is probably the end of this series. I think we're done with sending email through CDO. There is another sending email method I want to talk to you about. It's another quick way you can send email using PowerShell. Maybe we'll do that in tomorrow's video. But as far as this lesson goes, we're all done. Take care.
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.
TOPICS:
Sending email with CDO part 4
Implementing a Send Email button
Logging emails in contacts
Using a record set to log emails
Opening connections to tables
Adding new records with record sets
Setting fields in record sets
Handling customer ID for emails
Avoiding escaping characters in SQL
Updating and saving records with record sets
Debugging and testing the email logging
Saving logged emails into database
Logging email status in contacts
Sending email without an email client
Direct connection to SMTP server
Including cc and bcc fields in emails
Adding reply-to field in emails
Sending HTML formatted emails
Adding attachments to emails
Exporting PDFs from database
Attaching PDFs to emails from Access
Handling email follow-ups
Creating mail merges with Access and Word
Broadcasting bulk email batches
Implementing send delay to manage limits
Assigning customers to mailing lists
Building a mail server database
Acquiring data from incoming emails
Creating web forms for survey data
Mail merge directly in Access
COMMERCIAL:
In today's video, I dive into part 4 of my Sending Email with CDO series, showing you how to log sent emails in your Access database contacts. We've already covered setting up the email functionality; now you'll learn how to use a record set to save email details, including customer ID, subject, date, and body. I explain why record sets are more practical than SQL statements for this purpose. By the end, you'll have a fully functioning email logging system. Catch the full video on my YouTube channel and my website at the link shown. Live long and prosper, my friends.Quiz Q1. What series is this tutorial a part of?
A. Using Outlook for Email
B. Sending Email with CDO
C. Automating Tasks with Excel
D. Building Websites with HTML
Q2. What is the primary purpose of using a record set in this tutorial?
A. To build a user interface
B. To log emails sent in the contacts table
C. To create SQL queries
D. To generate reports
Q3. In the context of this video, what is the key advantage of using a record set over an SQL statement?
A. It is faster in execution
B. It handles data connection automatically
C. It avoids issues with double-quote characters
D. It provides better security
Q4. What is the necessary object to declare when using a record set in the code?
A. Email Set
B. Data Object
C. Record Set
D. File Object
Q5. In the tutorial, what notable data field is missing from the email form that is discussed?
A. Contact Date
B. Customer ID
C. Email Body
D. Email Subject
Q6. What method is suggested to retrieve the Customer ID if not directly available on the email form?
A. Using the email address to look up the Customer ID in the customer table
B. Ignoring the Customer ID altogether
C. Manually entering the Customer ID
D. Using a default Customer ID value
Q7. What command is used to add a new record to the record set?
A. rs.NewRecord
B. rs.Insert
C. rs.addnew
D. rs.Create
Q8. Why is it important to free up memory after closing the record set?
A. To prevent data loss
B. To avoid compilation errors
C. To reduce the load on the CPU
D. To maintain optimal performance and prevent memory leaks
Q9. What function is used to get the current date and time in the tutorial?
A. CurrentDate
B. DateTime.Now
C. GetDate
D. Now
Q10. Which event does the instructor suggest checking for debugging purposes?
A. OnClose
B. OnClick
C. OnLoad
D. OnError
Q11. What additional functionalities are mentioned in the extended cut of the tutorial?
A. Adding cc and bcc fields
B. Importing data from Excel
C. Performing mail merges with Word
D. Scheduling future email sends
Q12. According to the tutorial, what is a benefit of setting up a mail server database?
A. It allows for sending and receiving emails from multiple networks
B. It automates the email sending process for the entire network
C. It stores large volumes of incoming mail
D. It provides advanced security features for emails
Q13. What is the suggested way to handle email limits imposed by providers like Gmail in the email seminar?
A. Use multiple email accounts
B. Send fewer emails
C. Implement a send delay between emails
D. Use a third-party email service
Q14. What other method for sending emails does the instructor mention considering for a future video?
A. SMTP Client
B. IMAP Protocol
C. POP3 Protocol
D. PowerShell
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-A; 7-C; 8-D; 9-D; 10-B; 11-A; 12-B; 13-C; 14-D
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 continues with part 4 of my Sending Email with CDO and not having to use Outlook series in Microsoft Access. If you have not gone through parts 1 to 3 yet, make sure to check those out first, then come back to this segment.
At this stage, the email sending function is up and running. On the customer form, I have set up a Send Email button. When I compose a message and click Send, everything transmits correctly and a success message is displayed.
The remaining enhancement is to log each email in the contacts table after it is sent. This step provides a record of what has been sent, which acts much like a sent folder in an email client. While using services like Gmail will still place a copy in your sent folder online, it is very helpful to keep your own internal record in the Access database.
For logging emails, I prefer to use a record set instead of an SQL statement. While SQL works well for inserting simple values, record sets make it easier to deal with more complex data, especially when you have to handle special characters like quotation marks. Record sets avoid the need for escaping characters, so they are ideal for this type of logging. If you need more background on record sets, I have explained them in detail in a previous lesson.
To accomplish this, I go into the design view of the email form and open the code for the Send Email button. The process starts by determining whether the operation was successful. If so, I call a subroutine to handle the logging.
In the subroutine, I create a new record set connected to the contacts table. Before proceeding, I take a moment to review the fields in this table: there is a customer ID, a description field, a contact date, and a notes field where the email body will be stored. The table design also allows for follow-up tracking if you want to add that functionality.
When adding the log entry, the customer ID is required. The email form itself only has the customer's email address, not the customer ID. There are several ways to retrieve or supply the customer ID. You can perform a lookup in the customers table using the email address, pass the customer ID to the form when opening it, or make the form modal so it is always tied to an open customer record. For now, I am retrieving the customer ID from the customer form, assuming that form is open. There are more robust ways to check for this, which you can explore based on your application's needs.
With the record set, I assign the appropriate fields: customer ID from the customer form, a description that includes 'sent email' along with the message subject, the contact date set to the current time, and the notes field containing the email body. Using a record set to assign these fields is much easier than constructing a long SQL string, especially if the message body contains quotation marks, which often cause problems in SQL statements.
After setting all the field values, I save the new record, close the record set, and clear it from memory. At the end, I update the status to show that the email has been logged in contacts.
Once everything is in place, I test the system by composing and sending another email, including some single and double quotes in the message to ensure the logging works correctly. After sending, I confirm that the email is properly recorded in the contacts table. This successfully provides a built-in sent email log inside Access, which can include any extra fields or features like follow-ups if desired.
With this system, you can now send emails directly from Access without needing any traditional email software installed. All it needs is direct access to an SMTP server.
In the Extended Cut of this lesson, I explain how to add cc, bcc, and reply-to fields to the email form. I also demonstrate sending HTML-formatted emails either by using a rich text field or entering HTML code yourself. Further, I cover how to add attachments to emails, including picking files and even exporting PDFs directly from your database and attaching them before sending. You will see how to automate this entire process from creating the PDF to attaching and sending the email with just a few clicks. These are powerful tools for streamlining your workflow.
All of these topics, along with handling follow-ups, are explored in detail in my email seminar. The seminar also covers advanced topics such as mail merges between Access and Word, sending bulk email batches, implementing send delays to comply with email provider limits, assigning customers to mailing lists, and even building a dedicated mail server database to centralize outgoing mail. I also demonstrate how to process incoming emails and gather data from web forms. The seminar provides a comprehensive overview and is especially useful if you want to master email integration in Access.
That wraps up today's TechHelp tutorial. I hope this helps you to implement and understand email logging within Access. 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 Logging sent emails in Access contacts
Using a record set to log email data
Opening and connecting to the contacts table
Adding a new record with record set
Assigning fields in the record set
Handling customer ID when logging email
Setting contact date and description fields
Storing email subject and body in notes
Avoiding escape character issues in SQL
Updating and closing the record set
Testing email logging with special characters
Verifying logged emails in the contacts table
Sending email using CDO without Outlook
Direct SMTP connection from Access |
