Automate Mail merge with MS word

[Jalz]

Hi Guys,

I've been tasked with creating templates within word and exporting data out of FileMaker to merge with the word document.

I thought ODBC was the way to go, and the driver seems a little hit and miss in Word. On some occasions it works great and in others it fails to connect 3-4 times. I know I can export out of FM a merge file and do a mail merge that way, but I dont want subsets of my exported data vulnerable on the harddrive in what is essentially a text file.

I was wondering whether it is possible to create a script in FM that will export my found set of records and create a merge automatically, and Delete the exported merge file?

I'm basically trying to find the neatest way to mail merge data from FM into Word documents.

Many Thanks

Jalz

[agutleben]

Hey Jalz,

While it is possible to script the deletion of an exported file, I do not believe it is possible to control the merging of data into Word from an FM process.

It sounds like there is concern surrounding the data itself. Could the files be exported to a secured location on a file server where you have better control of the files? It seems specifying a process for users may also be helpful. I'm thinking that once the files are available, the users have x amount of time and then the files would be deleted.

Not the perfect answer, but thought I'd chime in.

My 2 cents..

[Simon K]

try taking a look at EZxslt - it worked really well for me ...

ezxslt

[normanicus]

I suspect it would be easier to export your data to Excel and then do a mail merge to Word from there.

The Excel to Word steps are dealt with here:

http://wordprocessing.about.com/od/usingmailmerg1/l/blexcelmerge2.htm

and I'm sure there are many more.

[Wim Decorte]

It's actually ENTIRELY doable to do this from within FM. You can write a VBscript (the code would be pretty much the same as the VBA macro that you'd write in Excel or Word itself) and have it executed with any of the VBscript plugins or even without it (store your vbscript in a container and export it then call it).

The sequence is like this:

- FM exports the data to a merge file

- FM executes the VBscript to do the merge

- the VBscript deletes the exported file

- FM deletes the vbscript

Remember that FM can run OS commands through the Send Event script step. To delete a file all you'd need to do is:

cmd /c del c:someFile.ext

The Send Event syntax can be a calc so you can make the syntax on the fly...

[Jalz]

Thanks all for replies.

Especially Wim, given me confidence this can be done. Now I need to figure out how create a mail merge vba script in word.

Can I populate error messages into a Filemaker field from a vb script?

Thanks

Jalz

[Jalz]

Hi Wim,

Been searching high and low for some vbscript examples with Word and MailMerge. Found nothing, so I created a vba macro in word which seems to work locally.Haven't put it on our school network which is tight so not sure its going to work.

However I do have a question, can I automate FileMaker so it runds the Macro automatically rather than the user having to push a button?Any help much appreciated would love it if FM incorporated export to Word Dc in their v11 release.

Sub MM()

'

' MM Macro

'

'



End Sub

Sub Macro1()

'

' Macro1 Macro

'

'

    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

    ActiveDocument.MailMerge.OpenDataSource Name:= _

        "C:UsersMeDesktopwordtest.mer", ConfirmConversions:=False, _

        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _

        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _

        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _

        Connection:="", SQLStatement:="", SQLStatement1:="", SubType:= _

        wdMergeSubTypeOther

    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _

        , Text:="""Name"""

    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _

        , Text:="""Age"""

    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _

        , Text:="""Department"""

    With ActiveDocument.MailMerge

        .Destination = wdSendToNewDocument

        .SuppressBlankLines = True

        With .DataSource

            .FirstRecord = wdDefaultFirstRecord

            .LastRecord = wdDefaultLastRecord

        End With

        .Execute Pause:=False

    End With

End Sub

Edited November 12, 2009 by Guest

[Wim Decorte]

Yes you can, in a few different ways:

- by using a VBscript plugin (easiest): that way all you have to do is make your VBscript exit with a certain result (StdOut) and set the result of the plugin call in a variable or field

- have the vbscript create a text file somewhere and import that into a scratch table or a global field

