Followups 5
By Richard Rost
4 years ago
Customer Followups in Microsoft Access, Part 5
In this Microsoft Access tutorial, we're continuing work on our Followup database. We will make a followup email system. We'll start by making a table to hold mini email templates to respond with. We'll make a combo box on the customer form to select a template, and then our email program will launch and populate the template text into the email.
Pre-Requisites
Links
Up Next
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, How To Create A Customer Follow-Up System, organize your database followups, Database Follow up, follow-up template, Sales Follow-up System, customer follow-up system, Creating an Effective Follow Up System, show only followups, button from main menu, customer combo, double-click to open, contact management, crm, Quick Followup Emails, Email Template Table, Template Combo, Sending Email
Subscribe to Followups 5
Get notifications when this page is updated
Intro
In this video, you will learn how to add a button in Microsoft Access to quickly send follow up emails using pre-defined templates. I will show you how to create an Email Template table, set up a combo box to select responses, and use VBA with the FollowHyperlink method to send customized messages through your email program with just one click. This is part 5.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's class, part five of the Follow Ups series, we are going to add a button to send a quick follow up email to whoever you are following up with. I know that is part of my follow up strategy: follow up and then send off an email, whether it is, "Hey, thanks for calling me today," or "Thanks for not being in your office when I tried to get you this time."
We will make a little template table with some common responses like I just mentioned and then show you how to send it off with one click.
Of course, if you have not watched parts one through four, go watch those first. I will put links down below that you can click on to get started.
Our little follow up database is working nicely. We have our list of follow ups here. Now, let us say I just got off the phone with Geddy Lee. I gave him some congratulations on his latest book about basses, and I want to now send him a quick email. It says, "Thanks for taking my call, appreciate everything we talked about, blah blah blah, whatever."
I have other lessons where I show you how to make a button to send an email. There is this one you can go watch, which goes in depth over how to send emails via Outlook, and you can add attachments and stuff. But today, we are going to use the strategy that I cover in this video, which is the Follow Hyperlink button. We are going to do something a bit different with it.
One of the beauties of these lessons that I put together for Fast Tips is that I might have shown kind of how to do this before, but now I am going to put the Legos together in a slightly different way so you can see how this works. Watch this video if you want to learn more about following hyperlinks.
We are going to use the email hyperlink to send them a quick thank you letter or whatever. Nine times out of ten when I send a follow up response, it is just a quick couple of sentences: "Thanks for taking my call," whatever. So let us make a little table, just real simple email templates, that are like the top two or three things you might want to send as a follow up email.
Go to Create and then Table Design. We will call this our Email Template table.
- EmailTemplateID: AutoNumber
- Description: Short Text
- Subject: Short Text
- Body: Long Text
Even though I am using a long text field for the body, do not make this really long. We are going to use a special technique to send this to your email client, so we do not want paragraphs here. We want just a couple of sentences, and do not use any special characters like ampersands and such. Just keep it straight and simple. We will see why in a minute.
Save this as My Email Template table.
If you want to make this all fancy, you could build a form around this if you want. I am not; I have done a million videos on how to do that. We are just going to put some quick data in here in the table.
Description is just for you, and then Subject and Body are what actually get sent to your email program.
Here are some samples:
Description: Thanks for taking call
Subject: Thank you for taking my call today
Body: Thanks a lot for taking my call. It was a pleasure speaking to you. Blah blah, whatever else you want to put.
If you want to zoom in, you can shift+F2 to zoom in. You can add extra lines if you want, but I recommend just keeping it to a simple paragraph.
How about another: Invoice attached
Description: Invoice attached
Subject: Copy of your invoice attached
Body: Thanks again for your order. Your invoice is attached. Call me if you have any questions.
And one more: Tried calling
Description: Tried calling
Subject: Sorry I missed you today
Body: I tried calling today but apparently you are too busy and important to take my calls. Bye. I am just getting passive aggressive now.
So, we have three real simple email templates to use. Save changes.
On our follow-ups form, we are going to go to the person's follow-up to get the phone number and all the other stuff. I will put a little box, a combo box, next to their email address.
If I want to send them one of those prepackaged templates, I just drop that box down, pick it, and it will automatically send the email. We are going to have it load up in your email program so you can edit it more in the email program. This is just to give you a starting point.
Right click, go to Design View, and make a little bit of room. We do not need to see the whole big combo box, just the drop-down part. Like I said, same Legos, different way to put them together.
Give me a combo box. Have the combo box look up values with Table/Query. We are getting stuff from our Email Template table.
What do you want in here? I just want the ID and the description in the box itself. We are going to sort by description.
Next, it is going to look like that. Make that about as wide as you think it needs to be. Good enough. Next, and we are just going to remember this value for later use; we are not actually saving this in the table. Do not do anything more with it there. Next, label is going to get deleted anyway, and Finish.
There is my combo box. Delete the old label, and save it. Now, close it, and take a peek at what we have. That is what we have right now.
Maybe make this a little wider. Go to Design View.
Actually, let us do the next trick first. Do not worry about the column width. Now, we are going to take this and shrink it down so all we see is the drop-down part, a little smaller, right there, good enough. Slide that there. Move the email right up next to it, maybe one pip away. Just like that, now that looks all right. Save it, close it, and open it back up again.
Now all you have is a little drop-down sitting there. Click and you still see it when it opens up.
A combo box has a couple of different widths associated with it if you bring up its properties. It has a Width, which is that .1667 - that is tiny, we made the width of the box small. But there is also a List Width, which is the width of the list when it is open, and your Column Widths, which is the width of each column inside the box, which are now set to zero, semicolon, and so on. The first column is the hidden column (the ID), so that is zero, and the next one is 1.3. Make this a little bigger, change this to 2. Also, make the List Width 2 when it is open. You want this to add up to the right width. If you have three columns in here, like 2 and 1, you want to have this as 3, and maybe sometimes even a little bigger to accommodate a scroll bar if you have a lot of items, but we do not.
Save that, close it, open it back up again, drop it down - that looks a lot better.
When I click on one of these things, I want it to fire up my email program. Whatever your email program is - I am using Gmail so for me it will load up in a browser, but it will load up in Outlook or whatever your email program is. That is the beauty of the Follow Hyperlink. The VBA will just open whatever program you have associated with that file type.
Go into Design View and open up the properties for this combo box. I do not like Combo30, so let us give it a good name first. Let us call this MyEmailTemplateCombo.
Now, we are going to put some code in. Go to Event and then After Update. After I change the value in this box, we are going to do some stuff.
What stuff are we going to do? The first thing we have to do is use DLookUp to get those values out of the table that we are going to be using for the email. We need to get the subject and body.
If you have never done DLookUp before, go watch this video. DLookUp is probably one of the most important functions you will ever use in Access. It is used to get values from another table.
While you are at it, watch the Nz function. Sometimes the DLookUp function returns a null if something does not exist, and that will generate errors in your code. Nz gets rid of those errors.
I am going to look up those values from the table, but I have to have something locally to store them in. We are going to dim Subject as String and Body as String. That is called dimensioning variables. If you have never worked with variables, go watch this video. It will teach you all about it.
I am going to check to make sure there is an email address in the customer form. Otherwise, I am just going to exit out. If they did not even put an email address, you cannot send the person an email. So we are going to say, If IsNull([Email]) Then Exit Sub. You can do more - you could pop up a message and say "Hey, you have to put an email address in first" - but for now, we will keep it simple.
Of course, if you have not done IsNull yet, I have a video for that too. I will put a link down below.
Now we are going to get those fields from the table based on the ID the user selected in the combo box. So we will say:
Subject = DLookUp("Subject", "EmailTemplateTable", "EmailTemplateID=" & MyEmailTemplateCombo)
If Nz(Subject, "") = "" Then Subject = ""
Same thing for the body:
Body = DLookUp("Body", "EmailTemplateTable", "EmailTemplateID=" & MyEmailTemplateCombo)
If Nz(Body, "") = "" Then Body = ""
By the way, that trick does not work if it is inside a string; you have to capitalize this stuff yourself, but for other variables, yes.
All right, so I have the subject and body in local variables, right in memory. Now I can send to my hyperlink by saying FollowHyperlink.
To follow a mail hyperlink (an email address), you start off with mailto: and then the email address. If I just stopped here, that would work just fine. Let me save that. Let us test it.
Let me go over here and close this, open up the form, drop it down, and send "Invoice attached." Click, and it is opening up in my other screen. Let me move it - and there it is. It opened up in my Gmail in my web browser. It already addressed it to them, and brought up my signature from Gmail. Here is the subject line, and I can put whatever body I want there. But we want the database to populate the subject and the body as well.
Close this, go back to our VBA window. How do we add the subject and body?
A little more string concatenation here: ampersand, question mark, subject equals, loads of quotes, and whatever is in the subject variable. That question mark tells the web browser, "Whatever's coming after this are parameters." I am sending you something called Subject. What is it? It is the subject variable. Save it, come back in here, try it again, "Thanks for taking my call," and boom. There it goes: "Thank you for taking my call today," right?
How about the body? Look up here at the URL line - it is got your mail.google.com, then right here it changed to subject=Thank you for taking my call. This is why you do not use ampersands or question marks or funky characters, because this is a simple technique. If you want to send more robust stuff, I will give you some links in a minute to better email stuff. This is just quick.
How do we add the body? Now, do not put another question mark in here; the question mark only starts off the parameters. To add another parameter, you have to put an ampersand inside the string.
... & "&body=" & Body
That is it. That is all you need right there. Save it, close it, and let us tell Geddy, "I tried calling." There it is. I have noticed with Gmail, especially if you specify a body, it does not bring in your signature lines. You can add them yourself if you want, but usually I do not populate that; I just want the subject, and I type in the rest myself, but that is just me.
This is very simple, just something to fire off a follow up email for "Invoice attached," and of course, you will attach the invoice, that kind of stuff.
So there you have it, that is how to fire off a quick follow up email in just a couple of clicks.
That is it - there is your part five. Hope you learned some stuff. I want to know if you want to see anything else added to this, or if you have ideas for another series, not just follow ups. Let me know what kind of database you want to see me build. Put that in the comments down below, and I will gather up all the comments after a week or two and say, "Okay, I have 15 people that want to see this, so we will do this."
I think I have enough things left to cover for follow ups for one more lesson. I might do more in the future if you drop some ideas on me, but I have a couple things here involving workdays and stuff that some people want to see, like how to move a follow up ahead, but only if the next days are work days. For example, if you are on Friday and you say "move to tomorrow," I want to move it to Monday.
We are going to cover that in part six.
That is all. Bye-bye.
Quiz
Q1. What is the main purpose of the addition covered in this lesson of the Follow Ups series?
A. To send follow up emails quickly using email templates
B. To track follow up phone calls
C. To create a list of customer invoices
D. To automate appointment scheduling
Q2. What table is created in this lesson for storing common email responses?
A. Customer Contact table
B. Email Template table
C. Follow Up History table
D. Call Log table
Q3. In the Email Template table, which fields are included?
A. TemplateName, Message, EmailID, Notes
B. EmailID, Response, Attachment, Priority
C. EmailTemplateID, Description, Subject, Body
D. Subject, Body, SentDate, ReplyStatus
Q4. What type of field is recommended for the Body of the email template?
A. Number
B. Hyperlink
C. Long Text
D. Date/Time
Q5. Why should special characters, such as ampersands, be avoided in the email Body field?
A. Because they can make the email too long
B. Because they interfere with how parameters are sent in hyperlinks
C. Because special characters are not supported in Access tables
D. Because they cause errors with attachments
Q6. What user interface control is added to the follow-ups form to select an email template?
A. Text box
B. Option group
C. Check box
D. Combo box
Q7. How are the email templates displayed to the user in the combo box?
A. By Subject only
B. By showing Subject and Body together
C. By ID and Description, sorted by Description
D. By Email address
Q8. What is the purpose of adjusting the List Width and Column Widths properties of the combo box?
A. To ensure the email sends correctly
B. To display the drop-down and its columns clearly to the user
C. To hide all values from the user
D. To connect the box to the correct table
Q9. What function is used in VBA to retrieve the selected template's Subject and Body from the table?
A. DSUM
B. TOP
C. DLOOKUP
D. CONCAT
Q10. Why is the Nz function used in conjunction with DLookUp?
A. To convert text to numbers
B. To avoid null errors if a field is missing
C. To append extra lines to the email
D. To check email addresses for errors
Q11. Before sending an email, what field is checked to ensure the process can continue?
A. Subject is not empty
B. FollowUpID is set
C. The contact's email address exists
D. The current date is a weekday
Q12. What command is used in VBA to launch the email client with pre-filled email details?
A. SendMail
B. ShellExecute
C. FollowHyperlink
D. OpenForm
Q13. In the mailto URL, what character is used after the initial question mark to add more parameters (such as Body)?
A. Semicolon
B. Colon
C. Ampersand
D. Period
Q14. After implementing the solution, what does clicking a template in the combo box do?
A. Automatically sends the email without user review
B. Opens an email draft in the default email client with the template details
C. Prints the template to a PDF
D. Saves the email to the database
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-D; 7-C; 8-B; 9-C; 10-B; 11-C; 12-C; 13-C; 14-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 video from Access Learning Zone continues our Follow Ups series with part five, where we focus on enhancing our Access database by adding a button that sends a quick follow up email to your contacts. This feature is useful as part of a follow up strategy, allowing you to easily send off a thank you note or a message after an interaction, such as, "Thanks for your call today," or even a friendly nudge when you cannot reach someone.
In this lesson, I walk you through creating a simple table to store email templates containing common responses. These templates help save time and streamline the process, letting you send a basic, ready-to-edit email with just one click.
If you have not already watched parts one through four of the Follow Ups series, I recommend checking those out first to ensure you're up to speed on the project so far.
Our follow up database already has a list of contacts you need to communicate with. For example, if I just finished a call with Geddy Lee to congratulate him on his latest book and want to follow up with an email, I want the process to be as smooth as possible. Most of the time, the content of these emails is just a quick note of thanks or similar short message.
Although I have other lessons that go into detail on building buttons that send emails through Outlook, including how to add attachments, today's approach leverages the FollowHyperlink method. This method takes a slightly different path to accomplish the task.
A key benefit of these Fast Tips lessons is that while I may have shown elements of these concepts before, today we are combining them in a new way to fit this follow up scenario. In this case, using the email hyperlink makes sending these short messages efficient.
We begin by setting up a straightforward table for email templates. In Table Design view, I add four fields:
- EmailTemplateID: AutoNumber
- Description: Short Text
- Subject: Short Text
- Body: Long Text
Even though the Body field is long text, I advise against writing lengthy paragraphs here. Because we are sending these through your email client using a mailto link, you want to keep the content simple and avoid special characters like ampersands. The template body should be concise—a few sentences at most.
After saving the table as something like "My Email Template," fill in a few common scenarios. For instance, you might have:
- Description: Thanks for taking call
Subject: Thank you for taking my call today
Body: Thanks a lot for taking my call. It was a pleasure speaking to you.
Another might be for sending invoices:
- Description: Invoice attached
Subject: Copy of your invoice attached
Body: Thanks again for your order. Your invoice is attached. Call me if you have any questions.
Or a template for when you are unable to reach someone:
- Description: Tried calling
Subject: Sorry I missed you today
Body: I tried calling today but apparently you are too busy and important to take my calls. (Of course, feel free to make it less passive aggressive.)
Once you have a few templates in place, we set up the follow ups form. Next to a contact's email address, add a small combo box. This will allow you to choose a template, triggering the process of creating a quick email. The idea is to provide a starting point for your message, which you can edit further in your email program before sending.
In Design View, make space for a compact combo box. Configure the combo box to use the Email Template table as the source, showing the description for each template. You only need the template ID and description as visible columns, sorted by description.
Once the combo box is positioned as desired, pay attention to its properties. The Width property controls how much space the box takes up on the form, while the List Width determines how wide the drop-down list appears when you click the combo box. Adjust the Column Widths as needed so that the description shows properly while hiding the template ID.
Now, when a template is chosen from the combo box, we want the database to open your default email program (such as Outlook or Gmail) and populate it with the recipient's email address, subject, and body from your selected template. This is handled in VBA by using the FollowHyperlink method.
First, rename the combo box to something meaningful, like MyEmailTemplateCombo. In the After Update event for this combo box, add the necessary VBA code. We use the DLookUp function to fetch the Subject and Body from the Email Template table based on the template selected. If you have not learned DLookUp, I recommend studying it in detail, as it is a vital Access tool for retrieving data from tables. I also mention the Nz function, which prevents errors if DLookUp returns a null value.
Before proceeding, the code checks to make sure an email address is actually present in the contact form. If not, the code exits early to avoid an error.
With the email address, subject, and body ready, we construct a mailto hyperlink. The base is "mailto:" plus the email address. Adding a subject uses "?subject=" followed by the subject text, and to include the body, form "&body=" and attach the body text. Remember, since ampersands and question marks are part of the syntax, avoid using these characters in the template fields. This technique is good for quick, basic emails but is limited in formatting and complexity. For more advanced features, I recommend checking out my other videos on sending robust emails with more control.
Once implemented, this setup allows you to select a template and instantly open an email draft ready to send using your default mail client, pre-filled with the necessary details. While some email programs may not automatically include your email signature when the body is pre-populated, you can always add it manually if needed.
This was a simple, effective approach for sending out follow up emails with minimal effort. I hope this helps streamline your follow up process. If you have suggestions for other features you want added to the Follow Ups series, or ideas for new series or databases you want to see, let me know by leaving a comment. I regularly review your suggestions and plan future lessons accordingly.
We will be covering a few more advanced topics related to follow ups, such as handling follow up dates that skip weekends or non-workdays, in part six of the series.
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
Creating an Email Template table for follow up emails
Designing table fields for email templates
Populating sample email templates in the table
Adding a combo box to the follow ups form for selecting templates
Configuring the combo box to display template descriptions
Adjusting combo box widths and properties for a clean UI
Renaming the combo box for clarity in VBA
Using DLookUp to retrieve template subject and body in VBA
Using Nz to handle possible null values in DLookUp
Checking for a valid email address before sending
Building a mailto hyperlink with subject and body parameters
Using FollowHyperlink in VBA to launch the default email client
Concatenating strings for email parameters in VBA
Demonstrating and testing the email send functionality
|
