Email list of people

[Treas]

Hi,

From a list box I select email address of people to receive a short email. What I want is to have a command button that will send each person an email.

So email person 1, email person 2 etc. until end of the list.

My coding skills are very basic and I've never used a loop before so go easy on me!!

Thanks.

 

[David R]

Sometimes it's just a matter of knowing where to look. Search the archives for 'loop email' and limit to posts by 'Talismanic' if need be. Here's one example to get you started: http://www.access-programmers.co.uk/ubb/Forum1/HTML/004240.html


HTH,
David R

 

[Treas]

Thanks David

 

[Treas]

Ok, I'm on the right track thanks to Talismanic's code.

BUT not quite there. Database consists of fairly detailed records split across tab pages. One page holds the list of all those who need to be notified about the particular record. I'm doing this by using an unbound list of all email addresses in the company, clicking on the list copies the entry into the distribution subform.

However, using the code below, the distribution list on ALL records gets mailed, whereas I just want it to be the current record, but have no idea what to add or where!

Thanks for all help so far.

Here's the code:

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("tblsend query")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , "Subject", "Message test", no

rsEmail.MoveNext

Loop

Set rsEmail = Nothing

 

[David R]

Oh, I thought you wanted to mail a list of people, not just the current record.

Talismanic is again the Man when it comes to email. Try 'click sendobject email' with his name in the user box.

HTH,
David R

 

[Jerry Stoner]

Or use the code youve got but make a query based on "tblsend query" with a yes no box and check those records you dont want sent. Put 0 in criteria for the query. You could also write code to auto check the record once sent if you always only send it once.

 

[Treas]

I can't find anything to match exactly what I want to do and don't have the knowledge to adapt any code that I found.

I didn't make it quite clear. I don't want to send any object, but I want to notify just the people on the distribution list for each particular record.

The database keeps track of deviation issues in production and the people to be notified will vary with each record according to the issues involved. So, the QA Department decide who should be notified (clicking a list puts the names into a subform on a tabbed page). Then I want a button to email just those people that they have chosen saying that Deviation no.x has been raised and requires your attention.

The actual text part of the mail etc. is fine, but at the moment, I'm mailing everybody who was ever chosen on any record.

Sorry for being so long-winded, hope I've explained it better this time.

Thanks for all help so far.

 

[BukHix]

Treas,

Talismanic wishes he knew half of what I know about sending emails from Access. Notice he does not post here anymore. This is because when I joined the forum and started posting he stepped aside and went into hiding.

I think you find your answer in this thread:

Topic: Send a single record via e-mail

The code is a little confusing but I broke it down to make it easier to digest. Let me know if I am on the right track with that code. The body of the email will look like this:

Date: 10/24/2001 1:19:00 PM
Contact: Aurthor Dent
Employee Name: Marvin P. Robot

Conversation: This is the text that was entered into the memo box of
my form.

All this text is pulled from form fields, the Contact name and the Employee names are from drop down boxes and the Conversation is from a memo field. In my example the Send To is not pulled from a combo box but it could be modified to do that pretty easily. Let me know if I am on the right track and I will help you do that.

 

[Treas]

And modest too!

No, that's not exactly what I want either.

Main form contains RecordID Number, with five tabbed pages containing subforms. One of the subforms is a distribution record. I have an unbound listbox with all the company email addresses and clicking on an address puts it in the "Distribution" subform.

Then I want to click a button and mail each person on the list for that particular record. The text of the mail etc. is not a problem, but sending the mail to just those on the list is where I'm stuck.

So, let's say Pat and Tom are to be notified about Record A, but Mary and Joan are to be notified about Record B. The code I have at the moment mails all four people (I have just two records for testing).

Clear as mud!

Thanks

 

[David R]

Pardon me, oh great BukHix. What I meant to say is that Talismanic was the Man and that you now are the Man! So to speak...

Groveling,
David R

 

[BukHix]

David R,

Thank You for clarifing that point!

Treas,

Ok so how are you selecting the people? You could do it from a listbox, using checkboxes or multiple comboboxes. Clarify that point for me and I think I can help you wrap this up.

 

[BukHix]

Oops I missed this from your previous post:

I have an unbound listbox with all the company email addresses and clicking on an address puts it in the "Distribution" subform

Put semicolans at the end of each name that you send to the distribution list and then use the distribution list as your source for the email addresses.

    DoCmd.SendObject , , , Me.DistubutionTextBox, , , _
        "Optional Subject Here", "Optional Message Here", False

You may need to alter the code that fills you distribution list to concatenate the semi colan at the end of each email. Something like this strEmail = Me.Email & ";"

[This message has been edited by BukHix (edited 04-12-2002).]

 

[BukHix]

Did we lose ya?

 

[Treas]

Thanks guys. I'm now kicking myself (though I haven't had a chance to actually try it yet!) Can't see any reason for this not to work. I had been putting each entry into a new record on a sub form. The only advantage was that I had a tick box that was true when the mail was sent, so a new person could be added anytime and the same button used, but I can live without that!

Gotta go home now, but will try it first thing on Monday.

Thanks again.

 

[Treas]

Thanks for all the help. Finally got back to this today!

Another problem!

Click list box - names go to distribution box, click button - mails sent, that's fine.

Do it again on same record to test - still workign.

BUT if I go to a new record, it doesn't send. I have to shut down access, re-start and then away it goes again.

I guess I have to refresh something, but what and how?????

 

[BukHix]

Hmmmm you are not getting an error or anything? Just non action? Does the system become unstable at all?

Post your code so I can peer at it again. It has been I while since I first worked on this with you and I must of deleted the demo code I tried out.

 

[Treas]

No, not getting an error message at all. Code is just

DoCmd.SendObject , , , Me.Dist], , , "Subject", "Messagebody", no

 

[Treas]

Lost the bracket, that's not the problem, I DO have it in the code

me.[dist]

 

[Treas]

Email button works fine on other machines, but not mine. Will only send mail once without closing access and starting again.

Can't see any difference between outlook or access settings between PC's.

Any suggestions?


Thanks!