You can't set a field directly from the outside using FM's ActiveX interface, it's a glaring hole in their implementation. Using the AppleScript interface on the Mac you can do it, but not on Windows.

[Wim Decorte]

Here's a simple VBscript that does what you need to do:

- in your FM script export the data that needs to be merged

- have a Word document set up using merge fields in the body

- no need for a VBA macro in the word document, the vbscript opens the word document, sets the merge source and does the merge into a brand new word document (named based on some variables and the ID field in the exported data).

In the VBscript code below, substitute "<>" with where your word document is (I actually store it in a FM container field and export it out when I need it) and substitute "<>" with the location where you export the merge file to.

The vbscript as is will delete the word template and the exported merge data after the merge is done.

option explicit



const wdSendToNewDocument = 0

const wdMainAndDataSource = 2

const wdDoNotSaveChanges = 0

const docType = "Chattel"

const TEMPLATE = "<>"

const DATA = "<>"



dim wd, tempDoc, wdTemplate,loan

dim fso



Set wd = CreateObject("Word.Application") 

wd.visible = true

set wdTemplate = wd.Documents.Open(TEMPLATE) 

wdTemplate.MailMerge.OpenDataSource(DATA)



set tempDoc = wdTemplate.Mailmerge

loan = tempdoc.datasource.datafields("ID").value

if tempDoc.state = wdMainAndDataSource then

	tempDoc.Destination = wdSendToNewDocument

	tempDoc.Execute

end if



wdTemplate.close (wdDoNotSaveChanges)

wd.activedocument.saveas("h:" & doctype & " - " & loan & ".doc")



set fso = createobject("scripting.filesystemobject")



fso.deletefile(TEMPLATE)

fso.deletefile(DATA)



'clean up



set tempDoc = nothing

set wdTemplate = nothing

set wd = nothing

set fso = nothing



wscript.quit

[Jalz]

Hi Wim,

This is absolutely awesome! The document is merging, however I get the following error -

Windows script host

Line 19

Char1

The requested member of collection does not exist

800A1735

Any ideas what this means?

I've just commented out this line

'loan = tempdoc.datasource.datafields("ID").value

and the error has dissappeared. What does this line actually do and do I need it?

Many Thanks again, this is really hot stuff.

Jalz

Edited November 17, 2009 by Guest

[Wim Decorte]

In my merge export file I have a field named "ID" and I use whatever value is in that ID field as part of the Word document file name.

You can safely remove that line...

[Jalz]

Hi Wim,

Just wondered whether you had anu further tips/trick regarding word templating. Im currently storing the template in a container field, like you , and then with you vbscript, i export it out, merge data and voila.

My question is, is there any neat way within FileMaker on the windows platform, where the user can select the fields they want a layout. The process in my head would be, to open up a layout in Filemaker called word templates. This will have a portal, with multple word templates which I will generate. I may however have the requirement, where my end users can create their own templates, and that means dragging and dropping fields onto the word document.Can this be done within FM?

Thanks again for your advice.

Jalz

[Wim Decorte]

You can't drag and drop onto a word file itself. But I can see something like this:

Keep the body of the word document in a FM text field and let users drag and drop placeholders in the text (and generally write the text themselves).

When you generate the word document you basically first subsitute the placeholders with your data.

The whole body of the text gets then exported to the merge file and there is Word template setup to merge that whole "body" field into it.

Something like that.

[Jalz]

Hi Wim,

Thanks for the idea, Im trying to implement what you have suggested in your last email, I have a global 'body' field which I export (this also contains names and addresses from placeholders in my code). My question is how can I export the whole body of text which contains paragraphs and more importantly text with commas?

Many thanks

Jalz

[Wim Decorte]

Just so it's clear to everyone, the issue here is that in an exported merge file:

- returns signal the end of a record

- commmas signal different fields

In this setup we really only have one field and one record but any returns between paragraphs and commas in the text would mess up the merge format.

The solution is to substitute returns and commas with something unique while still in FM just before the export (like |||zzzReturnzzz||| for each return,...)

