Can We Run a Word Email Merge from Access?

[Guest]

Hello:

Our goal in our little Training Team is to click a button on a One to Many
Form and either:
a. Do a Word Email Merge sending an email to every student registered in the
class
or
b. Do a Word Email Merge sending an email to one student showing every class
where he/she is registered.

I created the Access Query and the Word Email Merge and can run them
manually. From Access, I click on a form button to run the query. I then
manually open Word to the saved Email Merge letter and send the email to
every student using Word’s Mail Merge functionality. Everything works
perfectly when doing it manually.

My question is:
Can I write code that will run the Access Query from the form and then
execute the Word Email Merge directly from Access? In other words, the only
thing the user would have to do is click on a command button on the form.

Note:
Here's name of the Word File we're using in test mode and the query that we
run on the form.

MailMerge_Test_OneClass.doc

Private Sub cmdRunTestQueryForEmail_Click()
On Error GoTo Err_cmdRunTestQueryForEmail_Click

Dim vClassNo As String
Dim stDocName As String


vClassNo = Me.ClassNo
stDocName = "qryTestEmail_OneClass"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdRunTestQueryForEmail_Click:
Exit Sub

Err_cmdRunTestQueryForEmail_Click:
MsgBox Err.Description
Resume Exit_cmdRunTestQueryForEmail_Click

End Sub

Thanks,
Robert

 

[Guest]

Whoooooooooooooooops......... Forgot to mention we're using Office 2003.

Robert

 

[Allen Browne]

Albert Kallal has a "Super Easy Word Merge" solution on this page:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

You import his forms and modules, and then call it with a single line of
code.

 

[Guest]

Hello Allen:

I'm a little slow and somehwat confused by this process, hence it isn't
very clear to me.

You wrote,

[Import his forms and modules and then call it with a single line of code.]

I have at least two questions that immediately come to mind .

[1] Call "it"............ What is it?

[2] Why do I need his forms? Can I run whatever it is above from the form
we're currently using?

Thanks,
Robert

 

[Allen Browne]

The download provides everything you need to do the merge.
Although I haven't used this particular one, I know Albert's stuff is good.

Apologies for the "g33ksp34k".
When I said "call it", I meant use it/run it/excute it/make it go.

 

[Guest]

Allen:

I really appreciate your help but I have no idea how to apply Albert's stuff
to
[1] run a query on the current record and then
[2] execute a mail merge

I must be missing something completely obvious to everyone else. My button
already runs the query, I just need to figure out how to run the Mail Merge.

Thanks,
Robert

 

[Allen Browne]

Robert, if you are not able to get Albert's utility to go, you might be able
to work around the whole thing by creating a report in Access that does what
you need.

The major limitation of that approach is that, unless you are using the
latest version (Access 2007), you cannot format part of a text box
differently than the rest (e.g. undescoring a word in a sentence.) Provided
you can live with that, you can create your letter as a report in Access,
and not worry about Word.

Typically the report might have a CustomerID group header where you put the
address panel, followed by a Detail section where you list the items (such
as invoice items) or paragraphs in the letter, followed by a CustomerID
group footer where the closing signature goes.

 

[Guest]

Hello Allen:

I thought about doing a report in Access, but that won't work. My co-worker
needs to send out email confirmations to students who register for the
classes we teach. Word has a nifty Word Email Merge feature that makes it
very easy to do such. So in the end, Word Email Merge is the only viable
option we have.

I'm still working on it and trying to figure out how to do this. I just
don't understand why this is so difficult. One would think the Access
development team should have figured out how to essentially automate this
process for Access users. After all, the functionality is built into Office
2003 and I can do it manually. My co-worker asked if I could automate the
process, and with lots of confidence, I told him yes. Now I have to stand
behind that statement.

Thanks for all of your help so far.
Robert

 

[Allen Browne]

If you must do it via Word, Albert's is the easiest solution you will find.

I still don't follow why it can't be done in Access, where reports are
specifically designed the merge the data from your query. I do that kind of
thing in most of the databases I create, and some of those are merging
hundreds of letters each day.

 

[Guest]

Hello Allen:

Yes Allen, you can definitely create the merge in Access. However, Word has
the ability to create an EMAIL merge that utilizes the student's email
address to EMAIL the document to each student via Outlook 2003. To the best
of my knowledge, we cannot do that with Access 2003. Is that correct?

If there is a way to send an email to each student using the Access Report
module, that would definitely be the best option.

Robert


[I still don't follow why it can't be done in Access, where reports are
specifically designed the merge the data from your query. I do that kind of
thing in most of the databases I create, and some of those are merging
hundreds of letters each day]

 

[Allen Browne]

Messy: SendObject can send emails, but you need to apply a filter string in
Report_Open.

You can use the Outlook library from in Access too.

Other alternatives:
http://www.granite.ab.ca/access/email.htm

 

[Guest]

Hi Robert,

I am doing the same thing and I hope this will help.

What I have done is created a parameter query that includes all the fields I
need.

When you set up your mail merge, select the query as your data source.

On a new form, insert an unbound text box with your parameter and a command
button that has the following code:

Application.FollowHyperlink "[Your document here with complete file path]"

So when you choose your criteria and press the button it will take you
straight to your mail merge document and then you can merge the information
in the query.