
Home	Excel	Access
\| Feedback \|

Distributing Access reports using email

This page is redundant - security processes have been added which attempt to prevent people using Access to send spam emails. Search elsewhere for a solution.

Access offers a menu command (File | Send) to directly send an open report to someone via email. With a VB program you can automate the process and send various reports directly to a long list of recipients at the click of a button.

In this example a query called 'emaildist' is opened. It links together various tables/queries containing report data, a list of people to whom each report is to be sent and their email addresses. Reports (in rich text format) will be generated and sent to everyone whose name matches a combo box value in a form called 'F_ChooseEmail'.

Place the following visual basic code in a Module:

Sub RunEmailDist()
Dim MyDB As Database, MyRecs As Recordset, MyName As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("emaildist")

MyName = InputBox("Enter your name","RunEmailDist (CiM)", "Chris Mead (Extn 3841)")

MyRecs.MoveFirst
Do While Not MyRecs.EOF

If MyRecs!distname = Forms("F_ChooseEmail")!DistNameCombo Then
DoCmd.SendObject acSendReport, "Your budget report", acFormatRTF, MyRecs!SendTo, , , "Budget reports", _
"Please find attached your set of budget reports." & vbCrLF & MyName, 0
End If

MyRecs.MoveNext
Loop

MyRecs.Close
End Sub

Note that 'vbCrLF' generates an <Enter><Line feed> command i.e. it starts a new line of text.

file: aemail.htm