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:
Set MailList = db.OpenRecordset("MyEmailAddresses")
to
Set MailList = db.OpenRecordset("TheQueryYouWantToUseInstead")
Simple enough.
2. Ask Each Time
The second way is just as much as a pain as the first, because you still have to type the query name each time.
To implement that, we’re going to change a line and add a few more. The few more should look familiar, since they’re the same ones we used to ask about the File to attach and whatnot…
Set MailList = db.OpenRecordset("MyEmailAddresses")
will get changed to
Dim qryName as String
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..."
Exit Function
End If
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")
to
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.
Hello,
thanks for posting it!
However, I have found a fourth way to do it, which from a programmer’s point of view might be messy/incorrect, but works just fine and is automatic, in a sense that an end user does not need to know the name of the queries, but can simply click a button on a form and is prompted to insert subject and text file with content, which send it directly to a predefined group.
This can be done by means of creating separate macros running separate modules which are just a copy of the module you originally designed for sending an email to multiple recipients. By creating separate macros and separate modules (extracting data from separate predefined queries grouping Email addresses of a given category I want to send my mail to) enables me to assign a button that executes each macro separately.
The advantage is that at the end the user is not asked to type in a query name (this is what options 2 and 3 in the above manual do), and thus, it seems more suitable and comfortable for non-experienced end-users.
What do you think of that? I suppose one can programme it in a neater way, but this is a simplistic solution I came up with.
That’s a perfectly viable way to do things, but programmers don’t like to have to maintain code in 4 different places if one will suffice.
That why the macro works so well. the programmer writes the code once, sets up each of the macros once, and then lets the user choose the appropriate macro.
Of course, this is totally extensible once you start programming forms and stuff — the article didn’t really go into that depth. Forms programming is a whole other arena. 🙂
That said, there’d be no reason why you couldn’t create 4 arguments to your function:
Function SendEmail(qryName, sSubjectLine as String, sBodyFile as String, sBody as String)
Them from your form, pass in the appropriate values.
You would add a little more logic to the Function so it would check that if sBody has any text in it, it should ignore the sBodyFile (or append the sBodyFile or whatever) and use that instead for the body of the email…
The possibilities are endless. 🙂
Many thanks, it is very helpful, especially that now I get how to use multiple arguments to a Function.
However, still the solution you proposed does not evade the query name to be input (wrote-in) manually by the user, which makes it error-prone 😛
Cheers mate and keep up the good work!
Romek
The end-user would never be asked for a queryname in my preferred scenarios — it would be handled by the initial programmer who would have done his error checking before rolling it out to his users.
Bear in mind, none of this production level code — there’s virtually no error checking and whatnot…
Can I fill in Word document from Access table and/or query ?
Sure.
Here’s a good tutorial on the subject
http://word.mvps.org/faqs/MailMerge/CreateAMailMerge.htm
Basically, you’ll set up a Word merge document like any other, and when it comes to choosing a Data Source, you’ll use your Access DB as the source as opposed to another Word document of Excel spreadsheet.
You can use a query within your database as the source, so as you update the database, you can re-run your merge with the updated data and not have to change anything else.
Jeff,
Based on this post as well as an older post, I’ve been able to create almost everything I need in order to send some mass mailings out at my job (many thanks!). However, one question still jumps out at me – since I’m sending one email to many people, I have to use the recipients coding…is there *anyway* to automate getting the emails into the BCC field instead?
Much appreciated!
matt
Jeff,
I’m using the original version of this code to assemble and send e-mails regarding delinquent timecards. The code is sending messages to both the employee and their supervisor and it works great (Thanks very much, by the way!). But I do have one small problem I can’t seem to get past. It is possible that a given employee will have more than one delinquent timecard and it is typical that a supervisor will have more than one employee who is delinquent. As it stands, the code sends one message for each delinquency. What I would like to do is get it to consolidate those and only send one message that lists all of the delinquencies that person is responsible for. Is there any way to select multiple records into the body of the message to create a list? I hate to ask since you’ve done so much already but I’m stuck and can’t figure it out….
Don,
Depending on how you have your query set up, you can use a second query master loop to test populate the delinquent timesheet info.
For instance, say your query returns SupervisorID, EmployeeID, TimecardID (and whatever related info to that) I would say make sure you’re sorting on SupervisorID and EmployeeID, so all your EmployeeIDs are grouped together. The result might be something like this:
1, 2, 3
1, 2, 4
2, 3, 5
2, 4, 6
etc.
How things are now, Supervisor 1 is getting two emails about Employee 2 — one for Timesheet 3 and one for Timesheet 4.
Instead of running multiplie loops (which is a fine way of doing it, but introduces pitfalls pertaining to do what to do when the recordset ends, how to keep track of where you are in the recordset, etc.), I would run a 2nd query.
So, add this code (the query obviously needs to be changed to suit your particular needs):
‘ – – – Begin Code – – –
MyBodyText = “Here are the delinquent timesheets for Employee ” & MyMail(“EmployeeID”)
Dim MyQuery2 as Recordset
Dim MyBodyText2 as String
Set MyQuery2 = Currentdb.openrecordset(“Select TimesheetID, TimesheetInfo from DelinquentTimesheetTable where SupervisorID = ” & MyMail.SupervisorID & ” and EmployeeID = ” & MyMail.EmployeeID)
Do until MyQuery2.EOF
MyBodyText2 = MyQuery2(“TimesheetInfo”) & vbnewline
MyQuery2.movenext
Loop
MyBodyText = MyBodyText & vbnewline & MyBodyText2
‘ – – – End Code – – –
So that runs a second query to get the timesheet info, cycles thru it, and appends the info to mybodytext2. When that’s done, it appends MyBodyText2 to your initial MyBodyText and then you can continue running the code as usual.
Hope this helps.
Hi Jeff,
Great site and I have been looking at your email code. I thought this was a perennial problem, but nobody has asked you yet so here goes:
Can I use this code (or any) to change the ‘From’ address.
I send an email to the buyer of my products, and a similar email to the despatcher telling them what to send.
I want one to have my professional shop address, and one to have my ‘phil’ address.
Thanks,
Phil
Phil,
Outlook 2007 adds a MailItem.SendUsingAccount property, and in 2003 you can use some semi-ugly hack/kludge to do the job providing you meet some criteria (not using Word as your editor, etc.)
You can find that code here — I’ve not tried or tested it, nor do I have any idea how to integrate into my routines:
http://www.outlookcode.com/codedetail.aspx?id=889
Jeff
Hi Jeff,
I just finished pasting 240 separate emails into 240 Outlook email messages (I created these 240 blank email messages by creating one master message, saving it as a DRAFT, and then copying it to get 240 messages). Then I googled & found your site ;-(
I would like to know if there is a way to use code to create the 240 copies and populate the TO: email address, leaving me with the task of only pushing the SEND button for each of the messages that is open. I think that this would be faster than using code to press the SEND button as there is no built-in 305 second security delay to wait upon.
TIA!
Joe
Northville, MI
Simple, change the one line of code from “MyMail.Send” to “MyMail.Display”
The code is in there, it is just commented out. Move the apostrophe from the .Display line to the .Send line and you’ll get your 240 open messages – assuming Windows’ GDI layer doesn’t crap out first. 🙂
You might also want to have a look at this:
http://www.mapilab.com/outlook/security/
Hi Jeff,
Great site – exactly what I needed.
One quick request – I changed the code some to either pre-populate text into the email body or leave it blank for the user to edit themselves when the email is displayed depending on different conditions.
When I don’t pre-populate the text, the email is displayed and the email body only contains the user’s default Outlook email signature. I would like to include their default Outlook email signature at the end of the pre-populated text as well.
How can this be accomplished?
Thanks,
Dan
Dan,
Outlook only puts the signature into a blank message.
However, the signature is just a text (or HTML) file, but you have to find it. It’s fairly well hidden in the registry, in binary fields to boot, but here’s a decent solution that seems to work.
http://www.rondebruin.nl/mail/folder3/signature.htm
Jeff, I was using your earlier code to send an email from access:
Do Until MailList.EOF
‘ This adds the address to the list of recipients
MyMail.Recipients.Add MailList(“email”)
‘And on to the next one…
MailList.MoveNext
Loop
This defaults to putting the addresses in the To line, how can I put them in the BCC line?
Thanks,
Mike
VBA’s Recipients.add collection only allows you pass in the email address; however, there’s a rich object model to the recipient object, we just need to make it available to us. Happily, Access gives us that object when we assign it to the message… in our old code, we didn’t need the object, so we ignored it — we just ADDed the email address and got on with our lives… but we could, if we wanted, say “Hey, Access — if we assign an email address, can you point us to that address we just created?” And it can, so everyone wins.
So, first, we need to establish the variable that is going to hold the RECIPIENT object we added to the message. Add this piece of code that DIMs the Recipient object where we DIM our other variables:
Dim MyRecip as Outlook.Recipient
Then, inside the loop, instead of using
MyMail.Recipients.Add MailList(“email”)
Use:
Set MyRecip = MyMail.Recipients.Add(maillist(“email”))
MyRecip.Type = olBCC ‘ sets the latest recipient as BCC
At the end, for good measure, we should clean up after ourselves:
Set MyRecip = Nothing
I may have missed it, but I need to go to a text file, fatch each line and send it in the body of a email and send it, then go to the next line in the text file and so on – can you please help, Thanks!
I am using Office 2007!