Dynamic text for mail merger

[Mohan]

I need some ideas form Word / Excel Experts.

I posted the same question in Excel programming group and no one answered... Now I ask the same with word experts..

I currently have a program in Excel (with User forms) to generate the data for mail merge. This programs connects to two different SQL databases and gets the information back into Excel (There are some additional fields entered by users). All this is working fine.
Just to give you a back ground info.. The letters is like a refusal letter for a credit application. There are many reasons why the applications can be refused (about 25 different reasons). The way I am doing it now is : From the user form, the users selects that applicable refusal reasons (can be up to 25) which are check boxes on the user form. In many cases, only about 3 - 5 will be applicable to any particular application. However, the letter template in MS word has all 25 fields (as mail merge fields) and the ones selected will have "X" beside it.

I wan to change this to dynamic text in the word template. so that not all 25 fields need to be a mail merge field. Instead, I like to have only the selected text (refusal reason) appear in the word template (instead of putting an 'X' beside the applicable refusal reason). This means that all 25 refusal reasons are going to be coded in the VBA code (rather than having it on word). May be I can have a bookmark (instead by merge field) and put the text (refusal reason) in the appropriate bookmark. But this may not look pretty because the bookmarks are on a static location in the MS word and I may end up with unnecessary spaces (or blank lines) between refusal reasons.

