here is some code you can pattern after:
'~~~~~~~~~~~~~~ LoopAndEmail
Sub LoopAndEmail(psReport As String)
'161111 crystal
' code to loop and email that you can customize
On Error GoTo Proc_Err
Dim sFilter As String _
, sPath As String _
, sFilename As String _
, sEmailMessage As String _
, sSQL As String _
, nCount As Long
Dim db As DAO.Database _
, rs As DAO.Recordset _
, oApp As Object _
, oMsg As Object
'initialize variables
sPath = CurrentProject.Path & "\" 'or some other path
nCount = 0
'get list of students and email addresses
sSQL = "SELECT s.StudentID, s.Init, s.emailE, s.StudentFirst " _
& " FROM Students AS s " _
& " WHERE (s.IsActiv=True)" _
& ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
'set reference to outlook
Set oApp = CreateObject("Outlook.Application")
'loop through each student and filter the report
With rs
Do While Not .EOF
'filter the report for the next record
sFilter = "StudentID = " & !StudentID '---- customize criteria
Call SetReportFilter(psReport, sFilter)
'construct filename for this report
sFilename = sPath & !Init & "_MyReportDescription_" & Format(Date, "yy-mm-dd") & ".PDF" '---- customize filename
'output filtered report to a PDF
DoCmd.OutputTo acOutputReport, psReport, acFormatPDF, sFilename
'create email and attach report
Set oMsg = oApp.CreateItem(0) 'olMailItem=0
oMsg.To = !emailE '---- customize who this goes to
oMsg.Subject = "Your report is attached" '---- customize subject
oMsg.Body = "Hello " & !StudentFirst _
& vbCrLf & vbCrLf & "Report generated " & Now & " is attached" '---- customize message
oMsg.Attachments.Add sFilename
oMsg.send
nCount = nCount + 1
GetTheNextRecord:
.MoveNext
Loop
'close the recordset
.Close
End With
'release the recordset object variable
Set rs = Nothing
'give user a message
If MsgBox(nCount & " reports were written to " & sPath & " and emailed" _
& vbCrLf & vbCrLf & "Open the folder?" _
, vbYesNo, "Done creating PDF files and emailing") = vbYes Then
Application.FollowHyperlink sPath
End If
Proc_Exit:
On Error Resume Next
'release object variables
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Set oMsg = Nothing
Set oApp = Nothing
Exit Sub
Proc_Err:
If Err.Number = 2501 Then 'report cancelled
Resume GetTheNextRecord
Else
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndEmail "
Resume Proc_Exit
Resume
End If
End Sub
'~~~~~~~~~~~~~~ SetReportFilter
Sub SetReportFilter(pReportName As String, pFilter As String)
' written by crystal (strive4peace)
' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string
' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments","City='New York' AND dt_appt=#12/18/65#"
On Error GoTo Proc_Err
'---------- declare variables
Dim rpt As Report
'---------- open design view of report and set the report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)
'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
'---------- Release object variable
Set rpt = Nothing
Proc_Exit:
On Error Resume Next
'release object variables
Set rpt = Nothing
Exit Sub 'or Exit Function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetReportFilter "
Resume Proc_Exit
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
have an awesome day,
crystal
Good morning group. It's probably been about 4 years since I've worked with access, but I've been thrown head first into it again.
With that said, I have created a database consisting of three tables and one form.
Table Names:
1. tblBillData
2. tblCLients
3. tblProfitCnt
Form Name:
1. fmMain (did have a subform, but modified my search criteria to search on fmMain)
fmMain Record Source is tblBillData. In the header of this form i have all my filtering criteria (i.e. start/end dates, clients, etc.) I hit the filter button that creates my string for filtering the data from the tblBillData. Everything works wonderful and I am happy the way it turned out.
Now my next task, which is something I have never done before, is to take that data that I've collected on the form and create a mail merge for each client_code from the form.
what i would like to do, is have access take each record that is showing, put the relevant data in an email, look up the email in the Email: field from the tblClients table and place that in TO: line also take the email in CC: field and place it in the CC: line.
Is this achievable or am I hoping too much? Please let me know if you guys have any questions. When I was designing previous databases, I didn't post much because I could pretty much find what I was looking for in the group, this one however has me stumped.
Again, thank you to anyone that responds, any help is much appreciated.
Matt
Posted by: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar