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]

Ernesto,

It's good to see you've made so much progress. Well done. With the Outlook code that Olaf gave you, you should be nearly there (assuming you have Outlook installed).

However, I would point out that using oWordObj=CREATEOBJECT("Word.Basic") is not the best approach. As Olaf suggested, it is more usual to use "Word.Application". The point is that Word.Basic is quite old technology, and might not always be supported. Word.Application is the preferred method now.

That said, I would suggest you stay with Word.Basic now, since you have come so far with it. If you changed, you would have to make some significant changes to your code, and I can understand you wouldn't want to do that. But it's something to keep in mind for the future.

Note that this issue has nothing to do with which version of VFP you are using. VFP 5.0 and 6.0 both have the same features when it comes to automation. Nor does it matter which version of Microsoft Office you are using. All versions from 97 onwards support both methods of automation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[titoneon]

Hi Guys,

Thanks for everything , Yes i have Microsoft outlook 2003 installed and ready,
One thing i want to mention is when i use the code below

oWordObj = CreateObject("Word.Application")
oWordObj.FileOpen (SYS(2004)+"\Letworldole.doc") && this is the name of the document
oWordobj.AppShow
I get this error "Ole error code 0x800706ba the RPC SERVER is unavailable"

Now when i use this code
oWordObj=CREATEOBJECT("Word.Basic")
oWordObj.FileOpen (SYS(2004)+"\Letworldole.doc")
oWordobj.AppShow

No errors and the document opens, that is what i said that maybe cause i am using VFP 5.0 and not VFP 6.0 i am getting this error but again i can be wrong.
Ok according to Tamar will be probably the best just to automate word by inserting the field company data as i did and then email that document on the fly, then comeback to the document continue to the next record and insert the next field company data and email it and so on until the end of the file from table Vendinfo ? again do not misunderstand me, i do not want to upset anyone here, if i need to save the document as "vendinfo.company" then the file will be saved in the disk and later called as an attachment to be sent from the outlook code you posted here, is that ok ?
well i need to this in a loop correct ? so if want to email this current record then i will need to replace loMailItem.To = "recipient@somewhere.com" for loMailItem.To = alltrim(vendinfo.email) is this correct ? so what will the best approach for the loop to call the document insert the vendinfo.company", save the file as "vendinfo.company and then email it to "Vendinfo.email" and continue to the end of table ?
Thanks a lot
Ernesto

 

[Mike Lewis]

Ernesto,

