Word Mail Merge Help Needed

[PeterHarmes]

Hi,

I'm trying to automate a mail merge using word in my app and need some help. Originally, i opened a doc that contained a piece of VB code that automated the mail merge, however this only worked with Word 2000 and not the later versions so i have decided to try and automate it through my app.

My app creates a temporary dbf that could contain multiple records.
I open the word doc
I then want to run the mail merge routine
Then save the word doc as another file name
One word document should be created that could merge multiple records.

my code so far looks like this:

Code: Select all  Expand view

         
oWord := CreateObject("Word.Application")
oWord:Set("Visible",.f.)
cFile := mTempDir+Alltrim(TmpFile)
oDoc:=oWord:Get( "Documents" )
oWord:Set("DisplayAlerts",0)
oWord:Set("Visible",.F.)
oWord:Set( "WindowState", 2 )  // Minimize
oDoc:Open(cFile)
oDoc:MailMerge:MainDocumentType := 0 //wdFormLetters=0  // sets the mail merge main document type
oDoc:MailMerge:EditMainDocument()
oDoc:MailMerge:OpenDataSource(TmpDbf)  //TmpDbf is the path&file name of temp database
oDoc:MailMerge:Execute()
cName:=oWord:Get("ActiveDocument")
//about to save check to see if possible or if someone else
//has file open
IF FILE(mQuotNbr)
  IF FERASE(mQuotNbr) == 0
    OkToWord := .T.
  ELSE
    OkToWord := .F.
  ENDIF
ENDIF            
IF OkToWord
  cName:SaveAs(mQuotNbr)
ENDIF
IF cName:Saved() == .F.
  info_box(3,"205",.F.)
ENDIF         
oWord:Documents:Close(.F.)
oDoc:Open(mQuotNbr)
oWord:Set("Visible",.T.)
oWord:Set( "WindowState", 1 )  // Maximize


When i try the above code i get:

Error description: Error 1564412/9 'Item' is not a property.: MAILMERGE

Thanks in advance

Pete

[Enrico Maria Giordano]

Try

Code: Select all  Expand view

oWord:ActiveDocument:MailMerge:...

EMG

[PeterHarmes]

Thanks for your help Enrico, you have saved me a lot of time

Pete

[driessen]

Pete,

I am very interested in this topic.

Can you tell me what your DBF-file, which is used to accomplish the mailmerge, looks like ?

Thanks in advance.

[PeterHarmes]

There is nothing special about the dbf - just used for name, address and pricing details - the database is used to create a quotation and creates one record per price record - here is the code i use to create the dbf:

