Re: email with criteria?

[nrage21]

Re: email with criteria?

Hi forum,
I read many threads but didn't find what I was looking for...

This is my problem... there are 9 people entering info on a form. These people are called 'enrollers' and are in a table. These enrollers send the report to different people. For example:

Enroller Mary has to send report to John Doe
Enroller Ed has to send report to Michael J
Enroller Mark has to send report to Michael J and Christian P
etc, etc

I have the following code but it's only designed to send the report to a recipient. How do I change the code so when Mary, Ed or Mark sends the report it goes to the designated recipient???? Do I have to declare variables?? and then set cases?? Is this task even possible to accomplish???...

 Function mcrOpenRept()
On Error GoTo mcrOpenRept_Err
    
        DoCmd.SendObject acReport, "rptMainStandard", "RichTextFormat(*.rtf)", "johndoe@something.net", "", "", "Re: Report", "Please see attachment.", False, ""


    End If
mcrOpenRept_Exit:
    Exit Function

mcrOpenRept_Err:
    MsgBox Error$
    Resume mcrOpenRept_Exit

End Function

The form and the report have a field name called EnrollerName. I know there must be a way to set the criteria for the code above based on this field. But, I have no idea how to do it.

Thanks in advance.

- Larry -
VBA Amateur

 

[jgc31]

A couple of thoughts spring to mind if the reciepients are fixed for each person then create a table with a one to many relationship so that each person entering reports has a list of recipients. Then open a recordset based on this and loop through sending a mail to each of the appropiate people in turn. If they are not fixed have one or more list boxes to allow the your enrollers to choose who th send mails to.

 

[nrage21]

jgc,
The recipients are fixed for each enroller. I followed your advise. I created a table called recipients.

RecipientID, EnrollerID, RecipientName, Email

I then proceeded to linking the Enrollers Table with the Recipients Table added a lookup in the EnrollerID column of the Recipient's table. I then established a one-to-many relationship between the two tables.

Now how do I loop so the email can be send to each appropiate recipient?

Pardon my inexperience!

- Larry -
VBA Amateur

 

[jgc31]

Larry

I would create a recordset for each enroller and then write a loop to send the mails.

Something along the lines of

dim dbs as DAO.database
dim rstRecipients as DAO.recordset
dim strSQL as string
dim intEnroller as integer

intEnroller = EnrollerID [COLOR=Red]' get this from some sort of input eg a field in a form[/COLOR]

strSQL = " SELECT RecipientName, Email FROM recipients WHERE EnrollerID = " & intEnroller

set dbs = current.db

set rstRecipients = dbs.openrecordset(strSQL)

rstRecipients.movefirst

do while not rst.eof

strEmail=rstRecipients.fields("Email") 
strRecipientName=rstRecipients.fields("RecipientName") 

[COLOR=Red]'Put the docmd.sendobject stuff here using the RecipientName, Email fields. [/COLOR]

rstRecipients.movenext

loop

rstRecipients.close

set rstRecipients = nothing
set dbs = nothing

