Mail Merge from Access

[Curt]

Is it possible to send mail merge forms from Access? I
have a word document that is merged from a query
qryNoShow, but I would like to select a person who no
showed from my form frmNoShow then have the email open
with the document. Now I have the query and the form with
email all working properly. Instead of creating a report
I would rather have the document used in case the user
needs to make some changes to it. How do I go about doing
this?

Thanks any help will be much appreciated...

 

[Byron]

The short answer to your question is, "Yes". Take a look
at http://www.mvps.org/access/modules/mdl0019.htm
Dev Ashish has some great help for many things you want
to do.

HTH.

Byron

 

[Curt]

Thanks for the reponse, but I have already looked at that
and it is way too complicated for me to comprehend. What
I am trying to do is select from a listbox on a form which
shows all of the people who noshowed for a class. When I
select them I want the email to open with the word
document and have it match to the individual I have
selected. I have the word document merged already, but I
need them to match. How do I use the example in this
situation? I really do need some assistance on this today
if possible. It is crunch time and this is pretty much
the last aspect of my project.


Thanks to all for any assistance.

 

[Albert D. Kallal]

Why don't you try using filter by form?

Filter by form is a great way to filter the records.

If you want some example mail merge that is easy to implement, then give my
example a try. Just give the sample I have a try (it has data..and to try
the example all your do is click on the merge button).

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

 

[Curt]

Thanks Albert I will take a look. I tried the other
method and integrated it into my form, but it didn't work.
It didn't open the email and send the attachment. I even
installed the correct reference it requested. Oh well I
will give your method a try and see what happens. I will
contact you if it all fails.

Again thanks again.

 

[Byron]

I think I misunderstood what you were wanting to do. If
you simply want to take the information from a selected
record and have that information placed into a Word
document in the specified places, the best way is to
create your document with Bookmarks in the desired
places. Then use VBA code on the after update of your
combo box or other control like the code below. It is
code that I used from the OnClick event a button
named "Command30" (not an appropriate name) but the code
will work. You will need to make some modifications to
fit your needs.

Here is the code:
'*******

