Mail Merge, Data Source, and UserForm with ControlButton

[ryguy7272]

Mail Merge, Data Source, and UserForm with ControlButton


In my office, we use Mail Merge to ‘import’ data from an Excel file. This
works fine, using the mail Merge technique, but we are trying to modify the
process so that a user is able to run a macro, search through a list of items
(viewable through a TextBox embedded in a UserForm), and then click a
ControlButton to import all the appropriate information to each MergeField (I
think it would be a standard Mail Merge). It is easy to run through the
items using the standard Mail Merge toolbar, but we are trying to get this to
work through the process described above. It would be so much easier to get
certain ID numbers to show in the TextBox, select the ID that is of interest,
and import all data that pertains to this ID number directly into each
appropriate MergeField.

I am trying to modify some code that Doug Robbins gave me over the summer:

I have the following in the textbox:
***notice, one of the main problems is that I am no longer using a bookmark
named “Addressee†instead, I am trying to use about 30 different
MergeFields. This seems to be the obvious way to go with this, as they seem
to be more intuitive, and give more control over what is imported, at least
in terms of formatting, etc. I’ve named the Excel file “Source Data†and
I’ve named the range of data, and assigned the name “List†to that range. My
main question is how do I import all of this data, after identifying the
appropriate from the TextBox?


Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide
End Sub


Sub UserForm_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Word\Source Data.xls", False, False, "Excel
8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub

Sub Macro2()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Word\ Source Data.xls", ConfirmConversions:=False, ReadOnly _
:=False, LinkToSource:=True, AddToRecentFiles:=False,
PasswordDocument:= _
"", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\Word\Source Data.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engin" _
, SQLStatement:="SELECT * FROM `Source Data`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub


Sub UserFormInitialize()
UserForm1.Show
End Sub


Regards,
Ryan---

Oh, maybe I should mention this, some of the MergeFields are as follows:
«MERGEFIELD "Proj_Name"»
«MERGEFIELD "Product"»
Reference Number: «MERGEFIELD "ID_Number"»
***this ID number is the one of interest. I want to get this to load into
the TextBox, select it, click the ControlButton, and have all data related to
this ID number load into the word document. Any help would be SINCERELY
appreciated.

 

[Doug Robbins - Word MVP]

Instead of using mail merge, in the template, I would insert a Docvariable
field in place of each mergefield and in the command button click event, I
would create variables in the document that would be set to the value from
the corresponding column using the .BoundColumn property of the listbox or
combobox, then finally have the code update the fields in the document so
that the docvariable fields displayed the information.

In the following code, some of the data from the combobox is first
transferred to textbox controls on the userform so that the user can change
it if required. It could however be use to set the value of the document
variables directly as in the case of the company name in the second part of
the routine:

Private Sub cmbCompany_Exit(ByVal Cancel As MSForms.ReturnBoolean)
cmbCompany.BoundColumn = 2
If cmbCompany.Value <> "Others" Then
cmbCompany.BoundColumn = 3
txtstreet = cmbCompany.Value
cmbCompany.BoundColumn = 4
txtCity = cmbCompany.Value
cmbCompany.BoundColumn = 5
TxtState = cmbCompany.Value
cmbCompany.BoundColumn = 6
txtZip = cmbCompany.Value
cmbCompany.BoundColumn = 7
txtCountry = cmbCompany.Value
Else
txtCompany.Visible = True
End If
End Sub

Private Sub cmdContinue_Click()
Dim logrange As Range, logtable As Table
Dim LogNum As Long, rownum As Long
Dim NewDoc As Document
On Error GoTo CATCH

Set NewDoc = ActiveDocument
With NewDoc
cmbCompany.BoundColumn = 1
.Variables("varCompanyCode") = cmbCompany.Value
If txtAddressee = "" Then
.Variables("varAddressee").Value = " "
Else
.Variables("varaddressee").Value = Trim(txtAddressee)
End If
If cmbCompany.Value <> "OT" Then
cmbCompany.BoundColumn = 2
.Variables("varcompany").Value = Trim(cmbCompany.Value)
Else
If txtCompany = "" Then
.Variables("varCompany").Value = " "
Else
.Variables("varCompany").Value = Trim(txtCompany)
End If
End If
If txtstreet = "" Then
.Variables("varaddress").Value = " "
Else
.Variables("varaddress").Value = Trim(txtstreet)
End If
If txtCity = "" Then
.Variables("varcity").Value = " "
Else
.Variables("varcity").Value = Trim(txtCity)
End If
If TxtState = "" Then
.Variables("varstate").Value = " "
Else
.Variables("varstate").Value = Trim(TxtState)
End If
If txtZip = "" Then
.Variables("varzip").Value = " "
Else
.Variables("varzip").Value = Trim(txtZip)
End If
If txtCountry = "" Then
.Variables("varcountry").Value = " "
Else
.Variables("varcountry").Value = Trim(txtCountry)
End If
If txtSubject = "" Then
.Variables("varSubject").Value = " "
Else
.Variables("varsubject").Value = Trim(txtSubject)
End If
If txtSignatory = "" Then
.Variables("varsignatory").Value = " "
Else
.Variables("varsignatory").Value = Trim(txtSignatory)
End If
If txtSalutation = "" Then
.Variables("varSalutation").Value = " "
Else
.Variables("varSalutation").Value = Trim(txtSalutation)
End If
End With


