UserForm/MailMerge

[AJ]

I am needing to create a document. What we would like for it to do is take
some information from a table in Access and pre populate some of the fields
and then the user will just fill in the rest. I have created Mail merges
before with the use of Access but I have not had to have the flexability of
the user being able to enter information into the document. I am confused
about how to go about this. Whether a user form, but then I can't mail merge
and pre fill from Access, can I? Or mail merge which I don't know if I can
make some of the fields into data entry fields. Any ideas or directions would
be appreciated very much.

 

[Doug Robbins - Word MVP]

Are you wanting to create multiple documents from multiple records in the
datasource, or a single document from one record in the datasource which you
would want to be able to select and then in both cases, have other fields
that you want the user to be able to populate?

You might look at:

The following pages of fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Or, take a look at the information referred to in the following post from
fellow MVP, Albert Kallal.

Quote

Actually, use my merge sample. it don't give that warning, and you don't
have to change registiry stuff etc.

The sample I have can be found here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease.

Make sure you read the instructions from above, and you should eventually
get to the follwoing page
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html


Note that the merge can also use a query, and thus you don't have to merge
just "one" record..

After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).

Give the above a try.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
Unquote


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[AJ]

Yes, we are wanting to create multiple documents from the records held in a
table in Access. I ran across a post you had recommended for another user and
I think it is what would work for me, I am just a little lost on the coding
part:
"...I would create a template in which I would have {DOCVARIABLE fieldname}
fields in place of the {MERGEFEILD fieldname} fields. Then I would create a
macro that would access the datasource and iterate through the records, for
each record, creating a new document from the template and creating Document
Variable fields in that document that contained the data from the current
record and then updated the fields in the new document so that the data that
was stored in those variable appeared in the {DOCVARIABLE} fields."
I believe this is the direction I need but I am stuck. I have created a form
and placed DocVariable's where I need them to be populated and also in the
fields the user will populate ( I have also created DocVariables for them).
That is where I find myself stuck. I am needing direction on the creating the
macro and or coding that will iterate through the records...

 

[Doug Robbins - Word MVP]

Before we go down that route, what sort of information do you want the user
to add to each document produced by the merge?

Have you considered the use of Fillin fields which can be used for the
purpose of having the user provide information to supplement that from each
individual record in the data source?

Or, is what you are wanting to do, create a series of documents each one
with data from a record in the datasource which are then to be issued to
others for them to supply additional information to complete the document?
That is, it is not the person executing the merge who is going to be
providing the additional information.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[AJ]

The second case, we will be doing the merge at the office and then email
documents all over the state, then field officers will fill in data and
return to us then we will import into database. We do not have an open
database to our field officers. They need some info (what we merge) to go out
to do inspections then they are going to be sending back their findings and
we will update database.

 

[Doug Robbins - Word MVP]

If the formfields in the main document are only of the TextInput type and
there are no On Entry/On Exit macros associated with any of the formfields,
then you, can use the following macro to execute the merge to a new document
in which the formfields will be reinstated (albeit without their bookmark
names, which is of probably no consequence in this situation)

Sub MergewithFormFields()
Dim i As Long
With ActiveDocument
For i = .FormFields.Count To 1 Step -1
If .FormFields(i).Type = wdFieldFormTextInput Then
.FormFields(i).Range.Text = "FF" & i
End If
Next i
With .MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
End With
Selection.HomeKey wdStory
With Selection.Find
Do While .Execute(FindText:="FF[0-9]{1,}", Forward:=True, _
MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=True) = True
ActiveDocument.FormFields.Add Selection.Range, wdFieldFormTextInput
Loop
End With
End Sub

You will however then end up with a single document which you would then
need to split into the individual documents that you want to send out to
your field offices.

For that, see the "Individual Merge Letters" item on fellow MVP Graham Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

Alternatively, if you run the following macro when the mail merge main
document is the active document, it will convert each MERGEFIELD in the
document to a DOCVARIABLE field and then it will create a new document for
each record in the datasource in each of which the values of the document
variables will be set to the corresponding data from the data source:

Sub MergewithFormFields()

Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values
'of the fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value <> "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
ActiveDocument.Fields.Update
ActiveDocument.SaveAs "C:\Test\MwithFF" & j
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

With the above code as written, each documnet is saved in a folder C:\Test\
with a file name of the form MwithFF# where # is from 1 to the number of
records in the data source. It is a fairly simple matter to modify the code
so that each document is saved with a name that is taken from a field in the
data source and making use of the information in the following article, if
the email addresses were also present in one of the fields in the
datasource, the code could be further modified to email each document to the
respective email addresses.

See the article "How to send an email from Word using VBA" at:

http://www.word.mvps.org/FAQs/InterDev/SendMail.htm




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[AJ]

Thank you so much for all your time and help. I think this is what I needed.
I will go with it and see what happens. Thank you again for your time and
help.

If the formfields in the main document are only of the TextInput type and
there are no On Entry/On Exit macros associated with any of the formfields,
then you, can use the following macro to execute the merge to a new document
in which the formfields will be reinstated (albeit without their bookmark
names, which is of probably no consequence in this situation)

Sub MergewithFormFields()
Dim i As Long
With ActiveDocument
For i = .FormFields.Count To 1 Step -1
If .FormFields(i).Type = wdFieldFormTextInput Then
.FormFields(i).Range.Text = "FF" & i
End If
Next i
With .MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
End With
Selection.HomeKey wdStory
With Selection.Find
Do While .Execute(FindText:="FF[0-9]{1,}", Forward:=True, _
MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=True) = True
ActiveDocument.FormFields.Add Selection.Range, wdFieldFormTextInput
Loop
End With
End Sub

You will however then end up with a single document which you would then
need to split into the individual documents that you want to send out to
your field offices.

For that, see the "Individual Merge Letters" item on fellow MVP Graham Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

Alternatively, if you run the following macro when the mail merge main
document is the active document, it will convert each MERGEFIELD in the
document to a DOCVARIABLE field and then it will create a new document for
each record in the datasource in each of which the values of the document
variables will be set to the corresponding data from the data source:

Sub MergewithFormFields()

Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values
'of the fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value <> "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
ActiveDocument.Fields.Update
ActiveDocument.SaveAs "C:\Test\MwithFF" & j
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

With the above code as written, each documnet is saved in a folder C:\Test\
with a file name of the form MwithFF# where # is from 1 to the number of
records in the data source. It is a fairly simple matter to modify the code
so that each document is saved with a name that is taken from a field in the
data source and making use of the information in the following article, if
the email addresses were also present in one of the fields in the
datasource, the code could be further modified to email each document to the
respective email addresses.

See the article "How to send an email from Word using VBA" at:

http://www.word.mvps.org/FAQs/InterDev/SendMail.htm




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

The second case, we will be doing the merge at the office and then email
documents all over the state, then field officers will fill in data and
return to us then we will import into database. We do not have an open
database to our field officers. They need some info (what we merge) to go
out
to do inspections then they are going to be sending back their findings
and
we will update database.