Private Sub Command30_Click()
Dim fName, lName, isName As String
fName = Me.FisrtName
lName = Me.LastName
isName = fName & " " & lName
On Error GoTo Mergebutton_Err
Dim objWord As Word.Application

Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible
.Visible = True
' Open the document
.Documents.Open ("Path to your document\Thank You
Letter-Auto.doc")
' Move to each bookmark and insert text from the
from.
.ActiveDocument.Bookmarks("Company").Select
.Selection.Text = (CStr(Me![CompanyName]))
.ActiveDocument.Bookmarks("Contact").Select
.Selection.Text = (CStr(isName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Me![Address]))
'.ActiveDocument.Bookmarks("Address2").Select
'.Selection.Text = (CStr(Me![Mail-Address2]))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Me![City]))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Me![State]))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Me![ZipCode]))
.ActiveDocument.Bookmarks("Greeting").Select
.Selection.Text = (CStr(fName))
End With
' Print the document in the foreground
'objWord.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes
'objWord.ActiveDocument.Close
SaveChanges:=wdDoNotSaveChanges
' Quit Word and release object variable
'objWord.Quit
Set objWord = Nothing
Exit Sub
Mergebutton_Err:
' If a field on the from is empty
' remove the bookmark text and continue.
If Err.number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.number & vbCr & Err.Description
End If
Exit Sub
End Sub

'******* End of Code:

HTH

Byron

If you need to contact me directly, use "mrbpolk@cox-
internet.com"

 

[Curt]

Thanks I will give it a try.

-----Original Message-----
I think I misunderstood what you were wanting to do. If
you simply want to take the information from a selected
record and have that information placed into a Word
document in the specified places, the best way is to
create your document with Bookmarks in the desired
places. Then use VBA code on the after update of your
combo box or other control like the code below. It is
code that I used from the OnClick event a button
named "Command30" (not an appropriate name) but the code
will work. You will need to make some modifications to
fit your needs.

Here is the code:
'*******

Private Sub Command30_Click()
Dim fName, lName, isName As String
fName = Me.FisrtName
lName = Me.LastName
isName = fName & " " & lName
On Error GoTo Mergebutton_Err
Dim objWord As Word.Application

Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible
.Visible = True
' Open the document
.Documents.Open ("Path to your document\Thank You
Letter-Auto.doc")
' Move to each bookmark and insert text from the
from.
.ActiveDocument.Bookmarks("Company").Select
.Selection.Text = (CStr(Me![CompanyName]))
.ActiveDocument.Bookmarks("Contact").Select
.Selection.Text = (CStr(isName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Me![Address]))
'.ActiveDocument.Bookmarks("Address2").Select
'.Selection.Text = (CStr(Me![Mail-Address2]))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Me![City]))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Me![State]))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Me![ZipCode]))
.ActiveDocument.Bookmarks("Greeting").Select
.Selection.Text = (CStr(fName))
End With
' Print the document in the foreground
'objWord.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes
'objWord.ActiveDocument.Close
SaveChanges:=wdDoNotSaveChanges
' Quit Word and release object variable
'objWord.Quit
Set objWord = Nothing
Exit Sub
Mergebutton_Err:
' If a field on the from is empty
' remove the bookmark text and continue.
If Err.number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.number & vbCr & Err.Description
End If
Exit Sub
End Sub

'******* End of Code:

HTH

Byron

If you need to contact me directly, use "mrbpolk@cox-
internet.com"

-----Original Message-----
Thanks for the reponse, but I have already looked at that
and it is way too complicated for me to comprehend. What
I am trying to do is select from a listbox on a form which
shows all of the people who noshowed for a class. When I
select them I want the email to open with the word
document and have it match to the individual I have
selected. I have the word document merged already, but I
need them to match. How do I use the example in this
situation? I really do need some assistance on this today
if possible. It is crunch time and this is pretty much
the last aspect of my project.


Thanks to all for any assistance.
.

.

 

[Curt]

I tried messing around with your code you suggested. To
me it looks Like it would work. However, I keep getting
recongition errors.

For instance Dim objWord As Word.Application I had to
change it to Dim objWord As Application before it would
accept it. Now since I did this it doesn't accept any of
the other code that starts with a .

any suggestions?

 

[Albert D. Kallal]

My code example does not requite you try and use, learn, and work with those
difficult book marks

My code example does not require you to hard code the field names. That mean
you can word enable any form you have with one button, and you don't have to
mess around with a bunch of code that has field names hard coded. You only
need one line of code.

Since my code does NOT use the difficult and clumsy bookmarks, then you, or
just about any of your users can make a word template. When they make that
template, the standard training, courses, and books they have read on
inserting a word merge field works the same with my example. Further, that
means not only don't you have to write a bunch of code for each form (where
you type in the field names), but, then when you make the word merge
document..you don't have to remember the bookmark names, nor remember the
field names..since you get to use the standard word merge dialog..and select
the field from a nice drop down list.

So, less work, less memorizing of fields, no typing of fields (you select
from a drop down list), No hard coding of a bunch of code each time (my
routines pick up the field names automatically).

Instead of messing around with a bunch of hard to use book marks and writing
some code..why not give my example a try. You can word enable any form you
have..and only ONE line of code is required for that form.

Even more import is my code uses late binding..and the example given to you
does not. This means if the same version of word, or if word does NOT exist
on the target pc..the rest of your code will not run.

There is some cases when book marks are ok..but most of the time they are
more work, more confusing, and more prone to error as they are hard to work
with in a document.

If you exhaushed all other possbile soltions (like my example)..then
sure..go with book marks...

 

[Curt]

Albert I agree that your program is very easy to use. I
created a form and copied my information from the other
document to this form and saved it as a Template.
However, for some reason when I click on the OK - merge to
Word on the Form GuiWordTemplate it only allows me to
select items from your contacts form and gives me invalid
merge fields. Also, I placed MergeSingleWord on my form,
but that does not seem to do anything as of yet. Any
Suggestions on what I am doing wrong?
I do like the program now if I can only get to work for my
needs.

Thanks,

 

[Curt]

Albert, Ok I placed it on a different form with textboxes
and it seems to work. However, in this instance I have a
listbox that displays the person's name and other
information. My situation is I want to pick a row then
select email button, have the document show up in the
email with the appropriate person's info. Here is my code
maybe if you can show me where and how this can be
accomplished. I would really appreciate it.
Private Sub Email_Click()
On Error GoTo Err_Email_Click
Dim dbs As Database
Dim rst As Recordset
Dim strWhere As String
Dim vReason As Variant
Dim ctl As Control
Dim varItm As Variant
Dim strTo As String
Set ctl = Me.NoShow
If ctl.ListIndex < 0 Then
MsgBox "Select a Student first",
vbCritical, "SELECT AN INDIVIDUAL"
Else
For Each varItm In ctl.ItemsSelected
If MsgBox("Do you want to send an E-Mail Notification?" &
Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Continue?",
20, "E-Mail Notification") = vbYes Then
If ctl.Column(2) = "*FIP*" Or ctl.Column(2) = "*HLW*" Or
ctl.Column(2) = "*BCIP*" Then
'Send form that relates to this requirement
MergeSingleWord
' DoCmd.SendObject , , acFormatRTF, strTo, , , , , True
Else
' Send other form
MergeSingleWord
End If
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblStudentsAndClasses")
vReason = ctl.Column(5)
strWhere = "StudentClassID =" & vReason
rst.FindFirst strWhere
If Not rst.NoMatch Then
rst.Edit
rst![Emailed] = "-1"
rst![EDate] = Date
rst.Update
rst.Close
Else
End If
ctl.Requery
Else: DoCmd.CancelEvent
End If
Next varItm
End If

Exit_Email_Click:
Exit Sub
Err_Email_Click:
MsgBox Err.Description
Resume Exit_Email_Click

End Sub

 

[Albert D. Kallal]

I don't think a listbox is a very good UI choice here.

Further, it not clear if you want the user to select a bunch of names..then
whack merge..and have all the documents made, or you want to do one at a
time?

If you want to do one at a time..then why bother with a multi-select? Just
have the user navigate to the name on a form how they do now (I sure you
have
provided a nice way to search and bring up a particular student to edit as
this
would be perhaps the most common task. In other words, to do anything
with a student..you have to search, and then view that student in a form
anyway. Once you train your users to search, and find a
student...then a simple merge button on the form to complete the task would
make the most sense, and requite less training to your users.

Here is some ideas on searching:
http://www.attcanada.net/~kallal.msn/Search/index.html

The way you have your listbox select code now is you let the user select a
bunch of names..and the for each on you prompt the user if you want to make
a word doc. I see that you also need to prompt for the "reason".

If the user clicks on 10 names..and then after a few letters..the user
might have the phone ring..and need to lookup something else. Now, how is
the
user to get out of your prompt loop and do something else? How will they
re-start the task back into the middle of the list? What happens if the
word doc freezes up. There is many things that can go wrong here
and your users will need some means to work through these problems

I think that UI approach is going to cause some frustration. Further, right
after the msgbox is answered...the code will loop around right around back
to that msgbox.
(it will NOT wait for the rest of the code).

Even more important here is that you are also trying to email the word
document also.

So, you are using quite a few technologies here. You are not just perform a
simple
word merge..but also then want to be able to email this document. So, you
are
throwing a lot of complexity to the user all at once. Again, for this
reason, I
would probably break this down in to doing this one at a time, or all at a
time WITHOUT any user prompts. In other words, go and edit
each student, set the reasons, and perhaps have a check box to
send a email to. Then make a process that runs the whole thing.

Or, go to each student, and have a email button..but do it one
at a time. But some prompts right in the middle of a
process is not very workable.

Further, to use sendobject, you can't use a word merge. Send object will
allow you to send a report to rtf, file and then have it attached to a
email. But you can't accomplish a word merge..and THEN attach the word doc
using sendobject.

So, my word merge will create the word document for you..but it will not
attached it to a email. There are two reasonable solutions here:

1) use a report that looks like the letter, and then you can use sendoubect.

2) Use my code to make the word doc, then use Microsoft Outlook..and NOT
outlook express. If you do that..then you can create the word document...and
then attach this document as a email. Again, it is not clear if the word
document needs to be edited in anyway before it gets attached as email. And,
also, again it is not clear if the email text needs editing here also.

