Coding for Email

[TygerTyger]

I just started programming in VB and have little experience in programming and I am in need of some help. I have generated the following code to separate one large report into different sections based on agency information and it saves these separates reports into a file. I want to email each agency their report but I have over a 1000 agencies and don’t want to email each individually. I know I can use the SendObject Command in my code to email these reports as attachments but I don’t know how I would incorporate it into my code. I don’t have the email of the agencies as part of my data. Do I have to set up another table with email addresses to accomplish this? Does anyone have any suggestions?


Option Compare Database
Option Explicit

Public Sub PrintAgencyReport()

Dim dbs As Database
Dim tdf As TableDef
Dim rstAgency As Recordset
Dim strWhere As String
Dim strReportName As String
Dim strFileName As String
Dim rstTestForRecords As Recordset
Dim strWhereTest As String
' Return reference to current database
Set dbs = CurrentDb

' Return reference to Agency table
Set tdf = dbs.TableDefs!Table1

' Open the AgencyList table containing agencies
Set rstAgency = dbs.OpenRecordset("Table1")

' Loop through AgencyList table to get the agency _
parameter for the report and path to send report
With rstAgency
.MoveFirst
Do Until .EOF
strReportName = "Report1"
strWhere = "[Agency]='" & rstAgency![Agency] & "'"
strFileName = "C:\FDLEfiles\" & rstAgency![Agency] & ".rtf"
strWhereTest = "Select * From Table1 Where " & strWhere
Set rstTestForRecords = dbs.OpenRecordset(strWhereTest)
If rstTestForRecords.BOF And rstTestForRecords.EOF Then
Else
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, strReportName, "RichTextFormat(*.rtf)", strFileName
DoCmd.Close
End If
.MoveNext
Loop
.Close
End With
End Sub


Thanks For The Help!

 

[BarkerD]

I don't have an answer for the e-mail question, but I have a question of my own regarding your code.

What purpose does the variable tdf serve?

I have never used a tableDef object in my code, nor would I know how to use it.

As for the second question regarding setting up a separate table with e-mail addresses, I think you should be able to access the address book in Outlook to reference the appropriate e-mails. I expect you would have to create groups based on sections.

Duane Barker