Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Yousfi Benameur
  Where is Yousfi Benameur?
 El Bayadh
 Algeria
 Yousfi Benameur
 To: Yousfi Benameur
  Where is Yousfi Benameur?
 El Bayadh
 Algeria
 Yousfi Benameur
 Tags
Subject: RE: Office Automation
Thread ID: 209577 Message ID: 209690 # Views: 2 # Ratings: 0
Version: Visual FoxPro 7 Category: Forms
Date: Tuesday, December 30, 2008 2:00:54 PM         
   


> > Hi Experts
> >
> > Is it possible to send a Letter designed in MS Word to No. Of Customers from VFP Menu. RTF is a solution to do this but I want send Word designed letter. So Plewase help me.
> >
> > Rgds:
> > Ahsan Rana
>
> I dont know the source of the code i give below. I find it in archives.
> it allow you to product a word template letter and all the leters to print automatically.it works here with the table customers(adapt to your data)
>
 
> IF VAL(SUBSTR(VERSION(4),2,1))>7
> lthemes=_screen.Themes
> _screen.Themes=.f.
> ENDIF
> 
>    DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ;
>       Integer, String, String
> 
>    ***
>    * Create a string containing the settings appropriate to the driver.
>    * The following is an example for the Microsoft VFP ODBC driver
>    * accessing the Customer.dbf file.
>    ***
> 
>    *** Change the path below to point to the Customer table ***
>    *** in the \Samples\Data folder.                      ***
>    settings="DSN=visual foxpro tables"+chr(0)+;
>              "Description=VFP ODBC Driver"+chr(0)+;
>              "SourceDB=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA"+chr(0)+;
>              "SourceType=DBF"
>    =SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings)
> 
>    * NOTE: Ensure there are no spaces on either side of the equal sign (=).
> 
>    ON ERROR DO errhand WITH ERROR(), MESSAGE(),  MESSAGE(1), PROGRAM( ),
>    LINENO( ) && Trap OLE & other errors.
> 
>    * Initialize variables passed to Word to create form letter.
>    intro1="Congratulations! You are one of our best customers since you have ;
>    purchased $"
>    intro2=" each month from us. "
>    intro3="As a result, your maximum order amount has been increased by ;
>    $2500.00. If you have any questions, please feel free to contact us."
> 
>    oWord = CREATEOBJECT("Word.Application")
>       WITH oWord
>          * Assign values to variables
>          dsname="C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA\customer.DBF"
>          wformat=0
>          wconfirmconv=0
>          wreadonly=0
>          wlinktosource=0
>          waddtofilelist=0
>          wpassworddoc=""
>          wpasswordtemp=""
>          wrevert=0
>          wprotectdoc=""
>          wprotecttemp=""
>          wconn="DSN=visual foxpro tables;uid=;pwd=;"+;
>             "sourcedb=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA;sourcetype=dbf"+;
>             "exclusive=no;backgroundfetch=yes;collate=machine;"
>          wsqlstatement="SELECT contact,company,title,address,city,;
>             postalcode,STR(maxordamt,12,2) as maxordamt FROM customer ;
>             WHERE (customer.maxordamt>$100000)"
> 
>          .Visible=.T.      && Make Word visible.
>          .WindowState = 2   && Minimize Word.
> 
> 
>          .Documents.Add      && Add new document.
>          .Selection.InsertParagraphAfter
> 
>          .ActiveDocument.MailMerge.OpenDataSource;
>             (dsname,wformat,wconfirmconv,wreadonly,wlinktosource,;
>              waddtofilelist,wpassworddoc,wpasswordtemp,wrevert,;
>              wprotectdoc,wprotecttemp,wconn,wsqlstatement)
>          .ActiveDocument.MailMerge.EditMainDocument
>          .Selection.InsertDateTime("dddd, MMMM dd, yyyy", 1)
>          .Selection.MoveRight
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"contact")
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"company")
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .ActiveDocument.MailMerge.Fields.Add;
>              (oWord.Selection.Range,"title")
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"address")
>          .Selection.InsertAfter(", ")
>          .Selection.MoveRight
>          .ActiveDocument.MailMerge.Fields.Add(oWord.Selection.Range,"city")
>          .Selection.InsertAfter("  ")
>          .Selection.MoveRight
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"postalcode")
>          .Selection.MoveRight
>          .Selection.InsertParagraphAfter
>          .Selection.InsertParagraphAfter
>          .Selection.InsertAfter("Dear ")
>          .Selection.MoveRight
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"contact")
>          .Selection.MoveRight
>          .Selection.InsertAfter(":")
>          .Selection.MoveRight
>          .Selection.InsertParagraphAfter
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .Selection.InsertAfter(intro1)
>          .Selection.MoveRight
>          .ActiveDocument.MailMerge.Fields.Add;
>             (oWord.Selection.Range,"maxordamt")
>          .Selection.MoveRight
>          .Selection.InsertAfter(intro2)
>          .Selection.MoveRight
>          .Selection.InsertAfter(intro3)
>          .Selection.MoveRight
>          .Selection.InsertParagraphAfter
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .Selection.InsertAfter("Thank you,")
>          .Selection.MoveRight
>          .Selection.InsertParagraphAfter
>          .Selection.InsertParagraphAfter
>          .Selection.MoveDown
>          .Selection.Fields.Add(oWord.Selection.Range,-1,"author")
>          .ActiveDocument.MailMerge.Destination = 0 && new document.
>          .ActiveDocument.MailMerge.Execute         && Run the merge.
>          nanswer = MESSAGEBOX("Would you like to view the finished ;
>          document?",36,"Switch to Word")
>          DO CASE
>             CASE nanswer = 6
>                .ActiveWindow.View.Type = 3    && Page layout view.
>                .Visible = .T.
>                .Application.Activate   && Bring Word forward.
>                .WindowState = 0         && Word in normal state.
>                =INKEY(5)         && Wait 5 seconds then bring VFP back.
>                .WindowState = 2    && Then minimize Word.
>               ENDCASE
>          nanswer = MESSAGEBOX("Print the document?",36,;
>          "Print the merged document.")
>          DO CASE
>             CASE nanswer = 6         && Yes
>                .ActiveDocument.PrintOut   && Print the merged form letter.
>          ENDCASE
> 
>          nanswer = MESSAGEBOX("Save the document?",36,;
>          "Enter the name to save the merged document.")
>          DO CASE
>             CASE nanswer = 6         && Yes
>                cbaddoc=SPACE(25)
>                DO WHILE .T.
>                   DEFINE WINDOW getname FROM 1,1 TO 15,60 ;
>                      FONT 'COURIER NEW',10 FLOAT GROW ZOOM
>                      ACTIVATE WINDOW getname
>                      cdoc=SPACE(25)
>                      mquit = SPACE(1)
>                   IF !EMPTY(cbaddoc)
>                      @2,1 SAY "Existing file name: "+cbaddoc COLOR R+/w
>                   ENDIF
>                   @4,1 SAY "Enter a name for the Word document."
>                   @5,1 GET cdoc color b/w VALID emptyChk(cdoc)
>                   @7,25 GET mquit FUNCTION '*T Save' SIZE 2,10
>                   READ CYCLE
>                   CLEAR WINDOW getname
>                   cdoc=ALLTRIM(cdoc)
>                   cpath= .Options.DefaultFilePath(0)   && Get Word's
>                                                  && default document path.
>                   IF FILE(cpath+"\"+cdoc+".doc")
>                      WAIT WINDOW "Document already exists! Enter another ;
>                      file name." TIME 1
>                      cbaddoc=cdoc
>                      LOOP
>                   ELSE
>                      .ActiveDocument.SaveAs(cdoc)
>                   ENDIF
>                   .Documents.Close(0)
>                   .Application.Quit
>                   EXIT
>                ENDDO
>             CASE nanswer = 7         && No
>                .Documents.Close(0)
>                .Application.Quit
>             ENDCASE
>       ENDWITH
> 
>    ON ERROR  && Restore system error handler.
>    IF VAL(SUBSTR(VERSION(4),2,1))>7
> _screen.Themes=lthemes
>    endif
> 
>    PROCEDURE emptychk
>    PARAMETER pcdoc
>    IF !EMPTY(pcdoc)
>       RETURN .T.
>    ELSE
>       WAIT WINDOW "Enter a name for the Word Document"
>       RETURN 0
>    ENDIF
> 
>    PROCEDURE errhand
>    PARAMETER merror, mess, mess1, mprog, mlineno
>    CLEAR
>    IF merror=1426
>       mlineno=LTRIM(STR(LINENO()))
>       merror=LTRIM(STR(merror))
>       =MESSAGEBOX("You have an OLE error. Usually this is caused by
>       quitting Word or canceling out of a dialog box in Word. "+CHR(13)+;
>       "The error message is "+mess)
>    ELSE
>       mlineno=LTRIM(STR(LINENO()))
>       merror=LTRIM(STR(merror))
>       =MESSAGEBOX("An error occured on line "+mlineno+;
>       ". The error message is "+mess+" The error number is "+merror)
>    ENDIF
>    ON ERROR
>    RETURN
>    ***/ End program code /***
> 

> regards


Sorry we have many troubles in internet these 2 days.
this is the code tested.Now it works well for me.
IF VAL(SUBSTR(VERSION(4),2,1))>7
lthemes=_screen.Themes
_screen.Themes=.f.
ENDIF

   DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ;
      Integer, String, String

   ***
   * Create a string containing the settings appropriate to the driver.
   * The following is an example for the Microsoft VFP ODBC driver
   * accessing the Customer.dbf file.
   ***

   *** Change the path below to point to the Customer table ***
   *** in the \Samples\Data folder.                      ***
   settings="DSN=visual foxpro tables"+chr(0)+;
             "Description=VFP ODBC Driver"+chr(0)+;
             "SourceDB=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA"+chr(0)+;
             "SourceType=DBF"
   =SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings)


   * Initialize variables passed to Word to create form letter.
   intro1="Congratulations! You are one of our best customers since you have ;
   purchased $"
   intro2=" each month from us. "
   intro3="As a result, your maximum order amount has been increased by ;
   $2500.00. If you have any questions, please feel free to contact us."

   oWord = CREATEOBJECT("Word.Application")
      WITH oWord
         * Assign values to variables
         dsname="C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA\customer.DBF"
         wformat=0
         wconfirmconv=0
         wreadonly=0
         wlinktosource=0
         waddtofilelist=0
         wpassworddoc=""
         wpasswordtemp=""
         wrevert=0
         wprotectdoc=""
         wprotecttemp=""
         wconn="DSN=visual foxpro tables;uid=;pwd=;"+;
            "sourcedb=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA;sourcetype=dbf"+;
            "exclusive=no;backgroundfetch=yes;collate=machine;"
         wsqlstatement="SELECT contact,company,title,address,city,;
            postalcode,STR(maxordamt,12,2) as maxordamt FROM customer ;
            WHERE (customer.maxordamt>$100000)"

         .Visible=.T.      && Make Word visible.
         .WindowState = 2   && Minimize Word.


         .Documents.Add      && Add new document.
         .Selection.InsertParagraphAfter

         .ActiveDocument.MailMerge.OpenDataSource;
            (dsname,wformat,wconfirmconv,wreadonly,wlinktosource,;
             waddtofilelist,wpassworddoc,wpasswordtemp,wrevert,;
             wprotectdoc,wprotecttemp,wconn,wsqlstatement)
         .ActiveDocument.MailMerge.EditMainDocument
         .Selection.InsertDateTime("dddd, MMMM dd, yyyy", 1)
         .Selection.MoveRight
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"contact")
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"company")
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .ActiveDocument.MailMerge.Fields.Add;
             (oWord.Selection.Range,"title")
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"address")
         .Selection.InsertAfter(", ")
         .Selection.MoveRight
         .ActiveDocument.MailMerge.Fields.Add(oWord.Selection.Range,"city")
         .Selection.InsertAfter("  ")
         .Selection.MoveRight
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"postalcode")
         .Selection.MoveRight
         .Selection.InsertParagraphAfter
         .Selection.InsertParagraphAfter
         .Selection.InsertAfter("Dear ")
         .Selection.MoveRight
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"contact")
         .Selection.MoveRight
         .Selection.InsertAfter(":")
         .Selection.MoveRight
         .Selection.InsertParagraphAfter
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .Selection.InsertAfter(intro1)
         .Selection.MoveRight
         .ActiveDocument.MailMerge.Fields.Add;
            (oWord.Selection.Range,"maxordamt")
         .Selection.MoveRight
         .Selection.InsertAfter(intro2)
         .Selection.MoveRight
         .Selection.InsertAfter(intro3)
         .Selection.MoveRight
         .Selection.InsertParagraphAfter
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
         .Selection.InsertAfter("Thank you,")
         .Selection.MoveRight
         .Selection.InsertParagraphAfter
         .Selection.InsertParagraphAfter
         .Selection.MoveDown
     *    .Selection.Fields.Add(oWord.Selection.Range,-1,"author")
         .ActiveDocument.MailMerge.Destination = 0 && new document.
         .ActiveDocument.MailMerge.Execute         && Run the merge.
         nanswer = MESSAGEBOX("Would you like to view the finished ;
         document?",36,"Switch to Word")
         DO CASE
            CASE nanswer = 6
               .ActiveWindow.View.Type = 3    && Page layout view.
               .Visible = .T.
               .Application.Activate   && Bring Word forward.
               .WindowState = 0         && Word in normal state.
               =INKEY(5)         && Wait 5 seconds then bring VFP back.
               .WindowState = 2    && Then minimize Word.
              ENDCASE
         nanswer = MESSAGEBOX("Print the document?",36,;
         "Print the merged document.")
         DO CASE
            CASE nanswer = 6         && Yes
               .ActiveDocument.PrintOut   && Print the merged form letter.
         ENDCASE

         nanswer = MESSAGEBOX("Save the document?",36,;
         "Enter the name to save the merged document.")
         DO CASE
            CASE nanswer = 6         && Yes
               cbaddoc=SPACE(25)
               DO WHILE .T.
                  DEFINE WINDOW getname FROM 1,1 TO 15,60 ;
                     FONT 'COURIER NEW',10 FLOAT GROW ZOOM
                     ACTIVATE WINDOW getname
                     cdoc=SPACE(25)
                     mquit = SPACE(1)
                  IF !EMPTY(cbaddoc)
                     @2,1 SAY "Existing file name: "+cbaddoc COLOR R+/w
                  ENDIF
                  @4,1 SAY "Enter a name for the Word document."
                  @5,1 GET cdoc color b/w VALID emptyChk(cdoc)
                  @7,25 GET mquit FUNCTION '*T Save' SIZE 2,10
                  READ CYCLE
                  CLEAR WINDOW getname
                  cdoc=ALLTRIM(cdoc)
                  cpath= .Options.DefaultFilePath(0)   && Get Word's
                                                 && default document path.
                  IF FILE(cpath+"\"+cdoc+".doc")
                     WAIT WINDOW "Document already exists! Enter another ;
                     file name." TIME 1
                     cbaddoc=cdoc
                     LOOP
                  ELSE
                     .ActiveDocument.SaveAs(cdoc)
                  ENDIF
                  .Documents.Close(0)
             *     .Application.Quit  &&close manually if you have other word instances
                  EXIT
               ENDDO
            CASE nanswer = 7         && No
               .Documents.Close(0)
             *  .Application.Quit  &&close manually if you have other word instances
            ENDCASE
      ENDWITH

   IF VAL(SUBSTR(VERSION(4),2,1))>7
_screen.Themes=lthemes
   endif

   PROCEDURE emptychk
   PARAMETER pcdoc
   IF !EMPTY(pcdoc)
      RETURN .T.
   ELSE
      WAIT WINDOW "Enter a name for the Word Document"
      RETURN 0
   ENDIF

  
   RETURN
   ***/ End program code /***

regards

ENTIRE THREAD

Office Automation Posted by Ahsan Rana @ 12/29/2008 5:43:40 PM
RE: Office Automation Posted by Anders Altberg @ 12/29/2008 5:57:50 PM
RE: Office Automation Posted by Ahsan Rana @ 12/29/2008 6:42:14 PM
RE: Office Automation Posted by Anders Altberg @ 12/30/2008 1:31:09 PM
RE: Office Automation Posted by Biju Thomas @ 12/30/2008 3:13:41 AM
RE: Office Automation Posted by Anders Altberg @ 12/30/2008 12:56:46 PM
RE: Office Automation Posted by Cetin Basoz @ 12/29/2008 7:09:42 PM
RE: Office Automation Posted by Ahsan Rana @ 12/30/2008 6:01:31 AM
RE: Office Automation Posted by Cetin Basoz @ 12/30/2008 2:28:03 PM
RE: Office Automation Posted by Yousfi Benameur @ 12/29/2008 10:36:49 PM
RE: Office Automation Posted by Biju Thomas @ 12/30/2008 3:23:16 AM
RE: Office Automation Posted by Yousfi Benameur @ 12/30/2008 2:04:47 PM
RE: Office Automation Posted by Ahsan Rana @ 12/30/2008 5:48:52 AM
RE: Office Automation Posted by Yousfi Benameur @ 12/30/2008 2:15:45 PM
RE: Office Automation Posted by Ahsan Rana @ 12/31/2008 5:55:57 AM
RE: Office Automation Posted by Anders Altberg @ 12/30/2008 5:06:44 PM
RE: Office Automation Posted by Ahsan Rana @ 12/31/2008 5:53:31 AM
RE: Office Automation Posted by Yousfi Benameur @ 12/30/2008 2:00:54 PM
RE: Office Automation Posted by Cetin Basoz @ 12/30/2008 3:52:57 PM
RE: Office Automation Posted by Yousfi Benameur @ 12/30/2008 6:30:56 PM
RE: Office Automation Posted by Ahsan Rana @ 1/1/2009 6:37:54 AM