e-mail goup

[steve111]

hi ,
at present I am using this code to send my emails it looks at a field called e-mail address.

 [B][FONT=Calibri][SIZE=3]=[E-mail A[/SIZE][/FONT][FONT=Calibri][SIZE=3]ddress] & IIf(Nz([E-mail Address])<>""," [" & [E-mail Address] & "]")[/SIZE][/FONT][/B]
 [CODE]
  
 I would like if possible to use a group email code as I have about 6 people that could receive this e-mail
 is it possible to create a  group ? an how would you call it
 if not how can I modify the above to put more that one person on the  it 
  
 thanks 
 steve

 

[AOB]

I do something similar in one of my db's - here's what I do

First, I have a table of e-mail addresses, for all potential recipients of reports. Each record in this table has a unique RecipientID (can be an AutoNumber)

Then, I have a separate table of reports - for each potential report (if you only have one report, then this isn't necessary - but if you plan on adding further reports, it's no harm to add it now, with just one record, so that your db becomes easily scalable further down the line) Each record in this table has a unique DistributionID (can also be an AutoNumber)

Finally, I have a table of distributions which joins everything together. Each report has a distribution, which references one or more records in the recipients table (along with their e-mail addresses)

Each record in this table therefore has both a DistributionID and a RecipientID (composite primary keys - the same DistributionID can be repeated across multiple records, as can the same RecipientID - just not the same combination in the same record)

Makes everything easy to maintain and you only have to have one record per recipient (even if they are part of multiple distributions) so it's nicely normalised. You can write a simple query to return the list of e-mail addresses for each report based on the ReportID (or some other field in the reports table, i.e. the report name etc.)

FYI - I would avoid giving fields names like [E-mail Address] - spaces and non-alphanumeric characters will eventually cause problems. If it were me, I would use something like [EMailAddress] instead...

 

[steve111]

hi ,

I have attached a sample of my form which is called NCR
I need this form to goto 8 people
I have put 3 e mail address in the employee table of which will be sent this form
could you please explain a boi more how I could set this up as you have described

at present I can only get it to one e-mail address if I put that name in the address field

use the buttom "QUERY " on the form and enter "3" when prompted for a number

thanks
steve

 

[AOB]

You have a textbox ("E-mail Address") which is bound to a field ([E-mail Address]) in a table ("INPUT TABLE2")

(NB Would strongly advise against using the same names for form controls and field names, as well as the previously mentioned use of non-alphanumeric characters (";", "/", ","...) and spaces in said names - these are tricky enough without making things even more difficult for youself!)

That textbox can only ever hold the field value from one record at a time

Therefore, only one e-mail address at a time, assuming that's the way you use that field.

If you want multiple addresses, you need to pull them from multiple records; hence you need a query.

I would suggest switching your textbox (single-value control) for a listbox (multi-value control). You can bind it to your query. Then you can display an arbitrary number of addresses based on the criteria for your query.

Your e-mail addresses appear to be stored in your Employees table - but the control source for the form is the INPUT TABLE2 (which does have a field for e-mail addresses, but which is blank for all records) So I don't see how you are ever going to get anything into that control, apart from keying it in and saving it? In which case, what's the point of the field in the Employees table?

I think you need to seriously look into normalising your data - use several small tables of 'like' data rather than one huge one with everything. Create relationships between them and use queries to pull different pieces of information from each one based on what you require.

Is there any relationship between the E-mail Address field in Employees and that in INPUT TABLE2? Or do you just free-type the address into the textbox and save it to the table? If so, you could just separate each e-mail address with a semi-colon ";" and that would allow you to send to multiple addresses.

But that is not a very efficient way of doing things...

 

[steve111]

HI,

Thanks for your advise
the reason I have the e-mail called e-mail address is because I used the same format as the northwind database and did not know any better . but will change it

the reason the e-mail was in the input 2 table was I was trying to fond a way to do what I needed , but it can be just in the employees table

I could create a table /form with all the people i need on the circulation list ( this would enable it to be altered if anyone leaves or gets replace

how would i call up that list to go onto the e-mail circulation automatically when i press the send button

thanks

steve

 

[AOB]

Have you tried adding multiple addresses to the textbox separated by semi-colons?... It's not elegant but it should 'work'?

If you want to go the more efficient route - how would I identify which e-mail addresses to be included from the Employees table?

 

[steve111]

yes I can do that but like you said it is not good and I want to be able to change them in a form if possible

could a table called report/circulation list be done and the fields called NCR , ORDERS, ETC
all the names under the NCR field would then be sent by the send button on the NCR FORM

but it is how we get those names from that table into the e-mail circulation

thanks
steve

 

[AOB]

Have a look at this - by no means a finished article but something to get you started

Have added a table called tblDistributions - just two fields : NCR and Employee

Have also added a listbox at the bottom of your form which shows the names and e-mail addresses for the distribution list specific to each NCR

And a query (qryListboxContacts) which is used to populate it

Notice how as you move from record to record (NCR to NCR) the listbox updates with the contacts as per the tblDistributions table

All you need to do is add some functionality to be able to add or remove contacts for each NCR (or you could do it manually by adding / removing records directly into the table) And adapt your macros so that the e-mail addresses are taken from the listbox rather than the textbox.

I don't use macros whatsoever so I don't know how it is doing it currently - I would do it with VBA in modules - but it's your database so it's your choice...