Anyone has any idea to have dynamic text inserted into word (as a mail merge)?. The data is in Excel file.
I only want to have the 3 or 4 refusal reasons in the word template (not all 25 of them and put 'X' beside the 3 or 4 that are applicable..

Any help is appreciated.

Thank you

 

[JGM]

Hi Mohan,

Yes, you can use a bookmark... you only need one though.

Also, you say that the data is in Excel? You mean the actual refusal reason
text strings are stored in Excel? If so, you do not need to hard code them
in VBA. If it is not in Excel, please explain that point further... I would
not advise putting 25 lengthy text strings in the code itself... Difficult
to maintain and update for the user. There is no reason (that I can see from
my limited perspective on your situation) it can be in Excel with the other
data.

Finally, if you want to write in the letter only the applicable reasons, you
do not need a checkmark next to them anymore...no? Before you needed the
checkmarks to signify to the reader which reasons were "used" to refuse the
credit. Now, you will only include the applicable reasons.... so, instead,
you can have something like:

You were refused the loan because
You have no collateral,
You are already in debt,
You still live with your mother!
etc.

So, in short,

Place a bookmark at the apprpriate point in the refusal letter;
Set the paragraph style (for the bookmarked paragraph) so that it will look
good!
Get the user input from the userform;
Go to the bookmark;
Select the strings from Excel one by one;
Use InsertAfter or Selection.TypeText to insert the reasons one after the
other in the refusal letter
(Or, build a long string variable and insert it in one shot)
And you are done!

HTH
Cheers!
--
_______________________________________
Jean-Guy Marcil
(e-mail address removed)

I need some ideas form Word / Excel Experts.

I posted the same question in Excel programming group and no one

answered... Now I ask the same with word experts..

I currently have a program in Excel (with User forms) to generate the data

for mail merge. This programs connects to two different SQL databases and
gets the information back into Excel (There are some additional fields
entered by users). All this is working fine.

Just to give you a back ground info.. The letters is like a refusal letter

for a credit application. There are many reasons why the applications can
be refused (about 25 different reasons). The way I am doing it now is :
From the user form, the users selects that applicable refusal reasons (can
be up to 25) which are check boxes on the user form. In many cases, only
about 3 - 5 will be applicable to any particular application. However, the
letter template in MS word has all 25 fields (as mail merge fields) and the
ones selected will have "X" beside it.

I wan to change this to dynamic text in the word template. so that not

all 25 fields need to be a mail merge field. Instead, I like to have only
the selected text (refusal reason) appear in the word template (instead of
putting an 'X' beside the applicable refusal reason). This means that all
25 refusal reasons are going to be coded in the VBA code (rather than having
it on word). May be I can have a bookmark (instead by merge field) and put
the text (refusal reason) in the appropriate bookmark. But this may not
look pretty because the bookmarks are on a static location in the MS word
and I may end up with unnecessary spaces (or blank lines) between refusal
reasons.

Anyone has any idea to have dynamic text inserted into word (as a mail

merge)?. The data is in Excel file.

I only want to have the 3 or 4 refusal reasons in the word template (not

all 25 of them and put 'X' beside the 3 or 4 that are applicable..

 

[Keith Miller]

You could have all 25 reasons in the template, but formatted as hidden unless they are the selected
reasons. If an item in a bulleted list or any paragraph is formatted as hidden, it doesn't leave a
blank line.

Keith

I need some ideas form Word / Excel Experts.

I posted the same question in Excel programming group and no one answered... Now I ask the same with word experts..

I currently have a program in Excel (with User forms) to generate the data for mail merge. This

programs connects to two different SQL databases and gets the information back into Excel (There are
some additional fields entered by users). All this is working fine.

Just to give you a back ground info.. The letters is like a refusal letter for a credit

application. There are many reasons why the applications can be refused (about 25 different
reasons). The way I am doing it now is : From the user form, the users selects that applicable
refusal reasons (can be up to 25) which are check boxes on the user form. In many cases, only about
3 - 5 will be applicable to any particular application. However, the letter template in MS word has
all 25 fields (as mail merge fields) and the ones selected will have "X" beside it.

I wan to change this to dynamic text in the word template. so that not all 25 fields need to be a

mail merge field. Instead, I like to have only the selected text (refusal reason) appear in the
word template (instead of putting an 'X' beside the applicable refusal reason). This means that all
25 refusal reasons are going to be coded in the VBA code (rather than having it on word). May be I
can have a bookmark (instead by merge field) and put the text (refusal reason) in the appropriate
bookmark. But this may not look pretty because the bookmarks are on a static location in the MS
word and I may end up with unnecessary spaces (or blank lines) between refusal reasons.

Anyone has any idea to have dynamic text inserted into word (as a mail merge)?. The data is in Excel file.
I only want to have the 3 or 4 refusal reasons in the word template (not all 25 of them and put

'X' beside the 3 or 4 that are applicable..

 

[Guest]

Excellent idea. I'll try this.

Thank you

-----Original Message-----
Hi Mohan,

Yes, you can use a bookmark... you only need one though.

Also, you say that the data is in Excel? You mean the actual refusal reason
text strings are stored in Excel? If so, you do not need to hard code them
in VBA. If it is not in Excel, please explain that point further... I would
not advise putting 25 lengthy text strings in the code itself... Difficult
to maintain and update for the user. There is no reason (that I can see from
my limited perspective on your situation) it can be in Excel with the other
data.

Finally, if you want to write in the letter only the applicable reasons, you
do not need a checkmark next to them anymore...no? Before you needed the
checkmarks to signify to the reader which reasons were "used" to refuse the
credit. Now, you will only include the applicable reasons.... so, instead,
you can have something like:

You were refused the loan because
You have no collateral,
You are already in debt,
You still live with your mother!
etc.

So, in short,

Place a bookmark at the apprpriate point in the refusal letter;
Set the paragraph style (for the bookmarked paragraph) so that it will look
good!
Get the user input from the userform;
Go to the bookmark;
Select the strings from Excel one by one;
Use InsertAfter or Selection.TypeText to insert the reasons one after the
other in the refusal letter
(Or, build a long string variable and insert it in one shot)
And you are done!

HTH
Cheers!
--
_______________________________________
Jean-Guy Marcil
(e-mail address removed)

"Mohan" <[email protected]> a écrit dans le message de news: and no one
answered... Now I ask the same with word experts.. to generate the data
for mail merge. This programs connects to two different SQL databases and
gets the information back into Excel (There are some additional fields
entered by users). All this is working fine. like a refusal letter
for a credit application. There are many reasons why the applications can
be refused (about 25 different reasons). The way I am doing it now is :
From the user form, the users selects that applicable refusal reasons (can
be up to 25) which are check boxes on the user form. In many cases, only
about 3 - 5 will be applicable to any particular application. However, the
letter template in MS word has all 25 fields (as mail merge fields) and the
ones selected will have "X" beside it. template. so that not
all 25 fields need to be a mail merge field. Instead, I like to have only
the selected text (refusal reason) appear in the word template (instead of
putting an 'X' beside the applicable refusal reason). This means that all
25 refusal reasons are going to be coded in the VBA code (rather than having
it on word). May be I can have a bookmark (instead by merge field) and put
the text (refusal reason) in the appropriate bookmark. But this may not
look pretty because the bookmarks are on a static location in the MS word
and I may end up with unnecessary spaces (or blank lines) between refusal
reasons. word (as a mail
merge)?. The data is in Excel file. word template (not
all 25 of them and put 'X' beside the 3 or 4 that are applicable..


.