topic-13071

Board index FiveWin for Harbour/xHarbour Word Mail Merge Help Needed

Page: 1 2 (18 posts)

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 09:22 AM

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 Collapse

 			
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

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 09:33 AM

Try

Code: Select all Collapse

oWord:ActiveDocument:MailMerge:...

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 09:40 AM

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

Pete

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 10:13 AM

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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 10:33 AM

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 Collapse

	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

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 12:08 PM

Pete,

Thanks a lot.

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

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Thu Oct 09, 2008 12:58 PM

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

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 09:54 AM

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 Collapse

			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

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 01:09 PM

Try

Code: Select all Collapse

oDoc:Close( 0 )

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 02:17 PM

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

Code: Select all Collapse

				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

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 04:32 PM

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

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

PeterHarmes

Posts: 363

Joined: Wed Feb 15, 2006 02:06 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 04:34 PM

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

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 08:02 PM

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.

All my documents are saved in RTF-format. RTF is just an ASCII-file and very easy to read and to write to.
In my document, I put the variables between "[" and "]".
To read and write to my RTF documens, I use FOPEN, FREAD and FWRITE.
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.
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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 08:10 PM

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

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Word Mail Merge Help Needed
Posted: Fri Oct 31, 2008 08:58 PM

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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Page: 1 2 (18 posts)

FiveTech Support Forums