Email Individual Records within a Report / Query

[dbain30]

I have created a query to identify the accounts that require attention.
My hope is that we can create a report that would contain only the
information for the first record and automatically send an email to the
account rep, and then do the same for the 2nd, 3rd, 4th, etc record.
The idea is that each record would go out as an independent email so
that they only receive information for those that they personally need
to pay attention to.

Any suggestions on how I can accomplish this?

I would have an update query for the record to time stamp when the
email was sent so the record would update in this process and would
then would not meet the criteria for when the report was run after this
time.

Any help would be greatly appreciated!

 

[Surendran]

Hi,

Your task is to send mails to Reps for accounts where mail is not yet sent.

My suggestion is you can use a form instead of a report as there is more
flexibility
available in manipulating records in a form compared to a report.

For this you can automate your task either by using Outlook or CDO.

Since your task involves multiple mails, Outlook is not preferable
as it prompts security alerts for each mail to be sent.Instead you
can use CDO which is very simple in sending multiple mails without
any user intervention.

To demonstrate in using CDO, let me explain with a small sample application.

If you want to test for yourself, then you may have to create
this sample application in Access.

Create a new Access databse and the following objects.

Table:tblMailToRep

Table fields:
Field Name Data type
Rep Text
Customer Text
RepMail_ID Text
OrderNo Number
OrderDetails Text
MailSent Yes/No

Create a query:qryMailToRep

"SELECT tblMailToRep.Rep, tblMailToRep.Customer, tblMailToRep.RepMail_ID,
tblMailToRep.OrderNo, tblMailToRep.OrderDetails, tblMailToRep.MailSent
FROM tblMailToRep
WHERE (((tblMailToRep.MailSent)=False));"

Create two forms:
Form 1: frmMailToRepSub - Data source - qryMailToRep

Form 2: frmMailToRepMain
In form 2 design view, create a sub form with source object as Form 1.
Give subform control name as frmlMailToRepSub

Also create a command button with caption "Send Mail" and name as
"cmdSendMail".

In the on click event of button enter the following code.

================================================
Private Sub cmdSendMail_Click()
Dim objMessage As Object
Set objMessage = CreateObject("CDO.Message")

Dim rst As DAO.Recordset
Set rst = Me.frmlMailToRepSub.Form.RecordsetClone

Dim strBody As String
'On error resume next
objMessage.From = "Sender Mail ID"
objMessage.Subject = "Order follow up"

rst.MoveFirst
If Not rst.BOF Or Not rst.EOF Then
Do Until rst.EOF
strBody = "Hi " & rst!Rep & "," & vbCrLf & "Customer:" &
rst!Customer & vbCrLf
strBody = strBody & "OrderNo:" & rst!OrderNo & vbCrLf
strBody = strBody & "OrderDetails:" & rst!OrderDetails & vbCrLf
strBody = strBody & "Please follow up the above order and ensure
that "
strBody = strBody & "the products are delivered with in the
stipulated date."

objMessage.To = rst!RepMail_ID
objMessage.TextBody = strBody
objMessage.Send

rst.Edit
rst!MailSent = True
rst.Update
rst.MoveNext
Loop
End If
Me.frmlMailToRepSub.Form.Requery
Set rst = Nothing
Set objMessage = Nothing
MsgBox "Mail sent"
End Sub
================================================

Enter few records with some valid mail ids in RepMail_ID which would be
in a position to be verified by you after sending the mail.

Also see that all fields are filled with some data.

Regarding the sender's address, i.e. objMessage.From in the above code
you can give any mail id as you like. Even if it is blank, the code will
work but the mail will become anonymous.

Now creating the sample application is over.

In order to CDO to work, your machine should be installed with IIS SMTP
service
as CDO uses IIS SMTP for sending mail messages.

IIS SMTP is an optional windows component.

If you don't know how to install IIS SMTP, then proceed as given below.

Open the Control Panel, launch Add/Remove Programs, and click on the
Add/Remove Windows Components button. In the Windows Components Wizard,
select the IIS item and click on the Details button. A new dialog box will
open listing all the services of IIS, including SMTP. Enable the
corresponding checkbox and the Windows Installer will add the files and
registry keys needed by the service.

Now come back to sample application.

Open the form "frmMailToRepMain".

If you have entered few records and MailSent field is not checked
then you will see the records in the open form.

Click "Send Mail" button.

If you get the MsgBox prompt with Mail sent, close it
and go and check the Mail Ids you have given in the sample data.

After verification of the mails, if you are assured that everything is
as intended, then you can apply the same technique in your applications.

If you want to know what is CDO ? and how it works, recently I have given
a reply to a post in AccessMonster.com where in I have explained about
whatever I have come across while using CDO.
The link is given below.
http://www.accessmonster.com/Uwe/Th...82/Attaching-a-CSV-file-to-an-Outlook-Express

 

[ivon_lui]

Hi, there is a sample which demonstrates how to send email in access,
it will pick the recipients from the Recipients table. If you want to
update the record after the email was sent, you can change the code in
this subroutine.

rivate Sub m_oFastSender_OnSent(ByVal lRet As Long, _
ByVal ErrDesc As String, _
ByVal nKey As Long, _
ByVal tParam As String, _
ByVal senderAddr As String, _
ByVal Recipients As String)

If lRet = 0 Then
Status.Caption = "Sent email to " & Recipients & "
successfully"
Else
Status.Caption = "Sent email to " & Recipients & " failed: " &
ErrDesc
End If

If nKey = m_nRecipientCount - 1 Then
m_bFinished = True
End If

End Sub

Download the sample.
http://www.emailarchitect.net/webapp/download/email_new.zip

To run this sample, you must install the email component on your
machine at first.

Download the component
http://www.emailarchitect.net/webapp/download/ansmtp.exe

Another easy way is: using the EASendMail Service + this component, you
need few code. More detail, please refer to this:

http://www.emailarchitect.net/webapp/smtpcom/sdk/default.asp?ct=object_queue.htm

It will pick the recipient from access database and update the result
in database automatically, please refer to asp_queue_database sample
after you install the smtp component.

Hope this could be helpful to you.