Create an email group and send report(s)

[Guest]

Hello everyone,
I am using Access 2002 and would like to create a form that will allow users
to setup groups of emails, then have the option of sending a report(s) to the
group.

Any help appreciated,
Connie

PS: I apologize if this posts twice. Waited for about 20 minutes and still
didn't see my original post, so here it is again.

 

[StevenR via AccessMonster.com]

Hello everyone,
I am using Access 2002 and would like to create a form that will allow users
to setup groups of emails, then have the option of sending a report(s) to the
group.

Any help appreciated,
Connie

PS: I apologize if this posts twice. Waited for about 20 minutes and still
didn't see my original post, so here it is again.

I am working on doing something similar to this today. I am creating a marco
that sends a snapshot of a report. If this is what you want to do then I
would create a button for each group and tie a macro to it so they can send
email to that group. Search the built in Access help for Email and it will
show you the layout for the macro. you can set who the email goes to, what is
in it and wheather or not they can edit it.

HTH

 

[Guest]

Hi Steven
Thank you very much. I'm pretty much ok with setting up a macro to email
and attach a snapshot. But say I want to give more control to the user, ie:
they select the report to attach and they select the recipients to send it to.

Having said that, there may be 100 or more recipients for a particular
report. So setting up a group in the first place is much cleaner. Then
users can simply select the group and report(s), then click 'send'. My
quesiton is, is there a way I can do this? Any ideas?

Connie

 

[Guest]

I've been trying the same thing and finally got it to work - with a lot of
help from the community members here.

My database is used for project management. Each project can be assigned
several resources, each of which is assigned to a functional area. Members
who are in more than one functional area are entered multiple times. All the
entry takes place in a form. Said form has a combo box to select the
functional group to send the email to. This value gets appended with "*" so
that if it is blank it will send the email to everyone.

Following is the code I have so far. I checked a sample and found that the
string length works up to at least 450 characters. Feel free to use/modify
it if it works for you.


Private Sub btnEmailFunction_Click()

On Error GoTo btnEmailFunction_Err

Dim rst As DAO.Recordset
Dim db As DAO.database
Dim strEmail As String
Dim strQuery As String

strQuery = _
"SELECT tblEmployees.EmailAddress " & _
"FROM (tblEmployees INNER JOIN tblProjectSupport ON " & _
"tblEmployees.[Employee ID] = tblProjectSupport.EmployeeID) " & _
"INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID " & _
"= tblDefFunction.FunctionID " & _
"WHERE (((tblProjectSupport.ProjectID) = " & _
[Forms]![frmProjectSupport].[FilterProjectID] & _
") And ((tblDefFunction.FunctionDesc) Like " & Chr(34) & _
[Forms]![frmProjectSupport].[EmailFunctionDesc] & "*" & Chr(34) & _
")) GROUP BY tblEmployees.EmailAddress " & _
"HAVING (((tblEmployees.EMailAddress) Is Not Null)) " & _
"ORDER BY tblEmployees.EmailAddress;"

Set db = CurrentDb()

Set rst = db.OpenRecordset(strQuery)

With rst

If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("EmailAddress")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & "; " & .Fields("EmailAddress")
.MoveNext
Loop
End If

.Close

End With

If strEmail = "" Then
MsgBox "No email addresses were found. " & _
"Please check the employee records and try again."
Else:
DoCmd.SendObject acSendNoObject, , acFormatTXT, strEmail, _
, , "Subject", "Message goes here", True
End If

btnEmailFunction_Exit:
Exit Sub

btnEmailFunction_Err:
Select Case Err.Number
Case 2501 'user canceled email
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in event"
End Select
Resume btnEmailFunction_Exit

End Sub

 

[Guest]

Hi there

I am also trying to set up an email with multiple recipients and I have
tried to use your code but I get the error message " can't find project or
library" when I try to run the code. I am using visual basic 6.0 inside
Access 2000 (with SP3). Which library should I be using?

many thanks
--
an access novice

I've been trying the same thing and finally got it to work - with a lot of
help from the community members here.

My database is used for project management. Each project can be assigned
several resources, each of which is assigned to a functional area. Members
who are in more than one functional area are entered multiple times. All the
entry takes place in a form. Said form has a combo box to select the
functional group to send the email to. This value gets appended with "*" so
that if it is blank it will send the email to everyone.

Following is the code I have so far. I checked a sample and found that the
string length works up to at least 450 characters. Feel free to use/modify
it if it works for you.


Private Sub btnEmailFunction_Click()

On Error GoTo btnEmailFunction_Err

Dim rst As DAO.Recordset
Dim db As DAO.database
Dim strEmail As String
Dim strQuery As String

strQuery = _
"SELECT tblEmployees.EmailAddress " & _
"FROM (tblEmployees INNER JOIN tblProjectSupport ON " & _
"tblEmployees.[Employee ID] = tblProjectSupport.EmployeeID) " & _
"INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID " & _
"= tblDefFunction.FunctionID " & _
"WHERE (((tblProjectSupport.ProjectID) = " & _
[Forms]![frmProjectSupport].[FilterProjectID] & _
") And ((tblDefFunction.FunctionDesc) Like " & Chr(34) & _
[Forms]![frmProjectSupport].[EmailFunctionDesc] & "*" & Chr(34) & _
")) GROUP BY tblEmployees.EmailAddress " & _
"HAVING (((tblEmployees.EMailAddress) Is Not Null)) " & _
"ORDER BY tblEmployees.EmailAddress;"

Set db = CurrentDb()

Set rst = db.OpenRecordset(strQuery)

With rst

If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("EmailAddress")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & "; " & .Fields("EmailAddress")
.MoveNext
Loop
End If

.Close

End With

If strEmail = "" Then
MsgBox "No email addresses were found. " & _
"Please check the employee records and try again."
Else:
DoCmd.SendObject acSendNoObject, , acFormatTXT, strEmail, _
, , "Subject", "Message goes here", True
End If

btnEmailFunction_Exit:
Exit Sub

btnEmailFunction_Err:
Select Case Err.Number
Case 2501 'user canceled email
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in event"
End Select
Resume btnEmailFunction_Exit

End Sub

Hi Steven
Thank you very much. I'm pretty much ok with setting up a macro to email
and attach a snapshot. But say I want to give more control to the user, ie:
they select the report to attach and they select the recipients to send it to.

Having said that, there may be 100 or more recipients for a particular
report. So setting up a group in the first place is much cleaner. Then
users can simply select the group and report(s), then click 'send'. My
quesiton is, is there a way I can do this? Any ideas?

Connie