Then in the VBscript that does the merging, after it created the new Word document, do a replace of those unique strings back to real returns and commas.

HTH

Wim

[Jalz]

Hi Wim,

Thanks for that idea, will hopefully try and implement it in the next couple of days. Awesome idea of substituting the characters before export - would never of thought of that!

On another note - I remember you doing a session on Outlook integration in Devcon (I think it was 2007 if my memory serves me correct?) Any chance you made those your presentation/sample files available? My next task will be to send calendar events from a FM solution to outlook - really wish FM would of done it natively....

[Wim Decorte]

I'll see if I can upload it soon. Remind me if you don't see it in the "solutions" section next week.

[Jalz]

Hi Wim,

Just thought I'd prompt you for the outlook demo files

Thanks

Jalz

[Dchall_San_Antonio]

Does anyone know what the design philosophy is at Filemaker such that they would require such an obtuse solution to a relatively normal use of a database?

My reason for using the database in the first place is to have a reliable system for storing data and retrieving it according to one of several dates. Secondarily, but equally important, I need to print the sorted catalog of selected records. My colleagues nearly universally use Word to keep their records and report their findings. Any change in sort order requires considerable effort for them to cut and paste their Word doc. Some of the more technosavvy use both Excel and Word. At least Excel allows them to resort as new information is received; however, as we all know, mis-sorting a spreadsheet is the chief cause of suicide among Excel users. Neither of these "solutions" is reliable nor particularly easy to use.

When I got into this business (historical real estate title research) I immediately went for the database solution and taught myself how to use Access. Later one of my recruits to the business discovered Filemaker Pro and eventually convinced me to use it despite the inability to merge a catalog into Word.

My industry might be small but I believe many would flock to Filemaker if it was easy to merge with Word - or ANY real word processor. Kluging up reports inside of Filemaker is a lot like brain surgery to these guys. All too frequently I am surprised by a subtle change in a font size that changes the flow of my reports. My co-workers absolutely cannot deal with report design unless it is mostly done for them (ala Word). In my humble opinion the Filemaker folks would have a significantly better product if it was Word-friendly.

[Wim Decorte]

Later one of my recruits to the business discovered Filemaker Pro and eventually convinced me to use it despite the inability to merge a catalog into Word.

Hold on a second now. FM is entirely capable of doing a word merge natively. In a number of different ways. What we've done in this thread is just one option and it not even a kludge.

The choice taken here is to not have code embedded in Word, which would be another choice:

- FM exports to merge

- FM opens the word document, word document has VBA code to do the merge

Other ways include

- exporting from FM in XML format and applyign an XSLT to make the word document

- using ODBC from within Word to retrieve data from FM

As you can see there are many options and they all fall squarely within what other applications do too. So I don't really get where you're coming from.

[Dchall_San_Antonio]

Here's where I'm coming from.

I have a report template in Word. The report will be what Word used to call a catalog where one report will have a long list of records from the database. If I can explain that a little better...the report is a letter format. At the top of page 1 is the sender, addressee, date, etc. that goes on a letter. There is an introductory paragraph and immediately follows the first record from the database. Word uses double brackets to identify the field, like FM does. The letter report might be 5 or 500 pages long depending on the selection in the db. Immediately following the last record is a closing paragraph and the sender's signature.

When I used Access for this, the Word template was less than a page long. Similarly in FM the template is a page long, but FM is a poor word processor. So are you saying there is a way to replicate what I was doing with Word and Access using Word and FM?

[Wim Decorte]

What you used to do is also possbile with FM.

I don't get why your template in Word is longer than it was with Access. Word uses merge fields (the ones with the << >>) and it doesn't really care what data source it comes from...

[Jalz]

Hi Wim,

Just wondering, is it possible to update a FileMaker Text field from Word? What I would like to do is edit a word document and automate a transfer to a filemaker text field. I guess the answer is going to be no, but thought I would ask just incase you said it was possible....

