VB Solution required!

[Graham]

I am hoping someone can help.
In every letter we send by mail merging, we have a field FULLUSER. we also
need an email address, but this is not available form the data source - and
never will be.
So, what I need to know is if VB could be used to split the text string
FULLUSER into two strings, using the blank space present between first and
second names.
I could then use these two strings, with a full stop in the middle, to make
the email address. Or maybe easier to replace the space with a full stop, but
it would still have to be a new string as FULLUSER is used on each letter.
Sorry if my use of the term 'string' is wrong - I am going back to when I
used extended basic, and I seem to remember you could cut text strings in
this way?
I do hope somone can help... The only alternative is a lookup table, but
that seems a bit messy on each of 1000+ letters!

 

[Greg]

Graham,

Maybe something like this will get you started:

Sub SpiltName()
Dim oStr As String
Dim myArray
oStr = ActiveDocument.Bookmarks("FullName").Range.Text
myArray = Split(oStr)
MsgBox myArray(0)
MsgBox myArray(1)
End Sub

 

[Kris]

Graham,

Maybe something like this will get you started:

Sub SpiltName()
Dim oStr As String
Dim myArray
oStr = ActiveDocument.Bookmarks("FullName").Range.Text
myArray = Split(oStr)
MsgBox myArray(0)
MsgBox myArray(1)
End Sub

Blimey that was quick. So if I make a bookmark "Fullname" that would be
populated with mergefield FULLUSER would this subroutine work as is? How do I
make sure the sub runs after the merge data is imported?
Graham

 

[Greg]

Kris,

I am sorry I can't help further and in fact might have given false
hope. The simple lines of code I posted in response to your question
was intended to show how you can use Split in VBA to separate a string
into segments. I don't know how, or even if, you can intercept a
string of text coming into a merger document and separate it and
display the result in different fields. Sorry.

 

[Jean-Guy Marcil]

Kris was telling us:
Kris nous racontait que :

Blimey that was quick. So if I make a bookmark "Fullname" that would
be populated with mergefield FULLUSER would this subroutine work as
is? How do I make sure the sub runs after the merge data is imported?

It depends what you want to do and how the merge is being done at the
present.

You stated that you might do this and that with the split information.
Wihthout knowing ore, it is difficult to help.

If the merge is run manually, you would have to call the macro after the
merge is done. But you would have to manipulate the FULLUSER field before
creating the individual letters because once the letters are merged, the
fields are gone (and so is the bookmark).

But really, it will be complicated to do what you want with the mergefields
in Word. It would be a lot , and I mean a lot, easier to split the info in
the database.
Ant let this be a lesson to whoever designed the database: You never create
a database with multiple info in the same field (like first and last name,
or city and state, age and gender, etc.)

Otherwise, you could assign a unique paragraph (or character) style (or a
font colour) to the string that will hold the FULLUSER info, and then, after
the merge, run a macro to locate all those strings and do something with
them. It would be a bit messy and might take a while to run if you have
1000+ letters, but it would work.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Ed]

Jean-Guy - I've never worked with merge fields, and barely a bit with
bookmarks, so I may be totally off-base here. But wouldn't it be possible
to create a larger bookmark with the mergefield inside? Here's my idea (and
it may be unworkable, but it's an idea!):

If you construct the bookmark so you know how many characters are in front
of and/or behind your merge field, then you can capture the whole text of
the bookmark, including the merged text, TRIM off the front and/or back, and
then have a string left over to play with. For instance, would a bookmark
the encompasses
*****
MERGEFIELD
*****
give you your merged text when the *'s in front and back are trimmed off?

Ed

 

[GTS]

Kris,

I am sorry I can't help further and in fact might have given false
hope. The simple lines of code I posted in response to your question
was intended to show how you can use Split in VBA to separate a string
into segments. I don't know how, or even if, you can intercept a
string of text coming into a merger document and separate it and
display the result in different fields. Sorry.

Well your lines of code do work perfectly - splitting the FULLNAME into two,
and displaying each separate name in a pop-up message box. If the subroutine
can be run, say, on print, then the bookmark would be populated. So can a
sub be set to run when the document prints? If so, then the nexty question
is how to get the two new separate name strings into the document? i.e. how
to get myArray (0) and myArray(1) onto the page? Can they be turned into
fields somehow? As you can see, I don't have a clue about VB!
Graham

 

[GTS]

Kris was telling us:
Kris nous racontait que :


It depends what you want to do and how the merge is being done at the
present.

You stated that you might do this and that with the split information.
Wihthout knowing ore, it is difficult to help.

If the merge is run manually, you would have to call the macro after the
merge is done. But you would have to manipulate the FULLUSER field before
creating the individual letters because once the letters are merged, the
fields are gone (and so is the bookmark).

But really, it will be complicated to do what you want with the mergefields
in Word. It would be a lot , and I mean a lot, easier to split the info in
the database.
Ant let this be a lesson to whoever designed the database: You never create
a database with multiple info in the same field (like first and last name,
or city and state, age and gender, etc.)

Otherwise, you could assign a unique paragraph (or character) style (or a
font colour) to the string that will hold the FULLUSER info, and then, after
the merge, run a macro to locate all those strings and do something with
them. It would be a bit messy and might take a while to run if you have
1000+ letters, but it would work.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

Thank you for your input. To answer your questions:
I wish to print the split information on the letter. So the letter ends
something like:
Yours Sincerely
MERGEFIELD "FULL NAME"
TEL ......
Email (e-mail address removed)

The merge is not run manually. The software package we use passes just one
record at a time to Word, along with the letter name that is required. Word
then opens the correct letter, merges the record, produces a new document
and prints that one document. So only one letter at a time is produced, but
there is a large number of different letters. There merging and printing
process is fully automated, there is no user interaction with Word at all.
Each letter needs the user's email address.

The existing plan is to use a lookup table in Excel, inserting a database in
every letter just to look for FULLNAME in the lookup table and return a
correct email address for each users name.
To me, this method seems cumbersome, and of course the excel table will have
to be open all the time.
I thought a VB solution to split the name would be more gracefull and save
having to have Excel running all the time...
Graham

 

[Jean-Guy Marcil]

Ed was telling us:
Ed nous racontait que :

Jean-Guy - I've never worked with merge fields, and barely a bit with
bookmarks, so I may be totally off-base here. But wouldn't it be
possible to create a larger bookmark with the mergefield inside?
Here's my idea (and it may be unworkable, but it's an idea!):

If you construct the bookmark so you know how many characters are in
front of and/or behind your merge field, then you can capture the
whole text of the bookmark, including the merged text, TRIM off the
front and/or back, and then have a string left over to play with.
For instance, would a bookmark the encompasses
*****
MERGEFIELD
*****
give you your merged text when the *'s in front and back are trimmed
off?

I see where you are going with this. This way you could do a search for
marker string + next word, and another for word + marker sting.

It is possible, but more work then simply using colours or styles, but
depending on the context, your idea could be used as well.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Ed]

Yes, I see what you mean. I ran over the part about assigning a style, etc
to the string and missed it. I'm not used to playing with styles - I still
make everything Normal and then Format paragraph, font, etc. Then again,
what I do (technical reports) doesn't give lots of room for "playing", and
not very many people I work with would know what to do with a toolkit full
of styles.

Ed