You should not take the above as a usable piece of code but a general pointer( not least because I haven't checked it properly for errors!). You should find a primer on using recordsets and the two different types of recordsets (DAO and ADO). You can also try searching the forum for info on recordsets.

Hope this helps

 

[nrage21]

Thanks for taking the time to assist me jgc,
I will implement the code into my model immediatelly and will make the necessary changes.

If I get stuck I will post back, and when I have a working solution I will also post the code, so it can help someone else.

- Larry -
VBA Amateur

 

[nrage21]

OK I'm stuck

When I pressed the Send button, I get the message "Object Required" ???

I get the feeling I'm not using the right reference or the DoCmd.SendObject stuff properly.

My table information:

table name: tblRecipients
Fields: RecipientID, EmpID (formerly 'EnrollerID'), RecipientName, Email

Form name: frmStandardRpt
There is an input field in the form called "EmpID"... this is what I used in the code above as per your suggestion.

Report name: rptMainStandard

References in use:
- Visual Basic For Applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Visual Basic for Applications Extensibility 5.3
- Microsoft DAO 3.6 Object Library

The code...

Option Compare Database
Function SendEmails()
On Error GoTo SendEmails_Err

Dim dbs As DAO.Database
Dim rstRecipients As DAO.Recordset
Dim strSQL As String
Dim intEnroller As Integer

intEnroller = EmpID ' get this from some sort of input eg a field in a form

strSQL = " SELECT RecipientName, Email FROM tblRecipients WHERE EmpID = " & intEnroller

Set dbs = Current.db

Set rstRecipients = dbs.OpenRecordset(strSQL)

rstRecipients.MoveFirst

Do While Not rst.EOF

strEmail = rstRecipients.Fields("Email")
rstRecipientName = rstRecipients.Fields("RecipientName")

'Put the docmd.sendobject stuff here using the RecipientName, Email fields.
DoCmd.SendObject acReport, "rptMainStandard", "SnapshotFormat", strEmail, "", "", "Re: Standard MOHIA Report", "Please see attachment.", False, ""

rstRecipients.MoveNext

Loop

rstRecipients.Close

Set rstRecipients = Nothing
Set dbs = Nothing

SendEmails_Exit:
    Exit Function

SendEmails_Err:
    MsgBox Error$
    Resume SendEmails_Exit

End Function

- Larry - (puzzled)
VBA Amateur

 

[jgc31]

Below is a line I used recently which worked fine

DoCmd.SendObject acSendReport, , acFormatSNP, "xyz@easynet.co.uk", , , "Invoice", strMailText, False

I have also spotted a mistake in the code should be

Do While Not rstRecipients.EOF

As I suggested before the code was meant as general guidance not finished code.

You should run the code in debug mode and step through line by line and check where it falls over or click on debug at the error message which should take you to the offending line of code.

Do you need to use the recipient name stuff ? If not get rid of it , the code will still work but it is best to delete redundant code .

 

[nrage21]

No I don't need the recipient stuff. Still getting the message Error '424' "Object require"

I took out the error handler and debug is stopping at the following line...

Set dbs = Current.db

????



- Larry -
VBA Amateur

 

[nrage21]

Why does it say "NO records"??

Ok,

I changed: Set dbs = Current.db for Set dbs = CurrentDB, now the debug is stoppinf at the line:

rstRecipients.MoveFirst

Run time error '3021' No current record ??? What does .MoveFirst do?


Ok, now I now MoveFirst moves the record position to the first record.

- Larry -

 

[nrage21]

I'm such an IDIOT!!

Thanks soooo much jgc! would you like a check or mo? j/k

I had the code in a module, and not as part of the form, so I think the field "EmpID" was pulling air, because access had no way to be referenced to that field. I moved the code to where it was supposed to be and also strEmail, hadn't been declared. That did the trick, it is working, now I have to set some sort of criteria, because the code is sending "all" the records in my standard table, and I want to send only the very last one.

It would have to be something with the last "StandardRptID" entry or last record. Now, does anyone know how to tackle this little obstacle?

For the benefit of all you readers, below is the working code... (I added the error handler again)

Function SendEmails()
On Error GoTo SendEmails_Err

Dim dbs As DAO.Database
Dim rstRecipients As DAO.Recordset
Dim strSQL As String
Dim intEnroller As Integer
Dim strEmail As String

intEnroller = EmpID

strSQL = " SELECT Email FROM tblRecipients WHERE EmpID = " & intEnroller

Set dbs = CurrentDb

Set rstRecipients = dbs.OpenRecordset(strSQL)

rstRecipients.MoveFirst

Do While Not rstRecipients.EOF

strEmail = rstRecipients.Fields("Email")

DoCmd.SendObject acSendReport, "rptMainStandard", "SnapshotFormat", strEmail, "", "", "Re: Standard MOHIA Report", "Please see attachment.", False, ""

rstRecipients.MoveNext

Loop

rstRecipients.Close

Set rstRecipients = Nothing
Set dbs = Nothing


SendEmails_Exit:
    Exit Function

SendEmails_Err:
    MsgBox Error$
    Resume SendEmails_Exit

End Function

- Larry -
VBA Amateur