VBA code to execute for a subsequent mail merge

[Colonel Blip]

Hello, All!

My outlook 2k3 contact form is the default. The contact names take the form
of Full Name made up of First Name and Last name. First name for a husband
and wife is of the form "Jack & Jill". I need, in a mail merge to be able to
separate this field into two fields if the & exists in the First_Name field.
I was hoping I could do in using Word's IF...Then... Else but I can't.

While I normally create my mailmerge using the OL Tool|Mail Merge wizard,
that is not necessary since I have my document designed and simply need the
data exported. Is there a way with a macro in OL to export Contacts in a
form that would be usable by Word's mailmerge, but as it does so to do the
following:

1. Check the OL First_Name field.
2. If there is a "&" in the field place the second name in the Spouse field
and drop the "&" and following and put the remaining name in the First_Name
field of the merge data file.
3. If there is not a "&" in the First_Name field then do not modify the
field.

I could then reconstruct the couples with the IF statement in Word, but I
would have the couples names in separate fields to be able to use them in
other conditional command I want to use.

Thanks,
Colonel Blip.
E-mail: (e-mail address removed)

 

[Doug Robbins - Word MVP]

You could use your data source with a directory type mail merge main
document in which you insert the mergefields into the cells of a one row
table. When you execute the merge to a new document, that document will
contain a table with a row of data for each record in the data source. You
could then insert a new column into the table and run a macro that iterated
through the cells in the column containing the first names and if there was
a & in the data in the cell, split the data so that part of it would remain
in the cell and the other part would be moved into the adjacent cell in the
new column. Then you could insert a row at the top of the table and into
the cells of that row, insert field names to be used when merging with that
file as the data source.

The following code will do the splitting of the names (assuming that they
are in column 1 and that you insert the new column to the right of it so
that it becomes column 2

Dim i As Long, j As Long
Dim drange As Range
With ActiveDocument.Tables(1)
For i = 1 To .Rows.Count
Set drange = .Cell(i, 1).Range
j = InStr(drange, "&")
If j > 0 Then
drange.Start = drange.Start + j + 1
drange.End = drange.End - 1
.Cell(i, 2).Range.Text = drange.Text
drange.Start = drange.Start - 3
drange.Delete
End If
Next i
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

 

[Colonel Blip]

Hello, Doug!
You wrote on Tue, 5 Sep 2006 21:50:24 +0200:

Maybe I'm missing a point here, but it seems to do what you're suggesting
means setting up a 2 step merge process. I will admit right now I enough of
a novice in this stuff that I may not be able to see where you are going (or
better yet, where I am going <g>) with this approach. FWIW, here is the
alternative that I know will work but requires redoing the contact data base
to have First_Name contain one name and have the Spouse filed have the
second name.

{ INCLUDEPICTURE "O:\\TEMP\\{ Mergefield "Full_Name" }.JPG" }


{ IF { MERGEFIELD Last_Name } = "Wonderful" "Sue & " "" }{ MERGEFIELD
"First_Name" }{ IF { MERGEFIELD Spouse }<> "" " & " "" }{ MERGEFIELD
"Spouse" }{ IF { MERGEFIELD Middle_Name }<> "" " " "" }{ MERGEFIELD
"Middle_Name" } { MERGEFIELD "Last_Name" }{ IF { MERGEFIELD Full_Name } =
"Jane Doe" " (with John Doe)" "" }

{ MERGEFIELD "Children" }
{ MERGEFIELD "Business_Address" }
{ IF { MERGEFIELD Home_Phone }<> "" "Home: " "" }{ MERGEFIELD "Home_Phone" }
{ IF { MERGEFIELD Business_Phone }<> "" "Work: " "" }{ MERGEFIELD
"Business_Phone" }
{ IF { MERGEFIELD Mobile_Phone }<> "" { MERGEFIELD "First_Name" } "" }{ IF
{ MERGEFIELD Mobile_Phone }<> "" " Cell: " "" }{ MERGEFIELD "Mobile_Phone" }
{ IF { MERGEFIELD Other_Phone }<> "" { MERGEFIELD "Spouse" } "" }{ IF {
MERGEFIELD Other_Phone }<> "" " Cell: " "" }{ MERGEFIELD "Other_Phone" }
{ IF { MERGEFIELD Email }<> "" "" }{ MERGEFIELD "Email" }
{ IF { MERGEFIELD Email_2 }<> "" "" }{ MERGEFIELD "Email_2" }
{ IF { MERGEFIELD Email_3 }<> "" "" }{ MERGEFIELD "Email_3" }
{ IF { MERGEFIELD Last_Name } = "Wise" "Jane Cell: (123) 123-4567" "" }

Thanks,

Colonel Blip.
E-mail: (e-mail address removed)

DRW> You could use your data source with a directory type mail merge main
DRW> document in which you insert the mergefields into the cells of a one
DRW> row table. When you execute the merge to a new document, that
DRW> document will contain a table with a row of data for each record in
DRW> the data source. You could then insert a new column into the table
DRW> and run a macro that iterated through the cells in the column
DRW> containing the first names and if there was a & in the data in the
DRW> cell, split the data so that part of it would remain in the cell and
DRW> the other part would be moved into the adjacent cell in the new
DRW> column. Then you could insert a row at the top of the table and into
DRW> the cells of that row, insert field names to be used when merging with
DRW> that file as the data source.

DRW> The following code will do the splitting of the names (assuming that
DRW> they are in column 1 and that you insert the new column to the right
DRW> of it so that it becomes column 2

DRW> Dim i As Long, j As Long
DRW> Dim drange As Range
DRW> With ActiveDocument.Tables(1)
DRW> For i = 1 To .Rows.Count
DRW> Set drange = .Cell(i, 1).Range
DRW> j = InStr(drange, "&")
DRW> If j > 0 Then
DRW> drange.Start = drange.Start + j + 1
DRW> drange.End = drange.End - 1
DRW> .Cell(i, 2).Range.Text = drange.Text
DRW> drange.Start = drange.Start - 3
DRW> drange.Delete
DRW> End If
DRW> Next i
DRW> End With

 

[Doug Robbins - Word MVP]

The directory type mailmerge and the macro amounts to re-doing the data
source.

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

 

[Peter Jamieson]

Other than Doug's suggestion, there are three or four ways you could do
this:
a. There are macros for exporting data from Outlook on www.slipstick.com -
you might need to adapt them
b. Ideally, you would be able to issue an OpenDataSource command from Word
to connect to the Outlook data source and issue SQL to construct the fields
you need. Unfortunately, there does not appear to be a simple way to do that
without being prompted for the Outlook contacts folder name. However, if you
have Access, I think you will be able to
- set up a linked table to your contacts folder
- set up a query that returns the fields you need
- use that as the data source
The trouble is that this method does not return as many of the fields in the
contacts folder as the "starting from Outlook" approach does. So even if the
basic idea works, you may not be abble to get the data you need. And it is
ludicrously complicated.
c. use Word Mail Merge Events to manipulate the data and stuff it into the
correct places in your document
d. consider "rolling your own" merge by automating from Outlook and
stuffing the values in each contact into Word Document Properties or
Document Variables, inserting them into your document using DOCPROPERTY or
DOCVARIABLE fields.

Peter Jamieson