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

[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

 

[Travis]

http://groups.google.ca/group/comp....e6338b9e9da/7dd9ac8179e2ba41#7dd9ac8179e2ba41

 

[Allen Browne]

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