Trying to send email to a group of contacts in a table in Access 97 1

[nalbiso]

Please help!

I am trying to email a report to a group of contacts in a table.

But when I use the following code:

Private Sub cmdsendjobopps_Enter()
DoCmd.SendObject acSendReport, "jobopps", acFormatRTF, Me![Email Address], , , "Current Job Opportunities for Classified Personnel", , -1
End Sub

Only one of the contacts shows up in the Send To: box.

I would appreciate any help!
[sig][/sig]

 

[DougP]

Are all of the e-mails in this one text box or is there only one and you have to open a recordset to see the rest.

You have to "March" through a list and build it up using a "for next" loop

this is a rough draft
"somenumber" is going to equal what ever you are using
if its a record count then use rst.recordcount
if its a combo box then use combo1.listcount "I think"

dim Emails as string , a, somenumber as integer

for a = 1 to somenumber
Emails = Email & rst!Email & ";"
next

DoCmd.SendObject acSendReport, "jobopps", acFormatRTF, Emails, , , "Current Job Opportunities for Classified Personnel", , -1
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[Guest_imported]

Somthing to consider, most business practices frown on putting all the recipients into the &quot;send to&quot; field. Each of your reciepients will see all the others. I would recomend considering sending one email for each record.

Just a thought!

John [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[nalbiso]

The reason why I want all of the recipients listed is because this information is not confidential so the manager doesn't care if the recipients see all of the names of other recipients. In fact, he would prefer it that way so in case someone within a department didn't get the information and was supposed to, possibly another recipient would notice that from the recipients list and bring it to our attention and that person can be added.

What this project is doing is taking the place of mailing paper copies of a job opportunities bulletin. All of the recipients are within the District that I work for and agencies throughout the community. Nothing being sent is confidential so it's available to anyone who wants it.

The main reason that I want all recipients listed is so that when the clerical hits the send button, he/she only has to edit and send one message to out and reach all 225 contacts. The constraints on this project is that it runs quickly and efficiently and if a clerical has to type the same message 225 times unnecessarily, as you can imagine, that is time wasted and I do not meet the constraints of the project.

But thanks anyways. [sig][/sig]

 

[Guest_imported]

did you get the example posted by DougP to work? That is the best way to go!

John [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[nalbiso]

I tried to post something earlier, but it didn't come out so I will try again. I am pretty new at the VB stuff, so I am going to need a little more explanation. Maybe I am just extremely confused, but why use &quot;somenumber&quot;? Doesn't that limit the number of records in the recordset?

I was trying this code:

Private Sub cmdsendjobopps_Enter()
Dim db As Database, rst As Recordset
Dim strSQL As String

'Return reference to current database
Set db = CurrentDb
strSQL = &quot;SELECT [Joblist Email List].[Email Address]FROM[Joblist Email List];&quot;
Set rst = db.OpenRecordset(strSQL)

rst.MoveNext
Do While rst.EOF = False
'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, &quot;jobopps&quot;, acFormatRTF, Me![Email Address], , , &quot;Current Job Opportunities for Classified Personnel&quot;, , -1
Loop

If rst.EOF = True Then
rst.Close
Set db = Nothing

End If

End Sub

But I still end up with only one contact in the Email Address column and it is still creating individual emails for each record.

thanks

[sig][/sig]

 

[LarryDeLaruelle]

A less sophisticated method that would work is to create a mail list in Outlook to include all the intended recipients. Then simply use that mail list as the recipient in your code. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[jagilman]

I think this is it.... here is your code

rst.MoveNext
Do While rst.EOF = False
'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, &quot;jobopps&quot;, acFormatRTF, Me![Email Address], , , &quot;Current Job Opportunities for Classified Personnel&quot;, , -1
Loop

you need to .MoveNext befor you loop back to the Do line
try this.

rst.MoveFirst
Do While Not rst.EOF
'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, &quot;jobopps&quot;, acFormatRTF, Me![Email Address], , , &quot;Current Job Opportunities for Classified Personnel&quot;, , -1
rst.MoveNext
Loop

[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[nalbiso]

I did try putting rst.movenext before the loop and it still only gave me one contact in the record set.

But thanks. [sig][/sig]

 

[jagilman]

I think you missed the error. Your loop does not have any move command within the loop that I can see. [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[nalbiso]

What I meant is that I tried the code again with .movenext before the loop (see code below) it still didn't work:

Private Sub cmdsendjobopps_Enter()
Dim db As Database, rst As Recordset
Dim strSQL As String

'Return reference to current database
Set db = CurrentDb
strSQL = &quot;SELECT [Joblist Email List].[Email Address]FROM[Joblist Email List];&quot;
Set rst = db.OpenRecordset(strSQL)

With rst
.MoveFirst
Do Until .EOF
'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, &quot;jobopps&quot;, acFormatRTF, Me![Email Address], , , &quot;Current Job Opportunities for Classified Personnel&quot;, , -1
rst.MoveNext
Loop
End With

rst.Close


End Sub


Am I still misunderstanding you?

Thanks! [sig][/sig]

 

[jagilman]

Sorry about the mixup. The edited code below will work

Dim Names As String
With rst
.MoveFirst
Names = ![Email Address] & &quot;;&quot;
.MoveNext
Do Until .EOF
Names = Names & ![Email Address] & &quot;;&quot;
.MoveNext
Loop
End With

'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, &quot;jobopps&quot;, acFormatRTF, Names, , , &quot;Current Job Opportunities for Classified Personnel&quot;, , -1

[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[nalbiso]

IT WORKED!!! THANK YOU!!

I really appreciate the help John. I have been trying to work this out for three days.

Thanks again!
[sig][/sig]

 

[Guest_imported]

I used the code and it works greate. However, if an email address appears more than once in a database, is it possible to only send the report once to the email address? If so how do I modify the above code?

 

[domk]

Hi Guys,

I tried the above, but I get errors on my Access 2000 .mdb database saying &quot;run-time error 424, object required&quot; and the debugger points to the first &quot;.MoveFirst&quot; line. Any ideas why this should be? Also, any ideas how to get the code to skip email fields which are empty?

Chearz, Dom from UK
____

 

[braindead2]

Q1 Probably because you maybe using an ado recordset and this code is for dao.
Q2 when you return the recordset leave out the nulls by using SQL to retreive your recordset or put in a condition that skips it while looping thru the code.
good luck

 

[UncleJack]

Here's the routine I use to construct a list of Email addresses and dump the result into a table as a single string. I then paste the string into the Email address line.

Function BuildEmail()

Set DB = CurrentDb
Set rstSource = DB.OpenRecordset(&quot;Customer&quot
Set rstDest = DB.OpenRecordset(&quot;EMail&quot
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;DELETE * FROM EMail&quot;
DoCmd.SetWarnings True

With rstSource
.MoveFirst
rstDest.AddNew
'Make sure something is entered
rstDest!EMail = &quot;yourownemail&quot;
rstDest.Update

Do Until .EOF
rstDest.MoveFirst
rstDest.Edit
rstDest!EMail = rstDest!EMail & &quot;; &quot; & !EMail
rstDest.Update
.MoveNext
Loop
.Close
Set rstSource = Nothing
rstDest.Close
Set rstDest = Nothing
End With

End Function

 

[petermaxwell]

I have a similar problem
I would like to use the sendobject function to send a record to a person but the recipient is dependant on the record, i.e there is a field which lists a persons email address in each record and i want to email this person the records which list their address

 

[bcindc]

Help! I am a raw beginner to VBA, or whatever the described code was. Have programmed in Pascal, Delphi, VB5, CBuilder, etc in the last 20 years, but I can't seem to locate an answer. It looks like what you are talking about is something about what I need?
I want to email a report every week, to an Access list of contactcs (130)- on our WAN. The format will be the same for all, but field data contents must come from an Access table.
This is all rather mundane so far. I have the tables and queries built, but how do you insticate an email, via code? Are there Access, VBA, C++ or Excel email components that I can use? I tried Word-Basic, but that was an effort in self-destruction! Any ideas?

 

[oxloc]

Can I get Access 2000 to use DAO rather than ADO so that I can utilise the code in this thread??

Or can someone tell me what the correct ADO code should look like to do the following:

My Table contains the following fields: NAME, EMAIL, ACTIVITY, COUNTRY
I have a Query which selects all records for a certain country
I then want to send a personalised email message to each of those selected records in the query, for example:

&quot;Dear NAME, Please refrain from your ACTIVITY&quot;

I presume this means one message per recipient, which is fine.

Jeremy