Code: Select all  Expand view

   LOCAL DetsArr := {}                
   //Header Details
   Aadd(DetsArr, {"PROSPECT" ,"C",10,0})
   Aadd(DetsArr, {"NAME"     ,"C",40,0})
   Aadd(DetsArr, {"ADDRESS1" ,"C",40,0})
   Aadd(DetsArr, {"ADDRESS2" ,"C",40,0})
   Aadd(DetsArr, {"ADDRESS3" ,"C",40,0})
   Aadd(DetsArr, {"ADDRESS4" ,"C",40,0})
   Aadd(DetsArr, {"POSTCODE" ,"C",20,0})
   Aadd(DetsArr, {"TELEPHONE","C",30,0})
   Aadd(DetsArr, {"FAX"      ,"C",30,0})
   Aadd(DetsArr, {"TITLE"    ,"C",10,0})
   Aadd(DetsArr, {"CONTACT"  ,"C",30,0})
   Aadd(DetsArr, {"SURNAME"  ,"C",30,0})
   Aadd(DetsArr, {"QUOTE"    ,"C",8,0})
   Aadd(DetsArr, {"START"    ,"D",8,0})
   Aadd(DetsArr, {"END"      ,"D",8,0})
   Aadd(DetsArr, {"ORIGOP"   ,"C",4,0})
   Aadd(DetsArr, {"ORIGNAME" ,"C",30,0})
   Aadd(DetsArr, {"SALEOP"   ,"C",4,0})
   Aadd(DetsArr, {"SALENAME" ,"C",30,0})
   Aadd(DetsArr, {"REFNO"    ,"C",30,0})
   Aadd(DetsArr, {"VALID"    ,"D",8,0})

   Aadd(DetsArr, {"DETAILTYPE","C", 1,0})

   //Container Price/Common Fields
   Aadd(DetsArr, {"DELIVERY"  ,"C",40,0})
   Aadd(DetsArr, {"DELADD1"   ,"C",40,0})
   Aadd(DetsArr, {"DELADD2"   ,"C",40,0})
   Aadd(DetsArr, {"DELADD3"   ,"C",40,0})
   Aadd(DetsArr, {"DELADD4"   ,"C",40,0})
   Aadd(DetsArr, {"DELPCOD"   ,"C",20,0})
   Aadd(DetsArr, {"CONTAINER" ,"C",10,0})
   Aadd(DetsArr, {"CONTDESC"  ,"C",30,0})
   Aadd(DetsArr, {"PRODUCT"   ,"C",10,0})
   Aadd(DetsArr, {"PRODDESC"  ,"C",80,0})
   Aadd(DetsArr, {"AREACODE"  ,"C",10,0})
   Aadd(DetsArr, {"AREADESC"  ,"C",30,0})
   Aadd(DetsArr, {"DISPSITE"  ,"C",10,0})
   Aadd(DetsArr, {"DISPNAME"  ,"C",30,0})
   Aadd(DetsArr, {"QUANTITY"  ,"N",2,0})
   Aadd(DetsArr, {"DELCHARGE" ,"N",7,2})
   Aadd(DetsArr, {"FREEDAYS"  ,"N",3,0})
   Aadd(DetsArr, {"RENTCHARGE","N",7,2})
   Aadd(DetsArr, {"RENTDAYS"  ,"N",3,0})
   Aadd(DetsArr, {"COLLCHARGE","N",7,2})
   Aadd(DetsArr, {"EXCHCHARGE","N",7,2})
   Aadd(DetsArr, {"ADDCHARGE" ,"N",10,2})
   Aadd(DetsArr, {"ADDFOR"    ,"C",30,0})
   Aadd(DetsArr, {"NOTES"     ,"C",LEN(QUOTSKIP->NOTES),0})
   Aadd(DetsArr, {"NOTES2","C",LEN(QUOTSKIP->NOTES2),0})
   Aadd(DetsArr, {"UNIQ_NBR"  ,"C",10,1})
   Aadd(DetsArr, {"CRED_DISC" ,"N",5,2})
   Aadd(DetsArr, {"ENVIR_TON" ,"N",5,2})
   Aadd(DetsArr, {"ENVIR_VOL" ,"N",5,2})
   Aadd(DetsArr, {"UPLIFT"    ,"N",3,0})
   Aadd(DetsArr, {"OFWHICH"   ,"N",7,2})
   Aadd(DetsArr, {"MAXCH"     ,"N",7,2})
   Aadd(DetsArr, {"MAXDAY"    ,"N",3,0})
   Aadd(DetsArr, {"MAXOFWH"   ,"N",7,2})
   Aadd(DetsArr, {"SPECCOL"   ,"N",7,2})
   Aadd(DetsArr, {"SPECOFW"   ,"N",7,2})

   //Weighbridge Price Fields
   Aadd(DetsArr, {"INTNOTE"   ,"C",15,0})
   Aadd(DetsArr, {"INTDESC"   ,"C",80,0})
   Aadd(DetsArr, {"ORIGCODE"  ,"C",10,0})
   Aadd(DetsArr, {"ORIGDESC"  ,"C",30,0})
   Aadd(DetsArr, {"HAULIER"   ,"C",10,0})
   Aadd(DetsArr, {"HAULNAME"  ,"C",40,0})
   Aadd(DetsArr, {"MINWEIGHT" ,"N",7,2})
   Aadd(DetsArr, {"MAXWEIGHT" ,"N",7,2})
   Aadd(DetsArr, {"MINVALUE"  ,"N",7,2})
   Aadd(DetsArr, {"MAXVALUE"  ,"N",7,2})
   Aadd(DetsArr, {"CHARGEBY"  ,"C",1,0})
   Aadd(DetsArr, {"HAULRATE"  ,"N",7,2})
   Aadd(DetsArr, {"HAULCHGBY" ,"C",1,0})
   Aadd(DetsArr, {"ADMINCHRG" ,"N",7,2})
   Aadd(DetsArr, {"PREMSAT"   ,"N",6,2})
   Aadd(DetsArr, {"PREMSUN"   ,"N",6,2})
   Aadd(DetsArr, {"PREMBANK"  ,"N",6,2})
   Aadd(DetsArr, {"REFERENCE1","C",30,0})
   Aadd(DetsArr, {"REFERENCE2","C",30,0})
   Aadd(DetsArr, {"RATE1"     ,"N",8,2})
   Aadd(DetsArr, {"QTY1"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND1"    ,"N",5,2})
   Aadd(DetsArr, {"RATE2"     ,"N",8,2})
   Aadd(DetsArr, {"QTY2"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND2"    ,"N",5,2})
   Aadd(DetsArr, {"RATE3"     ,"N",8,2})
   Aadd(DetsArr, {"QTY3"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND3"    ,"N",5,2})
   Aadd(DetsArr, {"RATE4"     ,"N",8,2})
   Aadd(DetsArr, {"QTY4"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND4"    ,"N",5,2})
   Aadd(DetsArr, {"RATE5"     ,"N",8,2})
   Aadd(DetsArr, {"QTY5"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND5"    ,"N",5,2})
   Aadd(DetsArr, {"RATE6"     ,"N",8,2})
   Aadd(DetsArr, {"QTY6"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND6"    ,"N",5,2})
   Aadd(DetsArr, {"RATE7"     ,"N",8,2})
   Aadd(DetsArr, {"QTY7"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND7"    ,"N",5,2})
   Aadd(DetsArr, {"RATE8"     ,"N",8,2})
   Aadd(DetsArr, {"QTY8"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND8"    ,"N",5,2})
   Aadd(DetsArr, {"RATE9"     ,"N",8,2})
   Aadd(DetsArr, {"QTY9"      ,"N",8,2})
   Aadd(DetsArr, {"ROUND9"    ,"N",5,2})
   Aadd(DetsArr, {"RATE10"    ,"N",8,2})
   Aadd(DetsArr, {"QTY10"     ,"N",8,2})
   Aadd(DetsArr, {"ROUND10"   ,"N",5,2})
   Aadd(DetsArr, {"CURRENCY"  ,"C",3,0})
   Aadd(DetsArr, {"PRIC_PRIOD","C",3,0})

   Aadd(DetsArr, {"EXTRA1"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA2"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA3"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA4"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA5"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA6"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA7"    ,"C",100,0})
   Aadd(DetsArr, {"EXTRA8"    ,"C",100,0})
   
   Aadd(DetsArr, {"QUO_NAME"  ,"C",40,0})
   Aadd(DetsArr, {"QUO_ADDR1" ,"C",40,0})
   Aadd(DetsArr, {"QUO_ADDR2" ,"C",40,0})
   Aadd(DetsArr, {"QUO_ADDR3" ,"C",40,0})
   Aadd(DetsArr, {"QUO_ADDR4" ,"C",40,0})
   Aadd(DetsArr, {"QUO_PCODE" ,"C",20,0})
   
   IF Alltrim( Upper( GetIwsSysIni("Quotation Module", "Use Temp Path for printing") ) ) == "YES"
         Dbcreate(TempPath()+ If(Right(TempPath(),1) == "\", "","\") +"QUODETS.DBF",DetsArr)
   ELSE
         Dbcreate("C:\QUODETS.DBF",DetsArr)
   END IF

   IF SELECT("QUODETS") <> 0
      QUODETS->(DBCLOSEAREA())
   ENDIF


I then populate the table with the price details.

Hope that helps

Pete

[driessen]

Pete,

Thanks a lot.

I notice that I have some study to do to come to a solution in my application.

[PeterHarmes]

Tell me about it - i had to copy most of the code from examples

[PeterHarmes]

I've almost got my new mail merge routine working, my app now opens the template performs a mail merge, saves with a new file name and opens the new merged document up. The only problem is that when i try to close word or the open document i get the following: The file is in use by another application or user. (c:\documents and settings\...\normal.dot)

Is this because i'm not closing a word doc correctly or something wrong woth my template doc? Source code below:

Code: Select all  Expand view

         oWord := CreateObject("Word.Application")
         oWord:Set("Visible",.f.)
         SECURITY->(DBSEEK(operator+"QUOTE     04"))
         SysRefresh()
         IF !Empty(TmpFile)
            cFile := mTempDir+Alltrim(TmpFile)
            oDoc:=oWord:Get( "Documents" )
            oWord:Set("DisplayAlerts",0)
            oWord:Set("Visible",.F.)
            oWord:Set( "WindowState", 2 )  // Minimize
            oDoc:Open(cFile)
            oWord:ActiveDocument:MailMerge:MainDocumentType := 0 //wdFormLetters=0  // sets the mail merge main document type
               oWord:ActiveDocument:MailMerge:EditMainDocument()
            oWord:ActiveDocument:MailMerge:OpenDataSource(TmpDbf)
            oWord:ActiveDocument:MailMerge:Execute()
            cName:=oWord:Get("ActiveDocument")
            //about to save check to see if possible or if someone else
            //has file open
            IF FILE(mQuotNbr)
               IF FERASE(mQuotNbr) == 0
                  OkToWord := .T.
               ELSE
                  OkToWord := .F.
               ENDIF
            ENDIF            
            IF OkToWord
               cName:SaveAs(mQuotNbr)
            ENDIF
            IF cName:Saved() == .F.
               info_box(3,"205",.F.)
            ENDIF         
            oWord:Documents:Close(.F.)
            oDoc:Open(mQuotNbr)
            oWord:Set("Visible",.T.)
            oWord:Set( "WindowState", 1 )  // Maximize
         ENDIF


Thanks in advance

Pete

[Enrico Maria Giordano]

Try

Code: Select all  Expand view

oDoc:Close( 0 )

EMG

[PeterHarmes]

Same result i'm afraid - i put the close here, is this correct?

Code: Select all  Expand view

            cFile := mTempDir+Alltrim(TmpFile)
            oDoc:=oWord:Get( "Documents" )
            oWord:Set("DisplayAlerts",0)
            oWord:Set("Visible",.F.)
            oWord:Set( "WindowState", 2 )  // Minimize
            oDoc:Open(cFile)
            oWord:ActiveDocument:MailMerge:MainDocumentType := 0 //wdFormLetters=0  // sets the mail merge main document type
               oWord:ActiveDocument:MailMerge:EditMainDocument()
            oWord:ActiveDocument:MailMerge:OpenDataSource(TmpDbf)
            oWord:ActiveDocument:MailMerge:Execute()
            cName:=oWord:Get("ActiveDocument")
            //about to save check to see if possible or if someone else
            //has file open
            IF FILE(mQuotNbr)
               IF FERASE(mQuotNbr) == 0
                  OkToWord := .T.
               ELSE
                  OkToWord := .F.
               ENDIF
            ENDIF            
            IF OkToWord
               cName:SaveAs(mQuotNbr)
            ENDIF
            IF cName:Saved() == .F.
               info_box(3,"205",.F.)
            ENDIF      
            oDoc:Close( 0 )   
            //oWord:Documents:Close(.F.)
            oDoc:Open(mQuotNbr)
            oWord:Set("Visible",.T.)
            oWord:Set( "WindowState", 1 )  // Maximize


[Enrico Maria Giordano]

Can you show a reduced and self-contained sample of the problem?

EMG

[PeterHarmes]

ok - i'll try - it will be monday before i can get it ready though

[driessen]

Hello,

I follow this topic because it looks very interesting to me.

Just for your information, I'd like to tell something about the way I handle mailmerge from my application with Word.

1. All my documents are saved in RTF-format. RTF is just an ASCII-file and very easy to read and to write to.

2. In my document, I put the variables between "[" and "]".

3. To read and write to my RTF documens, I use FOPEN, FREAD and FWRITE.

4. To do the mailmerge, I read my document until the first "[", then I read the name of the variable until the next "]". The variable, including the "[" and "]", is replaced by its corresponding value. Then I read till then next "[", and so on, until no "[" and "]" are found anymore.

5. Using this method, I am able to create my own functions (yes or no, add parts of text, even standard translations to other languages ...).

I use this method for years now (already in DOS) and it is working fantastic and even Word independed.

Really worthwhile to try.

Have a nice weekend.

[Otto]

Hello driesen,

I did exactly the same as you.
But WINWORD from a certain verson on (2000?) can't save pictures as links in RTF. So the documents with pictures are very large and if you have many placeholders this approach is slow.
How do you save the pictures?
Regards,
Otto

[driessen]

Otto,

BTW congratulations. Your next post will be your 1000th.

I never have pictures in my documents. My application handles the files of a laywer office, so the Word-documents are pure text files.

But my method works fine with Word 97, 2000, XP, 2003 and 2007.