Generating Email for a Specific Group

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

The company I work for has a large pool of employees,
which will be entered into a database that I am in the
middle of constructing, which report in to a small number
of Coordinators. I would like to create a system where
an email could be sent out only to those employees who
report to a specific Coordinator.

I currently have a VB Module which will open Outlook and
create a message that will be sent to everyone in the
database. My question is, how would I go about limiting
it to just one of the Coordinator groups. I would like
to add this functionality to a form which displays the
associates in a subform.

Regards,
Cameron
 
Hi Cameron,

Loop through a recordset executing the VB Module for every record in the
recordset. This recordset should be based on a query which filters only
employees who report to a specific Coordinator (i.e. the Coordinator field
would be the criterion field in your query).

Immanuel Sibero
 
Thanks for your response Immanuel. I was also wondering
if I could do it dynamically through the use of a
subform. Say I have a subform with the employees and use
a combobox to filter them by Coordinator. Would it be
possible to generate the email based on what's in the
subform at a given time.

Regards,
Cameron
 
Hi Cameron,

Yes. The combobox value would be the criterion for the query.

For example, you could have a command button on the main form to send out
email messages. The code behind the command button will open a recordset
(rst) based on a query (strSQL). This query filters all employees
(tblEmployee) for a specific Coordinator based on the value of the combobox
(CoordComboBox) on your main form.

The code for the OnClick of the command button could be something like this
(you would need to add your error handlers):



Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * from tblEmployee "
strSQL = strSQL & "where Coordinator = " & "'" & Me.CoordComboBox & "'"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.EOF = False Then
rst.MoveFirst
Do Until rst.EOF


'This is where the VB Module to open Outlook executes. You would
need employee's email address as parameter


rst.MoveNext
Loop
Else
MsgBox ("No Email to send")
End If

set rst=nothing
set dbs=nothing



HTH,
Immanuel Sibero
 
Hi again Immanuel. Thanks for your post it was very
helpful. Now that I have everything set up I just have a
small question. When I attempt to run it now I am
receiving the error message:

Run-time error '3011':

The Microsoft Jet database engine could not find the
object 'SELECT * FROM Employees WHERE Coordinator = '3'.
Make sure the object exists and that you spell its name
and the path name correctly.

I'm finding this very frustrating because the Employees
table exists along with the Coordinator field. Any
assistance would be helpful.

Thanks,
Cameron
 
I should also mention that it is highlighting this line:

Set recset = dbs.OpenRecordset(strSQL, dbOpenTable)

Cameron
 
Cameron

What is the data type of the Coordinator field
It is a character or numeric
If it is numeric then you should remove the apostrophe character from your query expression

hope this hel

Gabriel
 
Hi Gabriel, thanks for the response. Coordinator is a
numeric field. I'm not sure which apostrophe you are
referring too, but the one at the start of the SQL
statement was put there by the error messsage.

Cameron
-----Original Message-----
Cameron,

What is the data type of the Coordinator field ?
It is a character or numeric ?
If it is numeric then you should remove the apostrophe
Click to expand...
character from your query expression.
 
Hi Cameron

This was the orginal code I posted:

strSQL = "Select * from tblEmployee "
strSQL = strSQL & "where Coordinator = " & "'" & Me.CoordComboBox & "'"

I apologize for not testing it, it was air code. There might be two
problems - a semi colon needs to end the SQL text and Coordinator was
assumed to be text.


Try this:

strSQL = "Select * from tblEmployee "
strSQL = strSQL & "where Coordinator = " & Me.CoordComboBox & ";"


HTH,
Immanuel Sibero
 
Cameron,
Set recset = dbs.OpenRecordset(strSQL, dbOpenTable)
Click to expand...

You shouldnt use dbOpenTable here, use dbOpenSnapshot or dbOpenDynaSet.
dbOpenTable is used to open recordset of "table" type.

HTH,
Immanuel Sibero
 
Back
Top