I think the reason for the error might be your use of oWordobj.AppShow. Try changing it to oWordobj.Visible = .T. (I'm not completely sure about this).

As you have changed from Word.Basic to Word.Application, you can expect to have to make other changes to your code. The two systems use quite different object models, and therefore have different properties and methods. This is why I suggested you stay with Word.Basic for now.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[titoneon]

Hi,
is this the correct way to save the file ?
oWordobj.ActiveDocument.Saveas(sys(2004)+"\"+alltrim(vendinfo.company)+".doc")

Thanks
Ernesto

 

[Mike Lewis]

No.

SYS(2004) points to the Visual FoxPro directory. You don't want to save your file there. Simply specify the name of the directory and file where you want to save it. For example:

oWordobj.ActiveDocument.Saveas("c:\MyData\" + alltrim(vendinfo.company) +".doc")

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Both Mike and I said stay wih your code, if it works. You just weren't able to use krbbldrs code or mikes code without error, as that was written for Word.Application and not for Word.Basic. These two objects are totally different. Both are word related, but these are not just synonyms. With word.application you automate the Word as Application, with Word.Basic you automate the Basic language engine inside Word.

In both cases your program code does not depend on your foxpro version, because you really execute Code within Word, VBA code, your loWord object is just referencing Word.EXE and automation really means you remotely execute code inside Word.Exe. So your foxpro version doesn't matter, but which object you automate matters and which version of Word. As Mike said, th Word version doesn't matter much as all these things already where available in Word 97.

As your code works, stay with. Don't just switch to Word.Application because I mentioned it, you're now with Word.Basic. I don't know though, how that would reference a word document, load or save it. If Tamars code automated Word.Basic, then stay with that.

In regard to saving a word document: oWordobj.ActiveDocument is referencing the active word document, if oWordobj is a Word.Application object. Does this line fail? If so, with what error?

You need write access rights in Sys(2004), which is unlikely, if your program is installed in the system folder PROGRAM FILES, because this folder is there to store executables to execute them, and nothing else. The only time you write there, is if a program is installed, so this folder and subfodlers are writable only to an admin in elevated state (eg while running a setup). Define some directory, where you want to store your word docs and use that instead of SYS(2004), if you have a write problem.

Bye, Olaf.

 

[Olaf Doschke]

Mike,

>SYS(2004) points to the Visual FoxPro directory.

That's what I thought, too, as I displayed ? Sys(2004), but help says it's the directory of vfpNr.dll (or vfpN.exe inside the IDE), the current runtime. So if you install your final EXE and put the vfp runtime with it, SYS(2004) would be the application directory, and could differ from HOME().

Nevertheless I share your advice, change it to a concrete directory. Anything you know you can write to.

Bye, Olaf.

 

[titoneon]

Mike,
i added this oWordobj.ActiveDocument.Saveas("c:\"+alltrim(vendinfo.company)+".doc") inside a with and endwith and get this error "ole errror code 0x8000706be the remote procedure cal failed" and if i not include the saveas line in the with ... endwith i get this error " ole error code 0x800020006 Unknown name"

so if this correct ?
with oWordobj
oWordobj.ActiveDocument.Saveas("c:\"+alltrim(vendinfo.company)+".doc")
Documents.Close
.Application.Quit
endwith

Do you know why i get these errors ?

Ernesto

 

[Olaf Doschke]

With oWordobj means you want to every line, which follows, with this object, until endwith.
So in these lines you want to abbreviate your code, so you won't write oWordobj within, but any line beginning with a dot really is referring to oWordobj.

Forget about this, if you don't really understand it, simply write it out:

oWordobj.ActiveDocument.Saveas("c:\"+alltrim(vendinfo.company)+".doc")
oWordobj.Activedocument.Close()
oWordobj.Quit()

If you do the last line, you quit word in further loops you will need to recreate a word object again. Don't do this.

I assume you get the different errors in different line. Do you know how to let VFP show you the line of error, which line of code really caused what error?

Bye, Olaf.

 

[titoneon]

Ok Guys,
I will copy my prg file, my word doc file and my table to another directory that i will call TESTW and then make the changes inside my prg file, i will stil use ("word basic") and see what happen.
can i still use this syntax for saving a file in vfp 5.0 oWordobj.ActiveDocument.Saveas("c:\tempw\"+alltrim(vendinfo.company)+".doc")

Thanks

 

[titoneon]

Olaf,
Ok i understand this should be like this

with oWordobj
.ActiveDocument.Saveas("c:\"+alltrim(vendinfo.company)+".doc")
.Activedocument.Close()
endwith

correct ?
Erensto

 

[Olaf Doschke]

By the way: For different, but also good reasons, often enough users can't write into the C:\ root directory. You neither want to clutter your program folder nor a root directory with an uncounted number of files.
Really create a folder and put your doc in there. This also makes it easier to delete them finally, just one folder you select and delete. Of course you could also select all in root, but then you wouldn't only delete the doc files, you would also select any folder in C:\, wouldn't you? Isn't it much easier, if you put a more or less large number of files into a folder?

Bye, Olaf.

 

[Olaf Doschke]

I can't tell you if this will work, I don't know what Word.Basic offers. If you got this code from Tamar's book, then refer to it.

Bye, Olaf.

 

[titoneon]

Yes i said i will put it on c:\tempw, sorry in my precious post i forgot to write
.ActiveDocument.Saveas("c:\tempw\"+alltrim(vendinfo.company)+".doc" but the question is this the correct syntax for VFP 5.0 .ActiveDocument.Saveas ?
oK I will move everything into C:\TEMPW, and then run it from over there, i will need the some ideas from you guys on how or where start the loop in the prg to get the first record from the table, save the file and then be able to email it and close that loop, it is proper to use a do while in these case or a FOR ... ENDFOR

Thanks

 

[Olaf Doschke]

> is this the correct syntax for VFP 5.0 .ActiveDocument.Saveas ?

Again said: What you do there is not VFP5 itself, you automate Word or WordBasic. The ActiveDocument sub object is a word subobject. If this works, depends on Word, not on the foxpro version.

I tried it for you: Word.Basic has no Documents sub object and no ActiveDocument, so this can't work. You will already know it errors. It errors saying there is no such object, doesn't it? It errors with that error, because, well, there is no such ActiveDocument. This error is not because you use VFP5, the same error will occur in VFP6,7,8,9, because Word.Basic has no ActiveDocument.

OK, let's not panic here. Take one step back and look at what you did so far with success:

PUBLIC oWordObj
USE VENDINFO && table that has two fields company and email
oWordObj=CREATEOBJECT("Word.Basic")
oWordObj.FileOpen(SYS(2004)+"\Letworldole.doc")
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

Now you will have to find out how to let Word.Basic save your doc now. If FileOpen opens a file, perhaps try oWordobj.FileSave("c:\tempw\"+alltrim(vendinfo.company)+".doc")

ActiveDocument.SaveAs is the correct syntax for Word.Application, but with Word.Application .FileOpen() and ww7_EditGoTo, etc.

So with this perspective you have to decide now, if you want to find out how you save a document via automating Word.Basic, or you restart from scratch using Word.Application.

I just quote from

http://www.office-archive.com/44-word/0b8ac053d4c4adcf.htm:

"Word.Basic is *so* old, that it's not easy to find much on it, these days." And that quote is from 2005. I don't find references on Word.Basic and what methods and functions it offers. You will also not find this in your VFP5 help, because it's seperate, it's automation of Word, it's not part of the foxpro language.

Bye, Olaf.

 

[TamarGranor]

I really would switch from WordBasic to the Word Automation object. This should replace your code:

oWordObj = createobject('Word.Application')
oWordObj.Documents.Open(SYS(2004)+"\Letworldole.doc")
oWordObj.Bookmarks("company").InsertAfter(vendinfo.Company)
oWordobj.Visible = .T.

Then you can save with:

oWordObj.ActiveDocument.Save(<fill in filename here>)

Personally, I'd save it in SYS(2023), the temp folder.

Tamar

 

[Olaf Doschke]

Don't forget to alltrim vendinfo.Company and you don't need to make Word visible, this can stay in background. It's of course ok for a check.

PUBLIC oWordObj 
oWordObj = CreateObject('Word.Application')
USE VENDINFO
DO WHILE NOT EOF()
   oWordObj = createobject('Word.Application')
   oWordObj.Documents.Open(SYS(2004)+"\Letworldole.doc")
   oWordObj.Bookmarks("company").InsertAfter(vendinfo.Company)
   oWordObj.ActiveDocument.SaveAs("c:\tempw\"+alltrim(vendinfo.company)+".doc")
   SKIP 1
ENDDO
oWordObj.Quit()
oWordObj = .NULL.

* now mail them
PUBLIC oOutlookObj, oMailObj
oOutlookObj = CreateObject("Outlook.Application")

GO TOP
DO WHILE NOT EOF()
   oMailObj = oOutlookObj.CreateItem(0)
   oMailObj.To = "you@somewhere.com" && after testing with your own mail address put in oMailObj.To = alltrim(Vendinfo.email)
   oMailObj.Subject = "testmail"
   oMailObj.Body = "Hi, this is a test mail"
   oMailObj.Attachments.Add("c:\tempw\"+alltrim(vendinfo.company)+".doc")
   oMailObj.Send() 
   oMailObj = .NULL.
   SKIP 1
ENDDO
oOutlookObj.Quit()
oOutlookObj = .NULL.

Don't send to alltrim(Vendinfo.email) before you have set in a real mail subject and body text, or you will really send a mail with subject "testmail" and the main text "Hi, this is a test mail", you won't want to do that. Make a test run with a secondary vendinfo dbf, you shrink down to one record with your own mail adress, perhaps.

Besides you can also use the HTMLBody property of oMailobj to put in HTML into the mail body and have much more formatting options. You have to experiment with this.

Bye, Olaf.

 

[Olaf Doschke]

Remove the oWordObj = createobject('Word.Application')within the WHILE loop, one Word instance is enough. And you may want to Close the document after it' saved, so the word running will not end up with all documents loaded.

Bye, Olaf.

 

[Olaf Doschke]

Just as a sidenote:

Excuse me for being blind, the mail client you want to use is in this thread topic, so there was no need to ask. If a thread get's lengthy the title get's out of focus, you just blindly click on the tek-tips list of threads with new posts.

Sorry for being so ignorant. Fortunately I gave the Outlook automation code anyway.

Bye, Olaf.

 

[titoneon]

Hello Everyone,
Here is what i have done with the help you offered.
This is the code in the prg file, i will explain after the code "two things that are happening", hope you can help me out as you guys already did.

PUBLIC oWordObj, loOutlook
USE VENDINFO
go top
*** this will open an existing word document
oWordObj=CREATEOBJECT("Word.Basic")
oWordObj.FileOpen ("c:\tempw\Letworldole.doc")
*oWordobj.AppShow && this will show the file
c_company=vendinfo.company
oWordObj. ww7_EditGoTo ("company") && company is bookmark name created in word 2003
oWordObj.Insert (c_company) &&vendinfo.Company this is field in a the table that has the data that will replace in word 2003 document the bookmark for the first record.
*oWordobj.AppShow && this could show the application or word document open on the scren
oWordobj.FileSaveas("c:\tempw\"+alltrim(vendinfo.Company)+".doc") && this will save the document to such a folder

**** from here down to email the saved doc as Olaf Referenced.
loOutlook = CreateObject("Outlook.Application")
loMailItem = loOutlook.CreateItem(0)
loMailItem.To = alltrim(vendinfo.email) &&"recipient@somewhere.com"
loMailItem.Subject = "testmail"
loMailItem.Body = "Hi, this is a test mail including an attachament"
loMailItem.Attachments.Add("c:\tempw\"+alltrim(vendinfo.company)+".doc") && Saved doc into that folder.
loMailItem.Send()
oWordobj.FileClose()
********** end
*oWord.Application.Quit && for ("word.basic")
*oWordObj.FilePrint && for ("word.basic")

Ok all this code is doing all i want.

"two things that are happening"
1-when i open microsoft outlook to check if i received the email, yes everything is there as expected but when i open the attached document as print preview or if i print the attachment, next to the bookmark that was replaced by the vendinfo.company value, this error is printed too as part of the document " Error! Reference source not found" Does anyone know what i can do about it ?

2-The other thing each time the command loMailItem.Send() is issued it pop up on the screen this window that show this "Microsoft office outlook" then "a program is trying to automaticly send e-mail on your behalf. Do you want to allow this ? if this is unexpected, it may be a virus and you should choose No."
Then it is a options button commands Yes No and Help, of course the email won't go until the user Hit the Yes, is there any way to by pass this, otherwise the user will have to hit Yes as many emails are need to be sent.

3- I need to create a loop with a Do while !eof() for processing all the records from the table my question is should i put the Do while below the line oWordObj.FileOpen ("c:\tempw\Letworldole.doc") ?

Thanks a lot
Ernesto