Trying to e-mail from Access - Please help...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am using Access 2002, and have a database which stores payments owed by
individuals. In one query, I have a list of individuals, their email
address, and the amount they owe. I would like to know if it is possible to
be able to set up some Visual Basic code which would enable me to send all
the individuals separate e-mails advising of their payment amounts owing (at
the click of a button).
Basically, if there were 5 individuals who owed money (listed in the query),
one click of a button would enable me to send out 5 separate emails listing
the specific individuals' owing amount. Is this possible?

Hoping someone can help
Thanking you in advance

AC
 
Yes, if you are comfortable with writing VBA code, you can achieve this.

In essence, you create a report that shows what everyone owes, one person
per page. You use a public string to filter the report, and use the Open
event of the report to apply a Filter so it contains only the records for
one person. You OpenRecordset on a list of the people you, loop through the
list, and SendObject to email the report.

1. In a Standard module, declare a public string variable you can use to
filter the report:
Public gstrReportFilter As String

2. In the Open event of the report, read, use, and reset that variable:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

3. In the Click event procedure of the command button where you want to fire
off the emails, you will need something like this:
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT ClientID, Email FROM Table1 WHERE ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
gstrReportFilter = "ClientID = " & rs!ClientID
DoCmd.SendObject acSendReport, "Report1", acFormatSNP, _
rs!Email, , ,"Your report", "Attached is your report", False
rs.MoveNext
Loop
rs.Close
 
Back
Top