Sending data from form to multiple emails

[GeorgeB]

Hello everyone,
the last time I used Access was 2000 so I am always 12 years behind and very rusty,

anyway I am trying to create a database with a list of documents names, every few month we have a new issue of every document that we need to send to multiple emails, I finally created a form that contains all the data but I can't send it by email to the emails in the form

It's hard to explain it so I attached the database



Thanks in advance for you help and support getting me back to track

[Stingaway]

Without actually looking, as I am currently on a tablet... If you just want to send a list you can either export a query, table or even print preview a report and send it via email.

[GeorgeB]

Thanks Stingaway for you reply,
the thing is that the data are generated in the form, I couldn't extract the emails from the tables or queries

[Stingaway]

If the data is generated on a form, it should be able to be generated in a report.. In which case it can be sent directly through email as a PDF or an XPS...

[GeorgeB]

I attached a snapshot to make it easier to explain

I want to send the latest issue to all the emails in the list

[Stingaway]

So is the latest issue a file attachment or is it the data in the bottom of that form?

[GeorgeB]

Yeah, it is a file attachment, but I don't mind to attach the file manually, what I really want to do now, is adding all the emails in the list to the TO: field of an email

[Stingaway]

Ok finally got a look at this. It's going to be a lot trickier based on the fact that instead of normalizing the data between CPs and Documents and then having a separate CP-Doc related table you have a LOOKUP inside the Documents table.

Ideally what you would want to do in this scenario is isolate independent multiple relationships (better known as fourth normal form). Once you have the one to many/many to many relationships (ie CPs to Doc Nums) handled via separate tables you would then need to create a query to list all CP numbers based on the document number selected in your form.

You can do this by creating the query that references the new CP - Doc relationship table and then filter it by using =[Forms]![Documents]![doc_name] as your criteria.

Once you have that result set, there are a few ways to send the email - the one that comes to mind is that you can pass it to a string variable in VBA that you then would use as the "TO:" field.

There may be other ways around it, but as I said the issue is going to be complicated by the original design of the database itself.

Hope this helps...

[GeorgeB]

I really appreciate you help and support

I am going to create a new database to avoid the complexity I am having now

so you suggestion is

1) Document Table ( all the document numbers and name)
2) CP Table (with all the contractor numbers and email)
3) Issues Table ?? or Issues should be included in the Document Table ??

I know that it's annoying to explain something easy to someone and he still doesn't get it, I am apologizing in advance

[GeorgeB]

Hi Stingaway

I created my Try2 following your advise but still can't get it working, can you please if you have time recommend what type of modification do I need to do

Thanks

[Stingaway]

You are very much on the right track here - you just needed another table and some actual data defined. I am attaching a slightly modified version of yours (changes some table names for clarity and added some lookups on the tables to make them easier to populate quickly. (Ideally that should be form driven, but for purposes of this example I thought it might be useful to you.)

Hope this helps...

[GeorgeB]

Thanks very much for your help