Sending Email to Multiple Recipients - What wrong with my sript? (1 Viewer)

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
Hello,

I've been playing with this for ages without success. I have three tables (part of a database which is used to track responses from various divisions) with the following fields:

tblPerson
> pkPersonID
> txtFirstName
> txtLastName
> txtEmail

tblDivision
> pkDivID
> txtDivisionName
> txtLastResponse (** Date last response received from Division)

tblDivisionPerson
> pkDivPersonID
> fkDivID
> fkPersonID

I have a query which i run, which says, gives me all the division in where the last response is based on an input paramater (i.e.// give all divisions where the lastresponse was before Jan 1, 2010). On this form, when i run this query, i want a command button that when i click it, it would open up an email message with the email of all divisions who meet the above criteria (according to who the person linked to that division is). I'm sure there is a simple way to do this and i'm missing it.

I tried this code, but no luck:

Code:
Private Sub cmdEmail_Click()
Dim strMailList As String, rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblPerson", dbOpenDynaset)
With rs
  .MoveFirst
    Do
      strMailList = strMailList & !txtEmail & ";"
        .MoveNext
    Loop Until .EOF
      strMailList = Left(strMailList, Len(strMailList) - 1)
        .Close
End With
  DoCmd.SendObject acSendForm, "frmMissingResponses", acFormatHTML, strMailList, , , "Missing Scorecard Response", "Test", True
Set rs = Nothing
End Sub

When i run the above i get a "Run-time error 2295: Unknown message recipients(s); the message was not sent.

Any advice? This recordset is totally new to me..so i've been trying to figure it out.

Andrew.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:31
Joined
Aug 30, 2003
Messages
36,118
Try adding

Debug.Print strMailList

right before the SendObject line, which will print the address string to the Immediate window, where you can examine it to see if it looks valid.
 

CEH

Curtis
Local time
Today, 03:31
Joined
Oct 22, 2004
Messages
1,187
Have a look at this one.......

Code:
 Function EmailList() As String
'-- Return all of the email addresses in the EmailAddress table
'-- as one string separated with a comma ","

On Error GoTo Err_EmailList

Dim MyRs As DAO.Recordset
Set MyRs = CurrentDb().OpenRecordset("tblEmailList", dbOpenForwardOnly)

With MyRs
   Do While Not .EOF
      EmailList = EmailList & ![EmailAddress] & ","
      .MoveNext
   Loop
End With

'-- Strip off the last ","
EmailList = Left(EmailList, Len(EmailList) - 1)

Exit_EmailList:
If Not MyRs Is Nothing Then
    MyRs.Close
    Set MyRs = Nothing
End If
Exit Function
    
Err_EmailList:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_EmailList


End Function
 

ghudson

Registered User.
Local time
Today, 04:31
Joined
Jun 8, 2002
Messages
6,195
I believe that you will be limited to the total number of characters for the email addresses sent to your email program when using the SendObject method.

Scratch that, I think it is the mailto: option that is severely limited.
 

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
pBaldy,

I added the code you suggested, and the immediate window displays the below (which obviously doesn't look correct):

;;;cc@test.com;;;;;;je@test.com;kc@test.com;;;ksb@test.com;;;;;rb@test.com;;;;;;;;;;;;;;;;

Any advice how to fix this? I counted the number of "semi-colons" in above - and it's 35 - which is exactly the number of records in tblPerson. Obviously i have severiouly messed something up :)

Andrew.
 

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
Curtis,

I tried your code as well - but im' not sure where to place it? Do i place it on the onclick event of the command button on my form? Anything i tried doesnt' work.

Thanks for your help everyone - i am very much an access newbie - but i'm trying to learn :)

Andrew.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:31
Joined
Aug 30, 2003
Messages
36,118
It would appear you have Nulls in your email address field. Try replacing this:

strMailList = strMailList & !txtEmail & ";"

with this
Code:
If Not IsNull(!txtEmail) Then
  strMailList = strMailList & !txtEmail & ";"
End If
 

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
Hi Paul,

I think we're almost there -it seems to work now - HOWEVER, it emails everyone in "tblPerson", not just the people who were identified because of my query "qryMissingResponses".

I only want to email those people who are "late" on there responses as per the query i specified.

Does this make sense?

Andrew.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:31
Joined
Aug 30, 2003
Messages
36,118
Well, you're opening the recordset on tblPerson. Perhaps you could open it on qryMissingResponses?
 

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
Hi Paul,
I actually thought of that, so i ammended the code to read:

Code:
Set rs = CurrentDb.OpenRecordset("qryMissingResponses")

However, i get this error:

Run-time error "3061". Too few paramaters. Expected 1.

I gotta be missing something simple!
Andrew.
 

ghudson

Registered User.
Local time
Today, 04:31
Joined
Jun 8, 2002
Messages
6,195
Is qryMissingResponses a select query? You must use a select query for your OpenRecordset.
 

andmunn

Registered User.
Local time
Today, 01:31
Joined
Mar 31, 2009
Messages
195
GHudson,

Definately a select query. It does have a paramater within one of the fields:

Field / Criteria
DateLastResponse: <[What Month and Year] Or Is Null

Andrew.
 

Users who are viewing this thread

Top Bottom