Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.newusers,microsoft.public.word.vba.general
|
|||
|
|||
VBA code to execute for a subsequent mail merge
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: |
#2
Posted to microsoft.public.word.newusers,microsoft.public.word.vba.general
|
|||
|
|||
VBA code to execute for a subsequent mail merge
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" wrote in message ... 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: |
#3
Posted to microsoft.public.word.newusers,microsoft.public.word.vba.general
|
|||
|
|||
VBA code to execute for a subsequent mail merge
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: 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 |
#4
Posted to microsoft.public.word.newusers,microsoft.public.word.vba.general
|
|||
|
|||
VBA code to execute for a subsequent mail merge
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 "Colonel Blip" wrote in message ... 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: 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 |
#5
Posted to microsoft.public.word.newusers,microsoft.public.word.vba.general
|
|||
|
|||
VBA code to execute for a subsequent mail merge
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 "Colonel Blip" wrote in message ... 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: |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge with word 2003 | New Users | |||
Form Letter Mail Merge using field code: {Database} | Mailmerge | |||
Mail Merge Losing Data | Mailmerge | |||
Mail Merge dropping leading zeroes from the zip code | Mailmerge | |||
zip code issues when working with mail merge and excel | Mailmerge |