Sending E-mail based on String found in Text file

[lucour]

I have created a database in Access to track file transfers we do for the bank on a daily basis. Basically we send 5 files, and then the bank sends us 5 validation files. These are text files. These validations get e-mailed as attachments to various users. These text files are kept in a folder called C:\Banktran.
I would like to be able to open each file, search for the customer number (ie: 900100230), and based on the customer number found then e-mail this file as an attachment to a certain recipient.
Is this possible??

 

[The_Doc_Man]

It is possible but not necessarily very pretty. How good are you in VBA programming? Because if the answer is "not very" then I have to revise my answer to you from "possible" to "forget it."

 

[lucour]

Hi Doc_Man,

I don't really care if it is pretty or not, just as long as it works. I have been doing VBA coding for the past year or so and I feel pretty comfortable with it. Anything you can pass along would be great. Thanks !

 

[The_Doc_Man]

Well, you asked for it. If you have full Office available, then you can perhaps use Word to help you with the search.

This is an overview of how to do the search part. I've never tried to do the e-mail part.

OK, create an Application object. Instantiate it as a Word document. If you are not sure, look up in Access Help how to open application objects. The examples are pretty good. That object will come up as a Word instance for which no documents are open other than the default "New" document.

Open the filename you wanted to find. Look in Word's help files this time. Find the Vis. Basic reference for Word in the Contents list, then open that and in the index section, look up the Open method to get the parameters.

If this is truly just a text file, all you want is to open the file with Read-Only set True, with ConfirmConversions set False, with AddToRecentFiles set False, and with Format= (probably) wdOpenFormatText (unless you are using UNICODE files, in which case it is wdOpenFormatUnicodeText.) The Open help lists the common wdxxxx constants for the formats.

Make this new document your active document. See the .Activate method. Remember that the .Open method in Word returns a document, so if you had a document variable set to receive the results of the .Open, you already have a pointer to the document.

Create a range variable that takes in the entire active document. (set myRange = {document}.Contents will do that for you.)
Now do a myRange.Find with the myRange.Text = "{the thing you wanted to find in that document}"

If the given number is in the document, you will know that because myRange.Find.Found will be True. Copy the .Found item to any boolean.

Regardless of whether you find it or not, be sure to close the active document without saving anything. That would include parameters SaveChanges = wdDoNotSaveChanges. In that case the other parameters for the .Close method are not significant.

OK, if you copied the .Found to a boolean, you now know if the file in question contains the reference that would cause you to trigger the e-mail. As to how that is done, I believe there is an option in the command button wizard to create and send mail through .SendObject. Try looking that up, too. Also, search this forum for other threads related to sending e-mail from Access.