So, are you trying to make a word merge....and attached as email...or is the
word merge just good enough here?

 

[Curt]

What I am trying to do is select an individual from any
sort of way. I like the idea you suggested. Have an email
button on the student form which shows all their
information and if they attended class or not. The reason
I was using the form with the listbox was that it would at
one shot show all the people who didn't attend a mandatory
class.
The listbox is actually not multiselect. Do you suggest
that have user select the row have it open the students
form then send an email from there? If so how would it
compare to the class other in a listbox?

After selecting the individual I want the document to
update with the information and then have Outlook open up
with the attached document with students information on
it. The more I can automate would make the user much more
happier and program would be easier to use. Is what I am
suggesting possible or realistic?
I thought about a report, but like I said the user may
want to make changes at a later date and I might not be
around to help them out on this.

thanks for the replies

 

[Albert D. Kallal]

Ah, good.

Remember...I was little bit confused...because at first I did not realize
that in addition to making the word merge...you also wanted to email the
document.

What I am trying to do is select an individual from any
sort of way. I like the idea you suggested. Have an email
button on the student form which shows all their
information and if they attended class or not. The reason
I was using the form with the listbox was that it would at
one shot show all the people who didn't attend a mandatory
class.

The above sounds just fine. And remember, if you did NOT have to have a
prompt for the "reason", then I am 100% on the idea of selecting a whole
bunch of names..and hitting some button and walking away for coffee.
However, since we do need "some" user intervention for each email (the
reason prompt etc)..then that is why I strongly was against the "loop".

