Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Mail Merge Issues (Word XP)

    Hi all,
    I have been servicing a client and they raised some interesting issues regarding Mail Merge! I would like to post these and hope I could get some answers from the Word guru's that I can take back to this client!
    Any assistance will be very much appreciated!

    The client has 20 odd documents that contain merge fields. These docs were set up in Word2000. They also have source data set up as lists in Excel2000 that they merge into these letters. They have since migrated to Word XP and have been frustrated with the changes and hassles that they have picked up with the new merge procedures!

    QUESTIONS:
    1. When ever they merge date and currency data into these letters, the dates are short date style where in Excel it is Medium date style! The currency values loose their formatting and are merged as numbers with up to 10 decimal places!!! How do I fix this?

    2. The client mensioned that they have activated the "Confirm conversion on Open" option on the General Tab of the TOOLS OPTIONS Dialog. This prompts the user to choose a driver to import or connect with! They choose MS Excel Worksheets via DDE (*.xls). This assist with certain formats in the merge process. However...they need to do this for every single source file. Is there a way to select (identify) all the source files and have them converted in one go, sothat the prompt is not necessary?

    3. After the merge, they insert signatures at the bottom of the doc. These signatures are set up in Outlook. Since they activated Word XP as the Email Editor, access to the signatures are not available. I have looked to see if I can customise Words toolbar to include the Insert Signature command, just like there is in Outlook, but Word does not have this button in the Toolbar customize list. Is there a way to insert Outlook signatures into the doc if Word is the email editor?

    Thanx for your time in helping to answer some of these.
    Regards,
    Rudi

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail Merge Issues (Word XP)

    Hi Rudi,

    for (1), the issues can easily enough be resolved by adding the appropriate filed picture switches to the MERGEFIELDs, eg:
    {MERGEFIELD CurrencyValue # $,0.00} and
    to force a currency value to display with a '$' sign, thousands separator (where needed), and to a limit of two decimal places.
    {MERGEFIELD DateString @ "dddd, d MMMM yyyy"}
    to force a date string to display as 'Monday, 1 November 2004'

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge Issues (Word XP)

    Ok...I'll give this a try. Thanx. Do you possibly know if this is a conversion/migration to XP hassle. The client did not need to do this in Word 2000. No procedures or data has changed, but now suddenly the date and currency values do this?! I can have them put in the switches, (but they are gonna fuss with it as there are dozens of fields over the 20 odd letters!

    I also have a fourth question to add to my origional <post#=421568>post 421568</post#>, based on this statement...
    <hr>2. The client mensioned that they have activated the "Confirm conversion on Open" option on the General Tab of the TOOLS OPTIONS Dialog. This prompts the user to choose a driver to import or connect with! They choose MS Excel Worksheets via DDE (*.xls). This assist with certain formats in the merge process. <hr>
    4. When the dialog appears to prompt for a source file to merge, the dialog links automatically to MY DATA SOURCES. Is there a way to change that path to a drive and directory where the files are stored. If not, is there a macro that can be activated to FORCE the default path of this dialog to point to the area where the source files reside !???

    Thanx
    Regards,
    Rudi

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail Merge Issues (Word XP)

    Hi Rudi,

    I don't have Word XP, so I can't really comment on XP-specifics. However, if your client didn't need to use the file conversion dialog under Word 2000, I suspect the solution I posted would again obviate the need for that (though you might need to add pictire switches to the fields that are currently being helped by that). As for the other issues you've raised, I'm sure one of the mailmerge gurus will be able to help.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail Merge Issues (Word XP)

    I can give you a bit of background on the merge process. It was completely rewritten for Word 2002, and caused a good deal of grief for many of us, as it by default uses OLE DB to get data from the source document. As far as I know, the only way to really deal with formatting issues is to put the picture switch into the MERGE fields. You can use automation to set the connection method (DDE), and that might also resolve some issues with the generation of signatures if you also automate Outlook. We routinely do this sort of thing using Access to drive Word and Outlook.
    Wendell

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Issues (Word XP)

    Hi Rudy:
    I'll try to respond to some of the questions that haven't been answered, but don't take it as gospel...I'm still using Word 2000.
    1. There was a thread in which HansV discovered that Word will use the formatting from Excel (using DDE) if you:
    A. Turn on the field codes (Alt+F9) to view fields like {Mergefield FieldName}.
    B. Delete the word "Mergefield" & update your fields (F9).

    I don't know if that will work for the date format also, but you could give it a try. If it does, please post back as I'd like to know.

    2. &4. As far as I know, Word stores the last used datasource with each main document. If you rename or change the location of the datasource, you'll have to change it for each main document. I'm not sure if anyone has found a macro that could do it, similar to macropod's template to update Includetext files. <post#=261488>post 261488</post#>.

    3. If you have custom signatures in Outlook, you could copy & paste them in Word & then make each one of them a formatted autotext entry.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge Issues (Word XP)

    Hi all,

    Thanx for the responses. I have documented all your replies on file for reference.

    Phil,
    I will see if I can find out about the deleting of the word MERGEFIELD in the field code, and see if that helps to retain the formatting.

    I have since attempted a macro that will bypass the connecting to source on the merge toolbar.
    I created the macro to prompt for a source file and allow the user control over the default directory.
    Also a second macro allows the source to be opened from word and give instant access to the data for editing!

    See attachment

    Please review the macros and feel free to advise me or improve them where necessary!

    PS, is the FileDialogFilePicker the only way to collect a file path to use in Word.
    Is this the same as the Application.GetOpenFileName in Excel!?
    Attached Files Attached Files
    Regards,
    Rudi

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Issues (Word XP)

    Hi Rudi:
    I can't answer your VBA questions, but perhaps someone else will jump in. I will test it out when I get the chance, however. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge Issues (Word XP)

    Hi all,

    <hr>PS, is the FileDialogFilePicker the only way to collect a file path to use in Word.
    Is this the same as the Application.GetOpenFileName in Excel!?<hr>
    Can someone tell me what is the equivilent of the Application.GetOpenFileName (of Excel) in word.
    Is the FilePicker method the only way to collect a file and path in word via a dialog, and store it in a variable so as to use it to do some other action with the file?
    Regards,
    Rudi

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge Issues (Word XP)

    I was not familiar with that one, so I looked it up in Excel's help: "GetOpenFilename Method -- Displays the standard Open dialog box and gets a file name from the user without actually opening any files."

    In Word 97-2003, you can use the Dialogs collection's FileOpen dialog to do this, and in Word 2002-2003, you should be able to use the FileDialogs collection. I've only learned the older way. Here's a sample of how you could do it:
    <pre>Sub TestwdGetOpenFilename()
    MsgBox wdGetOpenFilename
    End Sub

    Function wdGetOpenFilename() As String
    Dim strPath As String
    With Dialogs(wdDialogFileOpen)
    If .Display = -1 Then
    ' User didn't cancel!
    strPath = Options.DefaultFilePath(wdCurrentFolderPath)
    If Right(strPath, 1) <> Application.PathSeparator Then
    strPath = strPath & Application.PathSeparator
    End If
    wdGetOpenFilename = strPath & Replace(.Name, """", "")
    End If
    End With
    End Function
    </pre>

    Hope this helps.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge Issues (Word XP)

    Thanx for your speedy reply Jefferson.

    I see you used a function procedure to return a value (the file path) to the variable. That is a method I never considered, though i suppose so logical, as you need a value returned. Thanx for that! It seems to be the method that is equivilent to the excel one. Have you ever used the filepickerdialog before. Could you tell me if this is a new option in 2003, or has it always been there! Am I using it in the right context. See the attachment in this same thread!?
    Regards,
    Rudi

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge Issues (Word XP)

    I haven't switched to the FileDialogs. I played with them once when working on another thread in the Lounge, but there are just enough differences to create coding errors... easier to stick with what I know until it stops working. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge Issues (Word XP)

    <hr>but there are just enough differences to create coding errors... easier to stick with what I know until it stops working.<hr>
    I definitely agree with you on this quote! Maybe we should inform Microsoft of this strategy too, before they change the Mail Merge feature again and created even more irritations! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •