Sending Email to Multiple Clients

[recyan]

Objective : Automate Mail Marketing Emails to Prospective Customers.


Details : The Emails will have particular content ( Pre-decided ) in the Body & have attachments ( Pre-decided ) relevant to that Customer, rather the Products being handled by the Customer.


Likely Entities involved in the Database :
Primary Entities :
CUSTOMERS
INDUSTRY
PRODUCTS




Additional Inputs :
Email Body Content
Catalogues
Other Attachments


Further Details :
1) A CUSTOMER belongs to One & Only One INDUSTRY.
2) An INDUSTRY can have Many CUSTOMERS.
3) An INDUSTRY has Many PRODUCTS.
4) A PRODUCT belongs to One & Only One INDUSTRY.
4) A CUSTOMER may be dealing in One or Many PRODUCTS.


At a basic level, on Selecting a Particular PRODUCT, the application should automatically send Particular Pre-Formatted Email along with relevant Catalogues & Attachments to all CUSTOMERS handling that PRODUCT.
Additionally, we should be able to update the CUSTOMERS to whom Mail has been sent & Provision should be there for Recording the Response of CUSTOMERS to the Email, if any ( this part can be manual ).
---------------
Is the Objective possible in Access or are there better tools / readily available applications, than Access to do it ( if Yes, can I request a few names )?


Thanks

[pbaldy]

This can get pretty involved, but you can start here:

http://www.granite.ab.ca/access/email/recordsetloop.htm

[recyan]

This can get pretty involved,

If pbaldy say's that, I think, I better stop thinking about it.
Thanks for the link.

Thanks

[pbaldy]

Oh,I wasn't trying to discourage you from doing it, just pointing out that it's specialized enough that you won't get a single answer that solves the problem. You'll need to work through it step by step.

[June7]

Giving up already, recyan!! Sending multiple emails isn't really complicated, certainly not for your level of expertise. What could get involved is customizing each email's body content and attachments.

As far as I can see, Access is ideal for this process. Probably the biggest hurdle is getting the data structure right. But I have confidence in you!

[recyan]

Oh,I wasn't trying to discourage you from doing it, just pointing out that it's specialized enough that you won't get a single answer that solves the problem. You'll need to work through it step by step.

Sorry Paul, if my response came across, as if you were discouraging. That would be the last thing on my mind. Directly or indirectly, you have helped so many people, that it would be difficult to put a number to it.
Your response gave me a sense of dimension to the project & I realised that it was not going to be a cakewalk & would require sustained effort.
As you rightly pointed out, I'll need to work through it step by step & that's the path I will take.

Giving up already, recyan!! Sending multiple emails isn't really complicated, certainly not for your level of expertise. What could get involved is customizing each email's body content and attachments.

As far as I can see, Access is ideal for this process. Probably the biggest hurdle is getting the data structure right. But I have confidence in you!

Coming from you, it's very humbling.
Expertise & Skills, I have, but minimum.
Only thing I have going for me, is my Avatar.
Can't chew, Can't swallow & Can't leave.
Hope this thing doesn't leave me with a chewed out brain, what with Forms & VBA involved.:-D
--------------------
Am posting the Rough ERD, that I have in mind, in the next post, for an opinion.

Thanks

[recyan]

Just as an easy reference, I am re-posting a few details of my first Post, along with the ERD.

1) The primary Entity of this application is the CUSTOMER.
2) A CUSTOMER can belong to One & Only One INDUSTRY.
3) An INDUSTRY can have One to Many CUSTOMERS.
4) An INDUSTRY can have One to Many PRODUCTS.
5) A PRODUCT can belong to One & Only One INDUSTRY.
6) A CUSTOMER may be associated with One to Many PRODUCTS.

tblCustomersIndustry -
1) Is this redundant ( due to the association of CustID field with IndustryProductID field in tblCustomersProducts ) or does it make sense to have it to simplify things ?
2) Is there a chance of Circular Reference ?

[Edit] : Just a couple of dummy Data :

Customer    Industry    Products
ABC    Telecom    Cables
DEF    Chemicals    HCl
DEF    Chemicals    H2SO4
GHI    Chemicals    HCl

[/Edit]

Thanks

[June7]

Think I see circular relationship, which can have issues. I don't think tblCustomersIndustry is needed. Customer associates with product, product associates with industry, therefore customer has an indirect link to industry through product.

Also, if product can be associated with only one industry, tblIndustryProduct is not needed. Put IndustryID foreign key in tblProducts.

tblCustomers
CustomerID (PK)

tblIndustry
IndustryID (PK)

tblProducts
ProductID (PK)
IndustryID (FK)

tblCustomerProducts
CustomerID (FK)
ProductID (FK)

[recyan]

Hi June,
Thanks for the inputs. At first glance, all of it, appears to be the way to go.

Thanks