Regardless...as you say...any way that works...is the idea here.

So, there is only one issue left here:

If you want to use a word merge..and then attach it..you have to use
Outlook...and NOT outlook express.

If you want to use OE...then you will have to make a report that looks like
the letter...and send that using sendojbect.

The listbox is actually not multiselect. Do you suggest
that have user select the row have it open the students
form then send an email from there? If so how would it
compare to the class other in a listbox?

Hum, yea..I kind of like the above idea.

After selecting the individual I want the document to
update with the information and then have Outlook open up
with the attached document with students information on
it. The more I can automate would make the user much more
happier and program would be easier to use. Is what I am
suggesting possible or realistic?

You are 100% on the money here. I mean, these computers are supposed to do
the work....right!!

I thought about a report, but like I said the user may
want to make changes at a later date and I might not be
around to help them out on this.

Ok...given that we are going to use Outlook...and NOT outlook express. We
have:

We will display the student form with a email button. (how you jump to that
student form..as mentioned is kind of still open for ideas. You hinted at a
listbox...or even perhaps a combo box...once the student is found...we
display the main student edit form).

Steps are now:

We whack are send-doc button.

We create the word doc...merge the fields

Launch Outlook, attach the word doc, and insert the email address

At this point, the user can double click on the attached word document..and
edit it if MORE changes are needed.

So, I would at first get the word merge button working. Get a few templates
working. You can then launch the word template, and then save it. You then
have to start working on the Outlook automation part.

There's some ideas and samples for email stuff at:

http://www.slipstick.com/addins/mail.htm#massmail