--
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

 

[ryguy7272]

Well, I have to say, I am thrilled to be learning this stuff, but it’s a
little frustrating because I don’t really know what I’m doing here. I added
in all the DocVariables (Insert > Field > DocVariable > NewName > Ok). So
far I feel I’m doing pretty good about this part. Now, I don’t really know
where to put the code that you gave me.

In the Module I have this:
Sub UserFormInitialize()
UserForm1.Show
End Sub

Then, under the CommandButton I have this:
Private Sub CommandButton1_Click()

Dim logrange As Range, logtable As Table
Dim LogNum As Long, rownum As Long
Dim NewDoc As Document
On Error GoTo CATCH

Set NewDoc = ActiveDocument
With NewDoc
cmbCompany.BoundColumn = 1
..Variables("varCompanyCode") = cmbCompany.Value
If txtAddressee = "" Then
..Variables("varAddressee").Value = " "
Else
..Variables("varaddressee").Value = Trim(txtAddressee)
End If
If cmbCompany.Value <> "OT" Then
cmbCompany.BoundColumn = 2
..Variables("varcompany").Value = Trim(cmbCompany.Value)
Else
If txtCompany = "" Then
..Variables("varCompany").Value = " "
Else
..Variables("varCompany").Value = Trim(txtCompany)
End If
End If
If txtstreet = "" Then
..Variables("varaddress").Value = " "
Else
..Variables("varaddress").Value = Trim(txtstreet)
End If
If txtCity = "" Then
..Variables("varcity").Value = " "
Else
..Variables("varcity").Value = Trim(txtCity)
End If
If TxtState = "" Then
..Variables("varstate").Value = " "
Else
..Variables("varstate").Value = Trim(TxtState)
End If
If txtZip = "" Then
..Variables("varzip").Value = " "
Else
..Variables("varzip").Value = Trim(txtZip)
End If
If txtCountry = "" Then
..Variables("varcountry").Value = " "
Else
..Variables("varcountry").Value = Trim(txtCountry)
End If
If txtSubject = "" Then
..Variables("varSubject").Value = " "
Else
..Variables("varsubject").Value = Trim(txtSubject)
End If
If txtSignatory = "" Then
..Variables("varsignatory").Value = " "
Else
..Variables("varsignatory").Value = Trim(txtSignatory)
End If
If txtSalutation = "" Then
..Variables("varSalutation").Value = " "
Else
..Variables("varSalutation").Value = Trim(txtSalutation)
End If
End With

End Sub


Finally, I have this just below the code under the CommandButton (in the
UserForm):
Private Sub cmbCompany_Exit(ByVal Cancel As MSForms.ReturnBoolean)
cmbCompany.BoundColumn = 2
If cmbCompany.Value <> "Others" Then
cmbCompany.BoundColumn = 3
txtstreet = cmbCompany.Value
cmbCompany.BoundColumn = 4
txtCity = cmbCompany.Value
cmbCompany.BoundColumn = 5
TxtState = cmbCompany.Value
cmbCompany.BoundColumn = 6
txtZip = cmbCompany.Value
cmbCompany.BoundColumn = 7
txtCountry = cmbCompany.Value
Else
txtCompany.Visible = True
End If
End Sub


Undoubtedly, the most important Field is this one:
{DOCVARIABLE Number \* MERGEFORMAT}

In the Spreadsheet named “Source Dataâ€, these numbers are in Column M. Do I
need to name the Column or is the code intelligent enough to find “Number†in
Column M? Also, I didn’t reference the name of the Spreadsheet, “Source
Dataâ€, anywhere in the code. I deleted all of this:

Sub UserForm_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Word\Source Data.xls", False, False, "Excel
8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
..MoveLast
NoOfRecords = .RecordCount
..MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub

Sub Macro2()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Word\ Source Data.xls", ConfirmConversions:=False, ReadOnly _
:=False, LinkToSource:=True, AddToRecentFiles:=False,
PasswordDocument:= _
"", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\Word\Source Data.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engin" _
, SQLStatement:="SELECT * FROM `Source Data`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub


I’m not sure how Word can locate “Source Data†because the two macros listed
above have been deleted. Right now I run the macro and the UserForm pops up,
but no data is displayed in the TextBox.

Thanks for getting me this far Doug, but I am lost now. Please help me get
unlost.
Thanks,
Ryan--

 

[ryguy7272]

Ok, I was working on this for a good part of today and this is where I am
now. The code you gave me is kind of over my head Doug, so I may have to shy
away from it. I changed a few things in the Document, and on the VBA end.

I started inserting MergeFields, and now all Fields look something like this:
{MERGEFIELD City \* MERGEFORMAT}
{MERGEFIELD State \* MERGEFORMAT}
{MERGEFIELD Number \* MERGEFORMAT}
…etc.

