Need to insert from a table into a Word 2003 document and email them using Microsoft outlook 2003 4

[titoneon]

Hi everyone,
I want to accomplish something here but actually i don't know how, i am wondering if i can get help from you.
Well i am still running Microsoft word 2003, Ms Outlook 2003 and VFP 5.0 to create prg, pjx, exe etc.... by the way i also i have VFP 6.0 but it is not installed, but if it is necessary i can install it, depends if you suggest me to do it.
This is what i want to to accomplish:
1- i have a word document that i need to email to a bunch of vendors but in that document i need to insert in a particular location of the document the Vendor name, then email this document to each of these vendor
the document is the same but in that particular location of the document their vendor name has to be inserted and then emailed it using Microsoft outlook 2003 that is setup with a POP3 and SMTP.
2- I have created in vfp 5.0 a table with all vendors name and their respective email.

can any one help me out here with some codes on how to call that word document, insert the vendor name in that particular location of the document and then be able to email it ?

Thanks in advance

 

[Mike Lewis]

Hello Titoneon,

Welcome to the forum.

You are asking rather a lot here. You want someone to actually write a large chunk of code for you. We do our best to help here, and it's possible that someone will do what you ask. But it would be much better if you learned the principles so that you can write the code yourself - or, at least, have a first shot at it. If you then get stuck on the details, you can post a specific question here.

To give you some general guidance: What you are trying to achieve is perfectly possible. You need to read up on Activex Automation (also called COM Automation) in general, and Word and Outlook Automation in particular. There are many articles and forum posts available on these subjects, and also at least one comprehensive book ("Microsoft Office Automation with Visual FoxPro", by Tamar Granor and others).

