Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12

    Adding loop to existing code

    Hello. Thank you in advance for your help! - I have existing code that I'm converting from another DB project where I needed to create a single pdf, save it and email it. Now I need to take this code and have it do 2 additional things.

    1 - I need to loop through a list of records, saving a pdf and sending an email for each line.
    2 - if the same email has several lines (creating several reports / PDFs), then I would like to have them all sent in a single email.

    I know that #1 is probably simple to many of you (not I). Is #2 possible?

    Here is the code as it is now:

    Public Function Command21_Click()
    Dim strRep As String
    Dim strDPath As String
    Dim strFName As String
    ' What report to send
    strRep = "01 - 01 - Commission Summary Report - Sage One"
    ' Initial Path
    strDPath = "C:\Users\DLSmith\Documents\Commissions Project\Reports Sent\"
    ' Filename
    strFName = (DLookup("[Name]", "01 - 03 - Commission Summary Report - Sage One")) & "_Order_" & (DLookup("[Month]", "01 - 03 - Commission Summary Report - Sage One")) & (DLookup("[Year]", "01 - 03 - Commission Summary Report - Sage One")) & "_" & Format(Date, "mm-dd-yyyy")
    'add file extension
    strFName = strFName & ".pdf"
    ' Output report as pdf
    DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
    ' Send the report to whoever
    Send_Email (strDPath & strFName)
    End Function
    Private Sub Send_Email(strDoc As String)
    Dim sTo As String
    Dim sCC As String
    Dim sBCC As String
    Dim sSub As String
    Dim sBody As String
    Dim strCC As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim varPress As Variant
    ' Original code copied from http://www.ozgrid.com/forum/showthread.php?t=51384
    strMess = "You are about to send an email message to ." & (DLookup("[Manager Email]", "01 - 03 - Commission Summary Report - Sage One")) & vbCrLf & vbCrLf
    strMess = strMess & "Do you wish to continue?"
    strStyle = vbYesNo
    strTitle = "Send Notificaiton"
    varPress = MsgBox(strMess, strStyle, strTitle)
    If varPress = vbYes Then


    ' Get the email address from the current form control
    sTo = DLookup("[Manager Email]", "01 - 03 - Commission Summary Report - Sage One")

    ' Set the subject
    sSub = (DLookup("[Name]", "01 - 03 - Commission Summary Report - Sage One")) & "_Commission Statement_" & (DLookup("[Month]", "01 - 03 - Commission Summary Report - Sage One")) & "_" & (DLookup("[Year]", "01 - 03 - Commission Summary Report - Sage One"))
    ' Build the body of the email
    ' sBody = "First name from some field" & vbCrLf & vbCrLf
    ' sBody = sBody & "Continue with message"

    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""


    With OutMail
    .To = sTo
    .CC = sCC
    .BCC = sBCC
    .Subject = sSub
    .Body = sBody
    .attachments.Add (strDoc)
    .Display ' THis will display the email, but not send it
    '.Send ' THis will send the email
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End If

    End Sub


    Any help would be greatly appreciated!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1. open a recordset of the email addresses and other data to filter report, open report filtered to records associated with the addressee, save report as pdf, attach to email object, send email, close report, move to next record, loop and repeat

    2. filtering in step 1 should result in a single PDF attachment

    This is common topic in forum. Many threads have example code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    Thanks. I've seen some other coding that uses the loop, but knowledge is so limited that I'm not sure where / how to work it into my existing code.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Pseudocode:

    open recordset

    while recordset not end of file - this is start of loop
    code to open filtered report, save pdf, close report, attach pdf, send email, delete pdf if want to
    move to next record
    repeat loop - this closes the loop
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    Thanks for the response. Sorry to be a pest here, but would you be willing to show me what this would look like in my code.

    For example, where in the code do I open the record set? I believe I already have the code to open the report, send email, etc.

    I'm ok with not being able to put multiple attachments in one email, but I need to figure out how to work in the loop.

    thank you!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Something like:

    Dim strFName As String
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT ManagerID, ManagerEmail FROM tablename;")

    While Not rs.EOF
    'code to open filtered report, save pdf, send email with pdf attachment
    rs.MoveNext
    Wend
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    Ok, I'm back to this again. I made some changes to the code and it ALMOST does what I need it to. It runs the report and attaches it to an email for each individual person. The issue now is that it's sending a report that includes all pages for all employees instead of moving down the list and sending a report only for the person in the email. Can someone help me figure out how to adjust my code so that it creates one report per line in the query rather than all lines?

    If that's confusing, this is what I want to happen:
    1 - create report from one record (line in query)
    2 - send email with report attached
    3 - go to next record and repeat until the end.

    The problem is in step 1 including all records instead of moving through them one by one.

    thanks!

    Here's the code as it stands:

    Code:
    Option Compare Database
    Public Sub Command21_Click()
    Dim sTo As String
    Dim sSub As String
    Dim sBody As String
    Dim strRep As String
    Dim strDPath As String
    Dim strFName As String
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM [01 - 03 - Commission Summary Report - Sage One];", dbOpenSnapshot)
    If (rst.RecordCount <> 0) Then
    rst.MoveLast
    rst.MoveFirst
    Else
     MsgBox "There are no records in the table!"
     GoTo EndLine
    End If
    
    With rst
        While Not .EOF
        ' What report to send
        strRep = "01 - 01 - Commission Summary Report - Sage One"
        ' Initial Path
        strDPath = "C:\Users\DLSmith\Documents\Commissions Project\Reports Sent\"
        ' Filename
        strFName = .Fields("Name") & "_Order_" & .Fields("Month") & .Fields("Year") & "_" & Format(Date, "mm-dd-yyyy")
        'add file extension
        strFName = strFName & ".pdf"
        ' Output report as pdf
        DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
        ' Send the report to whoever
        sTo = .Fields("Manager Email")
        
        sSub = .Fields("Name") & "_Commission Statement_" & .Fields("Month") & "_" & .Fields("Year")
        
        sBody = "First name from some field" & vbCrLf & vbCrLf
        sBody = sBody & "Continue with message"
        
        Send_Email strDPath & strFName, sTo, sSub, sBody
        .MoveNext
        Wend
    End With
    EndLine:
    End Sub
    ' ----------------------------------------------------------------------------------------
    Private Sub Send_Email(strDoc As String, sTo As String, sSub As String, sBody As String)
    Dim sCC As String
    Dim sBCC As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim varPress As Variant
    strMess = "You are about to send an email message to " & sTo & vbCrLf & vbCrLf
    strMess = strMess & "Do you wish to continue?"
    strStyle = vbYesNo
    strTitle = "Send Notification"
    varPress = MsgBox(strMess, strStyle, strTitle)
    If varPress = vbYes Then
    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""
    With OutMail
    .To = sTo
    .CC = sCC
    .BCC = sBCC
    .Subject = sSub
    .Body = sBody
    .attachments.Add (strDoc)
    '.Display ' THis will display the email, but not send it
    .Send ' THis will send the email
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End If
    
    End Sub

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have to open the filtered report in print preview before the OutputTo method, then close the report after making PDF. Something like:

    DoCmd.OpenReport strRep, acViewPreview, "EmpID=" & rst!EmpID
    ...
    DoCmd.Close acReport, strRep
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    I added your recommended code, but I'm still getting all pages in the report instead of just the single employee. Did I enter it incorrectly or in the wrong place?

    thanks again!
    Code:
    Option Compare Database
    Public Sub Command21_Click()
    Dim sTo As String
    Dim sSub As String
    Dim sBody As String
    Dim strRep As String
    Dim strDPath As String
    Dim strFName As String
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM [01 - 03 - Commission Summary Report - Sage One];", dbOpenSnapshot)
    If (rst.RecordCount <> 0) Then
    rst.MoveLast
    rst.MoveFirst
    Else
     MsgBox "There are no records in the table!"
     GoTo EndLine
    End If
    
    With rst
        While Not .EOF
        ' What report to send
        strRep = "01 - 01 - Commission Summary Report - Sage One"
        ' Initial Path
        strDPath = "C:\Users\DLSmith\Documents\Commissions Project\Reports Sent\"
        ' Filename
        strFName = .Fields("Name") & "_Order_" & .Fields("Month") & .Fields("Year") & "_" & Format(Date, "mm-dd-yyyy")
        'add file extension
        strFName = strFName & ".pdf"
        ' Output report as pdf
        DoCmd.OpenReport strRep, acViewPreview, "Name=" & rst!Name
        DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
        DoCmd.Close acReport, strRep
        
        ' Send the report to whoever
        sTo = .Fields("Manager Email")
        
        sSub = .Fields("Name") & "_Commission Statement_" & .Fields("Month") & "_" & .Fields("Year")
        
        sBody = "First name from some field" & vbCrLf & vbCrLf
        sBody = sBody & "Continue with message"
        
        Send_Email strDPath & strFName, sTo, sSub, sBody
        .MoveNext
        Wend
    End With
    EndLine:
    End Sub
    ' ----------------------------------------------------------------------------------------
    Private Sub Send_Email(strDoc As String, sTo As String, sSub As String, sBody As String)
    Dim sCC As String
    Dim sBCC As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim varPress As Variant
    strMess = "You are about to send an email message to " & sTo & vbCrLf & vbCrLf
    strMess = strMess & "Do you wish to continue?"
    strStyle = vbYesNo
    strTitle = "Send Notification"
    varPress = MsgBox(strMess, strStyle, strTitle)
    If varPress = vbYes Then
    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""
    With OutMail
    .To = sTo
    .CC = sCC
    .BCC = sBCC
    .Subject = sSub
    .Body = sBody
    .attachments.Add (strDoc)
    '.Display ' THis will display the email, but not send it
    .Send ' THis will send the email
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End If
    
    End Sub

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Name is a reserved word. Really should not use reserved words as field names.

    Names make very poor unique identifiers. What if you have two Jane Smith?

    If it is a text type field, parameters need apostrophe delimiters. The filter criteria is in the wrong argument - I see my error in earlier example.

    DoCmd.OpenReport strRep, acViewPreview, , "[Name]='" & rst!Name & "'"

    Don't specify report name in OutputTo and it will use the open filtered report - I should have remembered that earlier.

    DoCmd.OutputTo acOutputReport, "", acFormatPDF, strDPath & strFName, False, "", 0
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    I'm not getting any errors, but the report still has all names instead of just one at a time. Could it be because they are all going to the same email? The emailed report would be for different employees, but emailed to the same manager. So 5 employees = 5 emails to one manager. Right now it's sending 5 emails, but they all have the same 5 page report with all employees included.

  12. #12
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    Do I need to do something to the actual report or query to have it pull only one person at a time?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maybe I don't understand what you want to send to whom. I don't know your data structure - so is hard to advise specifics. I can only offer concept.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    smithdam is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Location
    North Georgia
    Posts
    12
    Ok, let me ask this way then.

    I have a query that returns 5 rows of data. I built a report off of that query. The report is a 5 page report, one for each row of data in the query.

    I'm trying to use the code to go through the data one row at a time, creating a report that is one page instead of 5. Then email this one page.

    Is that something that can be done entirely in the code or do I need to do something with the query / report separate from coding?

    Thank you very much for the help. I'm stuck!

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the unique identifier for records in that report?

    Open a recordset of that query. Loop on the query and use the identifier as filter criteria for the OpenReport method.

    Now where do the email addresses come from?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  2. Adding Existing Data to Records
    By LukeJ Innov in forum Forms
    Replies: 3
    Last Post: 04-29-2013, 09:44 AM
  3. Replies: 3
    Last Post: 07-28-2011, 11:24 AM
  4. Adding columns to existing tables.
    By OrangePie in forum Access
    Replies: 1
    Last Post: 07-07-2011, 01:49 PM
  5. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums