AUTOMATING E-MAIL/SENDING TO MULTIPLE USERS WITH DIFF INFO

M

Matt

I have a database that collects defects in a manufacturing environment.
Everyday, I need to send out defect notices via e-mail to several different
poeple, each requiring to see only thier data. This amounts to almost fifty
e-mails per day. I think that this is similar to an earlier string called
"Automating e-mail/Attachement Sending"

What I am currently doing is e-mailing out a report for each user to see
only thier information. I would like to send the information as well as a
sub report and write it to a PDF file (due to the charts I want to include in
the subreport). How can I send each user only thier information. The e-mail
addresses and the information are in the same record by a linked query and
the report is displaying the information.

It would be unbelievable if I could automate this process!

I am sonewhat new to writing modules and code. Any help would be greatly
appreciated.

THANK YOU!
 
R

Roger Carlson

You've set yourself a pretty big task. First of all, your going to have to
create a process to run through a list of people, create and export a report
with individualized data. On my website (www.rogersaccesslibrary.com),
there is a small sample database called: "EmailingSpecificReports.mdb" which
illustrates this. You'll notice, however, that it is in Access 97 ONLY.
That's because the SendObject command won't work properly in subsequent
versions. However the process for creating the reports will.

To correct this issue, you're going to have to look into Office Automation
to automate Outlook to actually send the reports. Here's a link for that:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161088&ID=kb;en-us;Q161088

Lastly, you're going to have to automate a PDF writer. There's some
information here:
http://www.mvps.org/access/reports/rpt0011.htm

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
M

Matt

Roger,

Thank you for your quick repsonse. In the following code, where will I need
to break it? I am using access 2002 version.

I will have to sift throught the other information......

Thanks again!

Option Compare Database
Option Explicit
'**********************************
'Created by Roger Carlson *
'[email protected]*
'[email protected] *
'**********************************


Sub SeparateEmails()
'*** error trapping - execution goes to bottom on error
On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("Users", dbOpenSnapshot)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table
strSQL = "SELECT * FROM GLTable WHERE "
strSQL = strSQL & "[Acct] = '" & rsCriteria![Param] & "'"

'MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acReport, "rptGLTable", "RichTextFormat(*.rtf)",
rsCriteria![User], "", "", "This is a test", "I am testing a new idea for
reports", False, ""
rsCriteria!Emailed = True
'*** goto the next record in Criteria table
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails: '*** if there is an error, execution goes here
'*** if the error is the table or query missing (3265)
' then skip the delete line and resume on the next line
' Error 2501 notifies you that the SendObject action
' has been cancelled. See the OnNoData Event of the report.
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
'*** write out the error and exit the sub
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub
 
R

Roger Carlson

OK, Here's what this code does. For each of the records in the table
"Users", it creates a query called "NewQuery". The report (rptGLTable) uses
NewQuery as its Record Source. The part of this that WON'T work is the
DoCmd.SendObject line. SendObject no longer works properly with Access 2000
and above.

Instead of the SendObject line, you'll have to
1) Write the report to a PDF file.
2) Create an email with Outlook automation
3) Attach the PDF to the email and send it

Then you go back to the next user and so forth.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Matt said:
Roger,

Thank you for your quick repsonse. In the following code, where will I need
to break it? I am using access 2002 version.

I will have to sift throught the other information......

Thanks again!

Option Compare Database
Option Explicit
'**********************************
'Created by Roger Carlson *
'[email protected]*
'[email protected] *
'**********************************


Sub SeparateEmails()
'*** error trapping - execution goes to bottom on error
On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("Users", dbOpenSnapshot)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table
strSQL = "SELECT * FROM GLTable WHERE "
strSQL = strSQL & "[Acct] = '" & rsCriteria![Param] & "'"

'MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acReport, "rptGLTable", "RichTextFormat(*.rtf)",
rsCriteria![User], "", "", "This is a test", "I am testing a new idea for
reports", False, ""
rsCriteria!Emailed = True
'*** goto the next record in Criteria table
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails: '*** if there is an error, execution goes here
'*** if the error is the table or query missing (3265)
' then skip the delete line and resume on the next line
' Error 2501 notifies you that the SendObject action
' has been cancelled. See the OnNoData Event of the report.
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
'*** write out the error and exit the sub
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub




Roger Carlson said:
You've set yourself a pretty big task. First of all, your going to have to
create a process to run through a list of people, create and export a report
with individualized data. On my website (www.rogersaccesslibrary.com),
there is a small sample database called: "EmailingSpecificReports.mdb" which
illustrates this. You'll notice, however, that it is in Access 97 ONLY.
That's because the SendObject command won't work properly in subsequent
versions. However the process for creating the reports will.

To correct this issue, you're going to have to look into Office Automation
to automate Outlook to actually send the reports. Here's a link for that:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161088&ID=kb;en-us;Q161088

Lastly, you're going to have to automate a PDF writer. There's some
information here:
http://www.mvps.org/access/reports/rpt0011.htm

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


include
in
Click to expand...
Click to expand...
 
Top