By the way, you can use either FoxPro 5.0 or 6.0 for this, so this in itself is no reason to upgrade (although 5.0 is very old now, and not the most reliable of versions; the best advice would be to switch to 9.0, but it's not essential for this particular job). Similarly, Office 2003 is perfectly OK as well.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

can any one help me out here with some codes on how to call that word document, insert the vendor name in that particular location of the document and then be able to email it

And, as Mike has said above: You want someone to actually write a large chunk of code for you.

We don't do that.
You can hire a SW Developer/Contractor to do that for you.

What we do here is to give you suggestions/advice on how you can approach this yourself to support your 'learning opportunity'.

The first part would most likely be best approached by using VFP Automation of Word.

The second part, now that a Word DOC file exists, would be to Send the DOC file as an Attachment via Outlook.

Good Luck,
JRB-Bldr

 

[titoneon]

Mike,
Thanks a lot to get back to me, really you are correct, i do a few things but never been involved in doing anything like this, I am trying believe me, in fact i have this book called "Microsoft Office Automation with Visual FoxPro, by Tamar Granor and Delta Martin" but i don't find what i need maybe you can point me to exact page where or how can i open this doc and insert the field i need in that position and of course how to let the program do the for .. endfor until all the fields values are inserted in the document and save each document unique for each inserted vendor name, then i will try how to automate the email, if you have or have seen something like can you point me to that ?
Thanks is advance

 

[titoneon]

Hi JRB-Bldr,
Well sorry if i meant that, it will be great but i meant since i am not very familiar with the automation, i just need at least some starting point like, wel if you want to access or open the existing word document , first you do this, second if you want to insert a field in that doc in an specific location then you need to do this and then use a loop to do it from the top of your table until all the records have been processed, that will give me some way to start and learn, so i want to excuse myself if i make you guys feel mad, sorry and thanks

 

[Mike Lewis]

There is absolutely no reason to apologise. We are happy to help as far as we can.

That said, I really think the best plan would be for you to sit down with the book, and read it from the start. Don't just look for examples of code, but rather try to understand the concepts - which are not particularly difficult. Try things out on your computer as you work through the book.

Doing that will take longer, but the important thing is that you will end up by understanding what you are doing. If you don't do that, the whole thing will be a mystery to you, and you will get stuck every time you make a minor mistake.

Give it a try, and come back when you have specific questions on matters of detail.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

No problem.

For meeting your immediate needs you might want to look at:
faq184-5150
It might help you get started.

well if you want to access or open the existing word document
Look at:

oWord = createobject('word.application')
odocument=oWord.Documents.Open('c:\all products') && Multiple page document.

in the referenced FAQ.

second if you want to insert a field in that doc in an specific location then you need to do this and then use a loop to do it from the top of your table until all the records have been processed,

I would begin by not using VFP at all.
Instead I would go into the Office application (Excel, Word, etc.) and start Recording a Macro.
I then do whatever I want to do (and it will be recorded)
Then, when done I go to Tools - Macro - Edit and review what steps were done.
I then take those steps back to VFP and write them into my Automation code.

On a different, but associated topic, I am assuming that you are using Outlook 2003. If that is true, you might want to look at a 3rd party tool to get around the Outlook SP-2 Security Patch

http://www.dimastr.com/redemption/home.htm

Get your Word document Automation working and then address sending them.
The general approach to Sending through Outlook has been posted here a number of times.

Good Luck,
JRB-Bldr

 

[titoneon]

Ok Mike,
Thanks a lot again, i will try that, the whole thing was trying to resolve a situation here w/o spending much time and money as well, i think what i should do is mailmerge from vfp, the only thing i won't be able to automate is sending emails automaticly after the merge, but Thanks a lot again and you are correct until we don't understand what we are doing it is not possible to fix mistakes in the future, i just came from Cuba recently and found this job, i never had the chance in Cuba to do anything like this, and now in this job they requested me to do this, so i am a little scare to jeopardize my job or lose it and as you know it is not easy to find a job(in USA) cause the time is running and i don't have an answer for that, i do a lot of programming here but never was involved with Ms word automation and outlook to email automaticly so that is why i was asking for some help, one more time thanks.
Ernesto

 

[JRB-Bldr]

For the Outlook sending of your emails you might want to look over:

faq184-766
faq184-1856
faq184-1156

And if you do a Search within this forum for: redemption
you will find more examples which are utilizing the 3rd party tool I referenced above.

Good Luck,
JRB-Bldr

 

[titoneon]

Hey JRB-Bldr,
Thanks a lot, i think that this is a good start point, i will have to make my own changes since there are going to be more than one email and each document has a unique name, i will have to put this to read the recipients from the table and going into a do while or for-- endfor loop until it pick all the emails, as well as the name for each document in order to attach them will see, thanks a lot again.

Ernesto

 

[TamarGranor]

On inserting into a document. If it's a single field, then rather than automation, you may be better off just putting a bookmark into the document and filling in the bookmark with automation. You'll find a little discussion of bookmarks in Chapter 4 of my book.

For the email portion, you can automate Outlook, but it may be easier for both you and your users if you use Craig Boyd's extended MAPI library:

http://www.sweetpotatosoftware.com/spsblog/2007/06/11/AdditionalExtendedMAPIFLLUpdate.aspx

Tamar

 

[titoneon]

Hi Tamar,
It is an honor for me that you have answered my question, i got your book in front of me and i am trying to understand how to work with the bookmark but i don't get it.
You are correct i just want to insert a single field from a table into the document, of course the table has 45 records and the fields are Vendname and email_acc, the document is already created in Ms word 2003
and need to be sent to each vendor with the field vendname inserted into that document in a particular place in the document but once the document gets that field value in, this document should be unique(i mean i will need to save it with a unique name cause i can not send that document to a different vendor, then later once i have all the documents with their respective vendname, i can proceed to email them, is there an example of what i need to accomplish somewhere, sorry probably my ignorance does not allow me to understand the procedure, how can vfp 5.0 tell or pass the field value to the document ? i guess first i need to open the doc in Ms Word, add a bookmark with the same name as the table field, so then the mechanism of transfer from vfp to the document is what i don't get it.
Thanks in advance
Ernesto

 

[Olaf Doschke]

This is between you and Tamar, but what exactly do you not find explained in Chapter 4 of Tamar's book. For sure it rather covers a general case of how to set a bookmark, and in fact I don't have the book at hand to look into it for you, but there should be sample code setting a bookmark, as Tamar says so, why don't you point to the code you don't understand.

Clearly you first need to add a bookmark into your template DOC and save it, that's not the job of VFP code, that's preparation you do once, manually.

Secondly you are right you need to pen/load that word document as the first step after you create an instance of Word for automation by loWord = CreateObject("Word.Application"). This line is your start to anything you do via OLE automation of Word. To load a DOC you write loWord.Documents.Open("<<file name of the doc here>>"). If that works sso far, you can at least show the loaded document to test it's the right one by loWord.Visible = .T.

This is just the starter, what to do to replace a bookmark I don't know. I assume it's not much more than one to four lines of code needed. If that code doesn't work with newer Office versions, what you can try is to go into word and turn on macro recording, then find the bookmark, replace it with some text manually, stop macro recording and look into the recoreded VBA code. That will give you a hint what you need to program.

Bye, Olaf.

 

[Mike Lewis]

Titoneon,

I won't try to give you a full explanation here, but this is an overview of how you work with bookmarks.

The first step is to place a bookmark in the master document, that is, the Word document from which you wish to generate the vendors' copies. You do this interactively, once only, from within Word. Place the bookmark at the point where you want the vendor's name to appear.

In your program, assume oDoc is an object reference to the Word document (if you don't understand what I mean by that, you really must read the book from the beginning). Then, to get an object reference to the bookmark, do this:

oBookMark = oDoc.Bookmarks(1)

(this assumes the bookmark in question is the first one in the doc).

Now, to insert the vendor's name at the bookmark, do this:

oBookMark.Range.InsertAfter("The Vendor's Name")

The final step is to save the document. I'll leave you to figure that out.

Give that a try, and let us know how you get on.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[titoneon]

Hi Olaf and Mike,
Thanks a lot for the help, i started to understand with your help.
Olaf, i don't mean she does not explain correctly, i meant i don't get it cause my ignorance of not being able to understand, that is all, i really appreciate all the help from her and you guys to point me to the right direction, as mike said i need to read the book from the beginning in order to be able to accomplish what i want now and for the future, sorry for being a pain.
Thanks a lot
Ernesto

 

[JRB-Bldr]

An alternative to using Bookmarks is to use the Word FIND to locate your 'cursor' and then perform your Insert.

NOTE - this approach fills out the remainder of a Word document 'line' - not inserting into the existing text within a line.

I worked out this by doing what I suggested about using Macros to find out what commands needed to be done and then converted them into VFP Automation code.

Something like:

* --- NOTE - The Word DOC file is already open by this point ---

* --- Mark Starting Position ---
nStart = loWord.SELECTION.START

* --- Re-Initialize Cursor Location ---
loSelection.START = 0
loSelection.END = 0

* --- Locate Position Just Beyond Second Position ---
loWord.SELECTION.FIND.TEXT = "Date Submitted:"  && This text already exists in 'blank' document
loWord.SELECTION.FIND.Replacement.TEXT = ""
loWord.SELECTION.FIND.Execute

* --- Mark End Position ---
mnEnd = loWord.SELECTION.START - 1

* --- Select "To-Be-Replaced" Text ---
loSelection.START = nStart
loSelection.END = nEnd

* --- Blank Existing "To-Be-Replaced" Text ---
loSelection.DELETE

* --- Define New Text ---
cNewText = "Date Submitted:  " + DTOC(mdListDate) ;
   + CHR(13);
   + "Text to follow Date:  xxxxccccvvvv"

* --- Insert New Text  Over-writing FIND Text ---
loSelection.TypeText(cNewText)

 

[TamarGranor]

JRBBldr - You're right that find and replace can do the trick here, but for what Ernesto wants to do, a bookmark is the simplest solution.

Ernesto - I wouldn't create all the documents and then email them all. I'd use a single loop through the table, creating the document for a customer and emailing it in one sequence. Also, I think if you just start reading the book from the beginning of chapter 4, you should be able to figure it out.

Tamar

 

[titoneon]

Hi Everyone,
So far this is what i have, after i created the bookmark in the document file ,i am able to open the word 2003 doc and be able to insert from the table named "vendinfo.dbf" one record from the field "company" in the location i wanted and it is perfect, i just put this code inside a prg file and ran it to checked, the thing now is, how to email this document after the value of the company field have been inserted ? the field "email" is in the same table, i guess i need to create a loop to go an access again the word doc and go to the next record in that table and pick the data from the field "company" and the data from field "email" and email it as an attachment to the respective email account and so on, here where things will get a little complicated for me (If anyone after looked my code, can help out to approach what need to do it will be very appreciated), i am doing this in VFP 5.0, i realized that some of the commands you posted here are for vfp 6.0 and above, so i had to use the one for VFP 5.0 here is what i have so far in the prg file.

Note: i know i have no progressed to much but at least i was able to insert the first record, thanks to all of you.
code inside the I_compn.prg:

PUBLIC oWordObj
USE VENDINFO && table that has two fields company and email
oWordObj=CREATEOBJECT("Word.Basic")
oWordObj.FileOpen (SYS(2004)+"\Letworldole.doc")
*oWordobj.AppShow && this will show the word doc file on the screen just for testing
*oWordRef.FileClose() && this will close the word doc file, i guess i don't need this line
oWordObj. ww7_EditGoTo ("company") && this is the bookmark mane in the word document
oWordObj.Insert (vendinfo.Company)
oWordobj.AppShow && i put this here to check if the data from field company was inserted
*oWordObj.FilePrint && i found this so will allow me to print the doc and check how the file looks when printing, i don't need this, this was just for testing

Thanks for looking and helping
Ernesto

 

[Olaf Doschke]

The code jrbbldr gave you should also work with VFP5, you're just automating Word.Basic and he is automating Word.Application, that's very different and has very different methods to find a bookmark and insert text there.

If your code works that far, keep it. What I miss in your code is saving the doc under a company specific name, though, you have to add that.

In regard to mailing, well, that's of course completely different code, depends on what you have at hand besides Windows and VFP, eg you Mail Software. Is it Outlook? Outlook Express? Nothing perhaps? Even if you have no Mail software to automate, you can use several VFP code snippets available where you already were pointed to, you just would need to know your mail server, login credentials, port number and such to send a mail using SMTP/POP3.

So, what do you have at hand? With Outlook it's quite simple to send a mail:

loOutlook = CreateObject("Outlook.Application")
loMailItem = loOutlook.CreateItem(0)
loMailItem.To = "recipient@somewhere.com"
loMailItem.Subject = "testmail"
loMailItem.Text = "Hi, this is a test mail"
loMailItem.Attachments.Add("d:\yourdocs\company.doc") && at this place the name can be computed from the company field of your DBF, eg as in:
* loMailItem.Attachments.Add("d:\yourdocs\"+Alltrim(vendinfo.Company)+".doc")
loMailItem.Send()

But just visit the links jrbbldr gave you or take a look at

http://fox.wikis.com/wc.dll?Wiki~AutomatedEmail

The possibilities are endless.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, I posted an error: loMailItem.Text would need to be loMailItem.Body.

But all that code will only work, when you have Outlook anyway. Anyway, we first need to know, if you have that or something else.

Bye, Olaf.