Dynamic E-mail senting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for ideas or even solutions to somewhat unique problem.

We are currently running a database that pulls data from many different odbc
sources in our business and collects and reformats the data. We then create
various queries, reports, and spreadsheets that are sent using the send
object and MAPI methods(spreadsheets as attachments) to many different people.

The problem is, whenever someone needs to be added/removed/goes on holiday,
we need to find the e-mail in the code (in all places, in case of an address)
and then edit it. This can be a big problem when the boss that receives
approximately 20 e-mails a day from our program goes on holiday and doesn't
want these automatic e-mails clogging up his system.

We thought of creating a table with the e-mail adresses in them and yes/no
fields for each e-mail. Unfortunately, with over 300 either weekly or daily
e-mails there simply wouldnt be enough room and the table would be hard to
work with.

Does anyone have any ideas of what we can do? I have moderate access
programming knowledge and can understand/use most coding.

Thank you very much in advance for any help you can offer.
 
Kat,

This kind of dynamic data (and in fact, any type of dynamic "data") should
NEVER be hard-coded - ever.

Store this kinda stuff in a table. The complexity is just a matter of the
right user interface design you use to manage it. You really don't have much
choice here; the email addresses need to be stored in a table, and you'll
just have to come up with a design that minimises the complexity. This will
probably involve quite a bit of complex design and coding, but most systems
that hide complexity from the user are inherently complex to design.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thank you Graham for your insight, however I think I didn't quite explain
properly what the problem actually is.

I agree at this point these e-mail addresses probably belong in a table in
some way shape or form. The problem is, how...

This system is completely automated. There are no "users" as such. A
scheduler runs the database at 3am and in doing so fires the AutoExec that
starts the process of pulling, updating, reformatting, and sending that takes
approximately 5 to 7hrs to complete. I am talking about a huge amount of
data crunching here(and some very slow systems that we have to connect to).
There is no personal data involved and the people receiving the output data
from the database are not currently held in the database at all.

Therein lies the problem. An example would be... email one might go to
person 1 2 and 3 while e-mail two might go to person 1 and 4 and e-mail three
might go to person 3,4,5,6,7,8. There isn't anything I can think of to query
on... and therefore step through the recordset to apply the e-mails
dynamically. There is also no relationship between person 1 and 2 and 3.
Some can be put in loose groups however some of that group might be on an
e-mail when the rest are not. Currently I would estimate there is about 300
e-mails with up to 20 addresses that go out on at least a weekly basis.

I am stuck and looking for suggestions on where to start that "complex"
design.

Thanks again for any help you maybe able to give.

Kat
 
Kat said:
Therein lies the problem. An example would be... email one might go to
person 1 2 and 3 while e-mail two might go to person 1 and 4 and e-mail three
might go to person 3,4,5,6,7,8. There isn't anything I can think of to query
on... and therefore step through the recordset to apply the e-mails
dynamically. There is also no relationship between person 1 and 2 and 3.
Some can be put in loose groups however some of that group might be on an
e-mail when the rest are not. Currently I would estimate there is about 300
e-mails with up to 20 addresses that go out on at least a weekly basis.
Click to expand...

You have three tables. Email Type. Email recipient, with name and
email address, and Email Recipient Type of Email. That's two forms
and a subform.

So once you've done the processing required for email type one you
call a routine which uses a query based on Email Recipients and Email
Recipient Type of Email. So now you have a recordset of email
addresses.

Now you loop through that recordset sending off your emails for type
one.

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Tony Toews said:
So once you've done the processing required for email type one you
call a routine which
Click to expand...

BTW you pass the record id of the Email Type that you want to send to
the routine. Thus it's a generic routine that you can call
throughout your lengthy process routine as you are done each email
type.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Thanks ever so much for your help. Exactly what I was looking for. Working
on the code now but it looks like although it will mean a lot of work up
front, it will save TONs of time in the future!

Kat
 
Back
Top