Automating Email Delivery 2

[romh]

Hi. I have searched for something similar, but have not found any similar situation and I know somebody has done this before. Here it goes.
I have an Access application written with extensive VBA code behind every form. I have a WIndows 2000 Adv Server network with database split FE / BE. I need to be able to send out an email from Access once certain criterias are met. I figured out how to email from VBA onto Outlook.
But I do not want to send the email from the workstation, but instead from the server where the back end resides. How is this possible in Access? What do I need to trigger a response from the back end, and send an email from the server? I think this would be possible with SQL server by using triggers. When a table is updated, I could run the email code.
I really wish to keep Access for a little longer. I do plan on upgrading to a client server database, but for now, can anyone give me some hints or ideas? Maybe I could wait until the end of the day, and send emails at the end of the day. But how can I make the server accomplish these tasks from Access at certain times or at certain conditions?
Thanks alot

 

[grnzbra]

Well, since there was no answer, I'll jump in with my 2cents.

I used to work on a system with Access FE/BE which had a timer running. At 5:00AM every weekday morning it would connect up to Bank of New York's ftp site and download 5 files of information about the previous days activity including a large table of info about every stock on the NYSE and the AMEX. As these were coming in, reports would be generated (reports that needed only the first table were done as soon as that was in, etc). Then the SendObject command was used to send the e-mails.

All this took place on the BE and all had the BE as the origin. The trigger was the timer in the back end. Don't know what kind of event you want to trigger the e-mail, but if it must come from the front end, perhaps you could set a timer to run every minute and check a table field or something that would receive the trigger from the FE.

 

[romh]

So you always had a form running on the back end with a timer event. Correct? At lets say, 5:30pm everyday, I could check a table and send out all the emails. Sounds pretty good. How much memory does a constant timer event take up on a server. Because the timer will have to be set to check for the time at about every minute interval.Thanks. Great Idea

 

[romh]

So you always had a form running on the back end with a timer event. Correct? At lets say, 5:30pm everyday, I could check a table and send out all the emails. Sounds pretty good. How much memory or cpu time does a constant timer event take up on a server? Will it be noticable? Because the timer will have to be set to check for the time at about every minute interval.Thanks. Great Idea

 

[grnzbra]

I don't know how much resources a timer uses, but we didn't worry about it too much. I expect that it's only a few lines of code in a loop.

The timer ran every so often, but, in our case, I don't think it was every minute. It might have been 10 or 15 mins. The general operation for it was Wake Up > Is the current time after the start time for the operation and befor the end time? > make FTP connection > Is file available? > download file > check what reports can run with only this file > generate report(s) > save as .pdf file > generate e-mail with SendObject and send to the people who are to receive this report. Repeat process for next file. etc. If any of the questions were answered "No", it would go back to sleep.

Set the timer interval for 60000 (60,000 miliseconds = 1 minute) and the code for the timer event would be:

If Format(now(),"hh:mm:ss&quot = "17:30:00" Then
Call ProcessEmailSub
End If

And then ProcessEmailSub would be the code to process the e-mail.

 

[grnzbra]

better make that time check

If format(now(),"hh:mm&quot = "17:30" then

 

[romh]

Perfect. One more thing. How did you get to save the reports as a .pdf file. Does this happen automatically when you purchase the full blown Adobe Acrobat. Because with the Reader, you can't. Tell me a little more about that please.

 

[danvlas]

Sending from the back-end needs:

1. Access + database running (that is OPEN) on the file server
2. Outlook running on the file server.

Both of them eat resources. How much...that depends on the file sizes and other factors.

I have been mailing reports daily for 2 years from the front-end at 16:04 every afternoon and I had no problems. Except I never turn off the workstation.
Task Scheduler just opens the front-end 2 minutes before sending time so that I can cancel sending if I want to.

I think you could do that with your program and it could open, send the messages immediately, then go back to sleep (close)...

But I wouldn't do that on the file server. It may be in another office, the office may be locked...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[grnzbra]

I don't remember how the system saved the reports as .pdf files. Never had to deal with that; that part always worked.
We always had Outlook, Access and the database running on the file server and it never seemed to cause any problems.

 

[romh]

Thanks fellows. Very helpful.

 

[6055IO]

I would like to send an attached .pdf format document (instead of a snapshot format document for which I succeed) from an access report. How is it possible?

thanks for your help

Olivier

 

[romh]

Great question olivier. I still don;t know the answer, and I need to send .pdf's as well. If you buy the full Adobe pdf program, then that will do it. The step that I don't know how to do, is have a cost efficient (even better if its free) manner in which to convert an Access report into a pdf file.

 

[GoDawgs]

I don't know of a way that you can create .pdf's without having at least Acrobat Distiller...it's not too expensive and well worth it in the end. Hope that helps.

Kevin

 

[romh]

I went to Adobe wesite and the Distiller Server cost $5000. Can I transform access reports into .pdf files with the Acrobat Professional version that cost $500.

Thanks

 

[GoDawgs]

Yeah, you can...I think you can with the Standard edition as well ($300/$350 I think, can't remember).

 

[tcalbaz]

Guys,

I'm curious too about saving in the pdf format.

I am wondering about some of the freeware and shareware PDF file solutions out there.

I did a search on google for "freeware pdf printing" and got blasted with quite a hitlist of selections.

Maybe one of them will work for you too.

Ted

 

[romh]

Yeah maybe one of them will work. I have to check those out.
Thanks.

I think the pdf way will be the easiest manner of emailing access reports. I wonder if there's a way of comverting the report to pdf through VBA. I want to be emailing reports everyday without any user interaction.

Thanks

 

[tunsarod]

I bought a copy of 'Jaws PDF Creator' from

http://www.jawspdf.com)

It was cheap, works fine and on installation creates a macro that enables Word to treat JawsPDF as another printer. i.e. to create a pdf document you (print) to the JawsPDF printer. I imagine therefore that you could publish your report to Word and using Automation issue the relevant print commands to Word from Access.

Rod