Template, VBA and Mail Merge problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CJ - 11 Jul 2007 19:44 GMT

I have a template. I have a user form. I have an Excel spreadsheet which
contains my data. Everything is working beautifully, but.....many users are
not Mail Merge proficient and the whole template idea may be trashed if I
can't make it more automatic. I'm trying to make it easier.
Is there a way to program Word's "Find Entry" to default to a specific field?
Or even better, I would like to declare a variable, and have the user enter
their initials, and have VB pick it up and search the spreadsheet for the row
containing that variable. So I can get the variable, but I can't plug that
into the Find Entry "find what" field, and I can't default the Field to a
specific field.
Research results appears like it can't be done, but before throwing in the
towel on the mail merge direction of my project, I thought I'd at least post
here. Thanks!

Reply to this Message

Russ - 12 Jul 2007 04:49 GMT

Your message is a little confusing.
Excel has the lookup function to bring back information.
Are you trying to do a lookup in Excel from Word?
Mail Merge is different from document formfields or activex controls.
Userforms are different from document formfields or activex controls.
The Find method in Word can find text patterns, styles, and formatting.
Other collections like table cells can be iterated through and tested.

From Shauna Kelly:
Control Excel from Word
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

For information on inserting text into a bookmark, see
Inserting text at a bookmark without deleting the bookmark
http://www.word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm

For a simple example of putting data from Excel into Word, see
http://tinyurl.com/39ga4g

> I have a template. I have a user form. I have an Excel spreadsheet which
> contains my data. Everything is working beautifully, but.....many users are
[quoted text clipped - 9 lines]
> towel on the mail merge direction of my project, I thought I'd at least post
> here. Thanks!

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Russ - 12 Jul 2007 06:06 GMT

CJ,
Just found:

From CyberTaz,
In Word's Tools menu you'll find a feature for Mail Merge for which Excel
files can be used as the record source for the merge. The differing versions
don't matter. If you're not familiar with the feature you might start by
looking it up in Word Help. Then take a look at the information you'll find
here:

http://word.mvps.org/faqs/mailmerge/CreateAMailMerge.htm

More is available from the links on this page4:

http://word.mvps.org/faqs/MailMerge/index.htm

> Your message is a little confusing.
> Excel has the lookup function to bring back information.
[quoted text clipped - 27 lines]
>> towel on the mail merge direction of my project, I thought I'd at least post
>> here. Thanks!

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

CJ - 12 Jul 2007 13:56 GMT

I'm sorry I didn't make enough sense. I have the merge working, and the user
form simply collects the recipient information and places it in the template
at the correct places, and I have the Merge "Find Entry" window opening via
visual basic. What I want to accomplish is to either have the "Find in
Field" default to a particular field rather than having to choose a field
every time; or even better, pass a variable to the MailMergeFindRecord form
for full automation.

I have found code to find specific text within a specific field:
ActiveDocument.MailMerge.DataSource.FindRecord FindText:="Text", _
Field:="Field_Name"
But it does not to what I want to do.

> CJ,
> Just found:
[quoted text clipped - 43 lines]
> >> towel on the mail merge direction of my project, I thought I'd at least post
> >> here. Thanks!

Reply to this Message

CJ - 12 Jul 2007 19:36 GMT

Oh, boy, can I sure be dense!!! Why try to go through all that when the
simple fix is to just rename my columns and make go with the Find Entry
default, which is "name" and I've been trying to force it to default to a
different column. Oh my goodness. The simplest things sometimes can be so
elusive.

If anyone does come up with code do change the MailMergeFindRecord default
field, I would still very much appreciate it!!!

So now basically I have one excel sheet from which several different
templates can pull and use data. And all I have to do is manage the
database. That is awesome.

> I'm sorry I didn't make enough sense. I have the merge working, and the user
> form simply collects the recipient information and places it in the template
[quoted text clipped - 56 lines]
> > >> towel on the mail merge direction of my project, I thought I'd at least post
> > >> here. Thanks!

Reply to this Message

David Sisson - 12 Jul 2007 14:58 GMT

> Or even better, I would like to declare a variable, and have the user enter
> their initials, and have VB pick it up and search the spreadsheet for the row
> containing that variable. So I can get the variable, but I can't plug that

Could you use a listbox in the Userform instead of a single lookup?
http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Reply to this Message

CJ - 12 Jul 2007 15:18 GMT

Yes, I could. Then what would I do?

> > Or even better, I would like to declare a variable, and have the user enter
> > their initials, and have VB pick it up and search the spreadsheet for the row
> > containing that variable. So I can get the variable, but I can't plug that
>
> Could you use a listbox in the Userform instead of a single lookup?
> http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Reply to this Message

Russ - 12 Jul 2007 18:46 GMT

I found this in Word VBA Help for the term code:
For Each aField In ActiveDocument.MailMerge.Fields
If InStr(1, aField.Code.Text, "Title", 1) Then
MsgBox "A Title merge field is in this document"
End If
Next aField

> Yes, I could. Then what would I do?
>
[quoted text clipped - 5 lines]
>> Could you use a listbox in the Userform instead of a single lookup?
>> http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

CJ - 12 Jul 2007 19:14 GMT

The information in the shortcut was very interesting, thank you.
But I still need to do the following:
1. get the user's initials - which I can do
2. go to the database - which I can do
3. go to the column containing the initials - which I can do
3. find the cell containing the initials - which I can do
4. get the entire row - which I can do
5. merge certain of those cells in that row into fields in the template.

I'm so sorry, but I'm missing the entire concept.

> I found this in Word VBA Help for the term code:
> For Each aField In ActiveDocument.MailMerge.Fields
[quoted text clipped - 11 lines]
> >> Could you use a listbox in the Userform instead of a single lookup?
> >> http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Reply to this Message

Russ - 12 Jul 2007 22:48 GMT

Are you trying to reinvent the Query in mailmerge?
http://word.mvps.org/faqs/mailmerge/MMergeQueryOptions.htm

> The information in the shortcut was very interesting, thank you.
> But I still need to do the following:
[quoted text clipped - 24 lines]
>>>> Could you use a listbox in the Userform instead of a single lookup?
>>>> http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

CJ - 13 Jul 2007 14:34 GMT

Oh, yes!! This is exactly what I needed. Thank you so much Russ!
(I did figure out I could just move the column, since the default is the
first column in the spreadsheet -- doh).

> Are you trying to reinvent the Query in mailmerge?
> http://word.mvps.org/faqs/mailmerge/MMergeQueryOptions.htm
[quoted text clipped - 27 lines]
> >>>> Could you use a listbox in the Userform instead of a single lookup?
> >>>> http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

Reply to this Message