Jumat, 26 Oktober 2018

Re: [MS_AccessPros] Mail Merg with access 2007-2016 Office 365

 

as for where to put the code ... sure, you can do everything in the CLICK event of a command button. Then you just need to DIM the psReport parameter and define it in the procedure since you wouldn't be using that procedure declaration I gave you -- or specify the literal value where needed.


On 10/26/2018 8:39 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:
ps,
what I meant by it requiring code is that not everyone has a ready example to give ... not the way you took it ~

On 10/26/2018 8:30 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:
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 (11)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___

Tidak ada komentar:

Posting Komentar