Perhaps the most popular article on the site explains how to send email to a bunch of people using Access and Outlook.
It has garnered its fair share of comments and emails, and one came in today that I figured I'd share and then elaborate on.
The mail reads (in part):
I have a following question: How to modify this module to be able to send messages to various mailing lists that I predefine in respective queries? In other words, I have in my database 3 categories of customers (in 3 different queries) andI want to address them with a different message. Do I need to create 3 macros running 3 modules each referring to a separate query with a given category of customers or is there another way to do it?
You don't have to create modules for each list, you just need to be able to tell the macro which query you want to use before running it.
There's a few different ways to do it:
- Edit the code to reflect the list each time you want to run the macro
- Have the code ask you which query you want to use
- Set up different macros to do the work for you.
Let's take a look at the various ways...
1. Edit the code
The first one is pretty self-explanatory, so I won't elaborate much. Just change the line:
qryName = InputBox$("Please enter the name of the query you want to use to get the addresses", "Which Addresses?")
' If there€™s no one to write to, call it a day.
If qryName$ = "" Then
MsgBox "Without addresses, we can't send a message." & vbNewLine & vbNewLine & "Quitting...", vbCritical, "No one to send to..."
Set MailList = db.OpenRecordset(qryName)
NOTICE that there are NO QUOTES around qryName like we had around "MyEMailAddresses" -- since qryName is a variable and will be replaced by whatever we typed in the box, it doesn't get quotes around it.
Also note, that we're not checking to make sure the query is correct, we trust you to get it right and not make a typo.
Lastly, we need to ensure that the queries we're using to seed this thing have the same fields as the query we're replacing, otherwise errors will be thrown... to clarify, if your new query has a field named "E-Mail" and we're looking for a field called "Email" -- the routine will fail.
3. Use a macro to fill the query in for you
This is the coolest way to do it, since you only have to make the macro once, and then you can run it as many times as you want with no more effort than a double-click.
We need a way to tell the Function that we want to use a specific query. The way we do this is by using an ARGUMENT.
The first line of our function looks like this:
Public Function SendEMail()
The parentheses at the end are where we put the arguments. In our initial example, we didn't need any arguments, so the parens were empty... but now we want to use an argument. An argument is nothing more than a variable that is filled in before hand. In Option 2, we typed the queryname in every time we ran the macro. We're going to change the irst line of our function AND we're going to change the line that assigns the query like so:
Public Function SendEMail(qryName as String)
See what we did there? We took the variable declaration from the 2nd option (Dim qryName as String), and put in the argument field instead.
Now, in the meat of the routine, we need to change one line from:
Set MailList = db.OpenRecordset("MyEmailAddresses")
Set MailList = db.OpenRecordset(qryName)
(Look a little familiar?)
So, now we have our function edited, but where do we put the query name we want to use? In the macro.
Running the Macro
If we run the macro we wrote in the first article, we're going to get an error since it doesn't provide an argument.
So, we need to edit the macro.
Go into the macro definition, and it looks like this:
And where it says "=SendEmail()" is where we're going to make our edit.
Since we want this macro to work like it always did, we need to use query we wrote for the original article, which is calledMyEMailAddresses.
We need to update the function name to =SendEmail("MyEmailAddresses") like so:
If you wanted, you could also use the Expression Builder by clicking the button with 3 dots on it to pick your function name. This is what that looks like:
So you can see that the function (SendEmail) is now asking for an argument (the bit in the parentheses) called qryName.
Now, if you save the macro and run it, the macro should run like always.
To make a different macro for each of your queries, just copy the macro we made...
... and paste it with a new name:
And that's all there is to it.
Any time you need to send to a new list, write a new query, copy, paste then edit the macro and you're off to the races.