[Jalz]

I'll see if I can upload it soon. Remind me if you don't see it in the "solutions" section next week.

Hi Wim,

Just wondered whether you have had the time to upload this to the solutions area?

Thanks

Jalz

[Dchall_San_Antonio]

What you used to do is also possbile with FM.

I don't get why your template in Word is longer than it was with Access. Word uses merge fields (the ones with the << >>) and it doesn't really care what data source it comes from...

I said they were similar. If you need them to be identical, then they are identical.

Word does care where the data comes from. I gave up on using Word and FMP together several years ago. Are you suggesting that the newer version of Word and the newer version of FMP will easily recognize .fp7 files as being a data source for a merge? If so please help me through the process.

[Wim Decorte]

No, Word does recognize .fp7 as a direct data source option. But there are many export formats that they have in common: tab, csv, merge,...

And there is also the ODBC option: Word can use ODBC data sources and FM can be set up as an ODBC data source through its ODBC drivers.

[Dchall_San_Antonio]

Which brings us right back around to the OP's original question.

I did use the export function for about a week when I first got FMP but found it to be intolerably cumbersome. With Microsoft Office I could print several merge docs from the same data resorted different ways (in real time) in about as long as it took to write this paragraph. With the FMP export process in the way, you can't do that. FMP needs to get with Microsoft and work this out to a seamless integration. The industry I work with will never stray from MS Office products. Unfortunately that limits the ability of a superior database program to get a foothold. I've been using PDF output from FMP but am getting increasing pressure to output to Word.

[Lee Smith]

FMP needs to get with Microsoft and work this out to a seamless integration. The industry I work with will never stray from MS Office products.

Well, that isn't going to happen.

FileMaker and Apple don't even communicate like that.

Lee

[Fenton]

"FMP needs to get with Microsoft and work this out to a seamless integration."

Strangely enough, the Mac version of Word (and Excel) integrate fairly seamlessly with FileMaker, because there is no competing Access application for Macs. So this is another instance where business concerns trump consumer desires. I don't see it changing. Unless Microsoft buys FileMaker from Apple (who owns it), and hell freezes over.

[Delta Tango]

Wim,

Thanks for this post. I was trying to figure out myself how to create a folder using the send event script step, but using a parameter to pass the folder name to be used. The thing is I cannot use GetTempFolder function because the users don't have access to create files on our server anywhere else but a specific directory, hence the need for this. So I wanted to create a script that would create a folder for that user within that folder where they do have access privileges.

Example:

ServerNameWorkFilesTemp

Is the folder where they can create files.

I wanted to create a send event script step that creates a folder within that location for that user to generate his files for the session.

So I wrote this calc for the send event step using your given example:

"cmd /c mkdir ServerNameWorkFilesTemp" & Get(AccountName)

Would this be the correct syntax? It worked the first time but the second time it crashed filemaker.

By the way, what does the /c do?

Thanks!

[Wim Decorte]

It was actually devcon 2005... time does fly :

Here's the demo file for creating an outlook appointment.

outlook.zip

[joyasmith]

Well…Automation is a process that allows the any application which is written in any format like c# ,c# .net and visual C# .net to control other application programmatically with each other. There is basically an object model and the project model……and a paragraph object also. The mailing procedure with Microsoft word is easy just …follow some steps. For mailing procedure first of all check the reference level of windows application.Micro soft 11.0 object library on the com tab then Add your reference with that table……….!

------------------------------------------------------

ZWCAD

CAD Design

[Jalz]

Thanks Wim for the example file; ive got the database creating tasks.

@Joyasmith it all sounds interesting and understand the concept and even though I have Visual Studio .net 2008 with c# on my desktop - I haven't really done anything with it. I suppose I'm a little 'frightened' getting the syntax of c# and php mixed up as I actually understand FileMaker php webpublishing - but would love to build a little plugin on windows that talks to FileMaker and Word in very basic form.

Edited February 5, 2010 by Guest