ps,
what I meant by it requiring code is that not everyone has a ready example to give ... not the way you took it ~
hi Matt,
sorry if you took what I said the wrong way, was trying to help, not add to your frustration. Great that you can go through the VBA and understand it. You can cut the statements that make the PDF file, if that is not what you want to do. I included that so you'd have the option -- if not now then maybe in the future.
It sounds perhaps you mainly need to know how to loop through a recordset. Here is an example of that:
'~~~~~~~~~~~~~~~~~~~
Sub LoopThruRecordset()
'160701 s4p
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim sSQL As String
Dim db As DAO.Database _
, rs As DAO.Recordset
Set db = CurrentDb
sSQL = "SELECT blah blah FROM tablename WHERE criteria"
Set rs = db.OpenRecordset(sSQL)
With rs
Do While Not .EOF 'LOOP through Recordset
'do whatever you want in the loop
'go to next record
.MoveNext
Loop
End With
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
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopThruRecordset"
Resume Proc_Exit
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~
instead of the "do whatever you want in the loop" part, you can refer to the code I gave you before, get what you want, customize it, and add whatever else you need.
respectfully,
crystal
On 10/25/2018 4:40 PM, matthew.randolph83@yahoo.com [MS_Access_Professionals] wrote:
Crystal,
So that I don't mess this up again, I am only going to reply to this string so that all relative info about this is in one post. I do apologize about replying and not having all the messages in my reply. Again, as I have stated before I do not post to this forum often.
I took some time before replying because when you stated "and now you see why no one else answered ... no solution to this without using code", I'll be honest that caught me off guard. I never once stated in any of my posts that I wanted to achieve this without coding. I've done quite a bit of coding in the past and I've got the database to my likings. I merely stated that I have never coded anything that had Access open up Outlook and send a mail merge based on query and table. If I didn't explain what I have and what I would like to achieve in my original post, I apologize.
I am grateful for the information given to me and appreciate the time and effort you and others have taken to reply. Your statements, to me, are unnecessary and are assuming I don't already know the basics. I am in no way a pro at this, but I do learn pretty quickly.
I'll take the coding that you have shared with me and go with that. Again, thank you for your help.
Matt
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
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
On 10/22/2018 7:28 PM, matthew.randolph83@... [MS_Access_Professionals] wrote:
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 (10) |
Tidak ada komentar:
Posting Komentar