I have three questions.

Question #1
Do I need to put Ҡaround City to create a string such as:
{MERGEFIELD "City" \* MERGEFORMAT}



Now, the code is as follows:
CommandButton:
Private Sub CommandButton1_Click()

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Mail Merge\Source
Data.xls", ConfirmConversions:=False, ReadOnly _
:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data"
Source="C:\Mail Merge\Source Data.xls":Mode=Read:Extended
Properties="":HDR=YES:IMEX="":Jet OLEDB:System database="""":Jet
OLEDB:Registry Path="""":Jet OLEDBatabase Password="""":Jet OLEDB:Engin",
SQLStatement:="SELECT * FROM `List`", SQLStatement1:="",SubType:=
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

End Sub


I get a “Compile Error: Syntax Error†at this line:
Source="C:\Mail Merge\Source Data.xls":Mode=Read:Extended
Properties="":HDR=YES:IMEX="":Jet OLEDB:System database="""":Jet
OLEDB:Registry Path="""":Jet OLEDBatabase Password="""":Jet OLEDB:Engin",
SQLStatement:="SELECT * FROM `List`", SQLStatement1:="",SubType:=
wdMergeSubTypeAccess

Question #2
What causes this error?



The Excel file is called “Source Data†and I named all ranges Insert > Name

Define (i.e., Name in A1, City in G1, Number in M1, etc.)

The following code is located below the code above:
Private Sub UserForm_Initialize()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\Mail Merge\Source Data.xls", False, False,
"Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub


Both of these subs:
Private Sub CommandButton1_Click() and Private Sub UserForm_Initialize()
Are now in the UserForm.

Question #3
Are these Subs in the correct area (in the UserForm)?



I have a module which contains only:
Sub UserFormInitialize()
UserForm1.Show
End Sub


I think I am close to finishing this thing. Can anyone assist for the last
5% or so?
Regards,
Ryan---

 

[ryguy7272]

I think I am almost where I need to be, but there is still one open item.
The following sub was given to me by Doug (thank you very much):
Private Sub CommandButton1_Click()

It works great when I insert a bookmark into my document, but it won’t do
anything for me now, as I have only MergeFields in my document. How can I
modify this sub to read any/all MergeFields, and import the appropriate data
from my Excel sheet? I’ve made some good progress in the past couple of days
(at least I think it is good progress), and I will continue to work on it
until I get it all straightened out. In the meantime, I’d really appreciate
it if someone out there can give me some assistance.



The following two subs contain the code in the UserForm:
Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide
End Sub


Sub UserForm_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Mail Merge\Source Data.xls", False, False,
"Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub



The Following two subs contain the code in the Module:
Sub Macro2()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Mail Merge\Source Data.xls", ConfirmConversions:=False, ReadOnly _
:=False, LinkToSource:=True, AddToRecentFiles:=False,
PasswordDocument:= _
"", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\Mail Merge\Source Data.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engin" _
, SQLStatement:="SELECT * FROM `List`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

Sub UserForm_Initialize()
UserForm1.Show
End Sub


For all of my fields I am using all MergeFields. The following is one
example (I believe there are 33 total)
{MERGEFIELD NUMBER \* MERGEFORMAT}



Thanks to all who viewed this post!!

Regards,
Ryan---

 

[ryguy7272]

For those interested, here is the final arrangement of code:
Code in Module:
Sub Userform_Initialize()
UserForm1.Show
End Sub



Code in UserForm (there are two subs here):
Sub Userform_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Documents and Settings\Contcts.xls", False,
False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub



Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("FirstName").Value = ListBox1.Value
ListBox1.BoundColumn = 2
ActiveDocument.Variables("LastName").Value = ListBox1.Value
ListBox1.BoundColumn = 3
ActiveDocument.Variables("Company").Value = ListBox1.Value
ListBox1.BoundColumn = 4
ActiveDocument.Variables("BusinessStreet").Value = ListBox1.Value
ListBox1.BoundColumn = 5
ActiveDocument.Variables("BusinessCity").Value = ListBox1.Value
ListBox1.BoundColumn = 6
ActiveDocument.Variables("BusinessState").Value = ListBox1.Value
ListBox1.BoundColumn = 7
ActiveDocument.Variables("BusinessPostalCode").Value = ListBox1.Value
' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub

In your Excel file, name your range (my range is called 'List' and the Excel
file is called 'Contacts.xls'). Finally, on the UserForm, you need to have a
CommandButton, named CommandButton1 and you also need a ListBox, named
ListBox1. The last step is to go to Word, click Insert > Field > DocVariable

(assign a name in the ‘New Name’ box) > Ok. Assign the links to

‘DocVariable’ wherever required throughout your document, fiddle with it a
little if it doesn’t work after the first attempt…you will get it to work.

Kind Regards,
Ryan—


PS, I extend a warm thanks to Jay Freedman and Doug Robbins for the
assistance. You two are amazing; I tip my hat to you guys.