Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Emailing Report from Access 2010 to separate people!

    Hi, Im new here so please be kind, ok my dilema is probably quite simple:

    I have a table Called ImportedTable with the following fields: ACREF (text field), INVRECDATE (date field), REPNAME (text Filed), Amount (currency field), Email (text field).

    I have a report called STATEMENT based on the above table. Basically the report is a statment listing the invoices outstanding and is grouped on the REPNAME field, so in the table there could be 10 records for REPNAME1, 15 for REPNAME2, 3 for REPNAME3 and so on, the report show each REPNAMEs outstanding invoices and amounts, each REPNAME on a separate page.

    Can someone please help me out and tell me the best way how I can email each of those pages to the corresponding email address in the EMAIL field in the Table.

    I hope I made it clear enough!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 Posts
    SteelJay,

    Welcome to the Lounge as a new poster.

    Basically you need to run your report from VBA code with a Where statement so you only get one Rep at a time this is done in a loop to work through all of them. The following code was written for Access 2003 so it has some code for creating a PDF which Access 2010 will do directly.

    The code opens an Outlook session, then opens the database as a recordset, loops through the recordset {looking for records with an email address} runs the report based on OwnerID if an email address is found. Writes the report to a .PDF file, creates an email then attaches the .PDF. I hope this isn't too complicated but it is the only example I have. Post back with any questions.
    Code:
    '                         +-------------------------+             +----------+
     '-------------------------|      EmailBills()       |-------------| 01/11/12 |
     '                         +-------------------------+             +----------+
     'Requires : PDFCreator {Open Source PDF Printer Driver}
     '           Sleep      {Windows API Function Declaration}
     'Called By: Switchboard
     'Calls: ClearPDFDirectory()
     '       SetDateForBills()
     '       [Utilities] SwitchPrinters()
     '       [Utilities] zGetDBPath()
    
     Sub EmailBills()
    
        Dim dbName     As Database
        Dim rst        As Recordset
        Dim lRecNo     As Long
        Dim lBillCnt   As Long
        Dim zWhere     As String
        Dim zMsgBody   As String
     #If LateBinding = 0 Then    'Early Binding
        Dim appOL      As Outlook.Application
        Dim miMail     As Outlook.mailitem
     #Else
        Dim appOL      As Object
        Dim miMail     As Object
     #End If
    
        Dim oMyAttach  As Object
        Dim zAttFN     As String
        Dim zBillPath  As String
       
        Forms![Switchboard].Visible = False
        If Not SetDateForBills() Then
          Forms![Switchboard].Visible = True
          Exit Sub
        End If
       
        MsgBox "Please Note:" & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is Closed the created Emails " & vbCrLf & _
               "will be sent to the INBOX folder." & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is OPEN {recommended} the created Emails " _
               & vbCrLf & "will be sent to the DRAFTS folder." & vbCrLf & vbCrLf & _
               "When OUTLOOK is properly set press OK", _
               vbOKOnly + vbInformation, _
               "IMPORTANT INFORMATION:"
              
        zBillPath = zGetDBPath() & "EmailBills\"
              
        ClearPDFDirectory
        strDfltPrt = Application.Printer.DeviceName
        SwitchPrinters "PDFCreator"
       
        Set appOL = CreateObject("Outlook.Application")
        Set dbName = CurrentDb()
        Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
        rst.MoveFirst
       
        lBillCnt = 0
        zMsgBody = "Please find your WPOA annual dues statement attached." & _
                   vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                   vbCrLf & "Attachment: "
        Do
          If (rst![EMailDocs] And rst![EMail] <> "") Then
         
            zWhere = "[OwnerID] = " & Str(rst![OwnerID])
        
     'Note: If acNormal is selected the report is send automatically to the
     '      Default printer!
     '      If acPreview is selected the report is sent to the screen.
    
            DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
       
     '******* Rename file with OwnerID
    
     On Error GoTo WaitForPDFCreator
     Try_Again:
    
            Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
              Sleep 1250           '** wait 1.25 secs before trying again **
            Loop
           
            Name zBillPath & "rptAnnualBilling.pdf" As _
                 zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
     On Error GoTo 0
     '******* Begin Send Email
    
     #If LateBinding = 0 Then
            Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
     #Else
            Set miMail = appOL.CreateItem(0)
     #End If
    
            With miMail
                .To = rst![EMail]
                .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
                .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                        " Owner: " & rst![OwnerLName]
                .ReadReceiptRequested = True
                zAttFN = zBillPath & "Bill" & _
                         Trim(Str(rst![OwnerID])) & ".pdf"
                Set oMyAttach = miMail.Attachments.Add(zAttFN)
                .Save
            End With   'miMail
    
            Set miMail = Nothing
            lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
    
     '******* End Send Email
    
          End If
         
          rst.MoveNext        '*** Move to Next Record ***
       
        Loop Until rst.EOF
       
        MsgBox Format(lBillCnt, "#,###") & " Email Bills Created." & _
               vbCrLf & vbCrLf & _
               "Maximize Outlook and Press F8 and select the" & _
               "SendAllDrafts macro then click Run." & _
               vbCrLf & vbCrLf & _
               "If Outlook wasn't open when you created the Email" & _
               vbCrLf & "Bills you will have to move them to the" & _
               vbCrLf & "Drafts folder from the Inbox BEFORE you" & _
               vbCrLf & "run the macro!", vbOKOnly + vbInformation, _
               "Next Step:"
        GoTo GetOut
    
     WaitForPDFCreator:
        Select Case Err.Number
              Case 75
                  Sleep 0.75  '*** Wait another 3/4 second. ***
                  Resume Try_Again
              Case Else
                  MsgBox "Module:" & vbTab & "BillingsCode" & vbCrLf & _
                         "Routine:" & vbTab & "EmailMailBills" & vbCrLf & _
                         "Error: " & Err.Number & " " & _
                         Err.Description, vbCritical + vbOKOnly, _
                         "Unexpected Error:"
                  Resume GetOut
        End Select
       
     GetOut:
        Set rst = Nothing     '*** Close RecordSet ***
        Set oMyAttach = Nothing
        Set miMail = Nothing
        Set appOL = Nothing
       
        SwitchPrinters strDfltPrt
        Forms![Switchboard].Visible = True
       
     End Sub                   '*** EmailBills() ***
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Further to RG's response, in Access 2010 you can remove all references to Outlook, and creating the PDF and just use code like this, assuming you have defined the variables, and assigned values to them

    DoCmd.SendObject acReport, stDocName, acFormatPDF, strRepEmail, , , strSubject, strMessage, True
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Apr 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RG hopefully its not too complicated to sort from this, ill give it a look at, be prepared for questions Cheers

  5. #5
    New Lounger
    Join Date
    Apr 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    bit confused with yours though John , can you elaborate abit more on what you mean by removing the references to outlook and creating the PDF, and would the docmd.sendobject send only the page of the report appertaining to the REPNAME field? Thanks for your help much appreciated

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Yes..you still open the filtered report (as RG said to do) but only in Preview Mode, then use the Docmd.SendObject while the report is open in Preview Mode, then Close it and move to the next record.

    Here is a modifed version of RGs code, but I have not actually tried to run it, so please excuse any typos.

    Code:
    Sub EmailBills()
    
        Dim dbName As Database
        Dim rst As Recordset
        Dim lRecNo As Long
        Dim lBillCnt As Long
        Dim zWhere As String
        Dim zMsgBody As String
        Dim zEmail As String
        Dim zSubject As String
        Dim zDocname As String
        zDocname = "rptAnnualbilling"
        Forms![Switchboard].Visible = False
        If Not SetDateForBills() Then
            Forms![Switchboard].Visible = True
            Exit Sub
        End If
        Set dbName = CurrentDb()
        Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
        rst.MoveFirst
    
        lBillCnt = 0
      
        Do While Not rs.EOF
    
            If rst![EMail] <> "" Then
    
                zWhere = "[OwnerID] = " & Str(rst![OwnerID])
                DoCmd.OpenReport zDocname, acPreview, , zWhere
                zEmail = rst![EMail]
                zSubject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
                zMsgBody = "Hi " & rst![OwnerLName] & vbCrLf & "Please find your WPOA annual dues statement attached."
                DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
                DoCmd.CloseReport zDocname, acSaveNo
                lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
            End If
    
            rst.MoveNext        '*** Move to Next Record ***
        Loop
    
        MsgBox Format(lBillCnt, "#,###") & " Email Bills Created."
        Set rst = Nothing     '*** Close RecordSet ***
        
        Forms![Switchboard].Visible = True
    
    End Sub
    Last edited by johnhutchison; 2012-04-19 at 19:06.
    Regards
    John



  7. The Following User Says Thank You to johnhutchison For This Useful Post:

    umarus (2012-10-12)

  8. #7
    New Lounger
    Join Date
    Sep 2012
    Posts
    8
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How can I link it to checkboxes

    Hi everyone!
    Basically I have a List of student with Yes/No chekboxes , OnClick I want so send reports (from my report for all students) only to the cheked emails.
    Need a help, Regards

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 Posts
    You need to change the If statement:

    If rst![EMail] <> "" Then

    To something like:
    If rst![EMail] <> "" AND rst![checkbox field name] = True Then
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    New Lounger
    Join Date
    Sep 2012
    Posts
    8
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thanks very much!

    One more time thank you very much!
    Works perfect but after fixed one typo (rst),
    Then I changed "DoCmd.CloseReport" because it din't work with 2010

    Also I deleted few rows. Do I need it?

    Forms![Switchboard].Visible = False
    If Not SetDateForBills() Then
    Forms![Switchboard].Visible = True
    Exit Sub
    End If
    ...
    ...
    Forms![Switchboard].Visible = True

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 Posts
    No those lines are to redisplay the Switchboard in my application. If you are not using a switchboard they are not necessary. \

    I don't know why the DoCmd.CloseReport didn't work in 2010 though.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    umarus (2012-10-12)

  13. #11
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I am trying to do something similar (emailing parts of a report to different email addresses) but I keep getting an error with your code. Please can I get any urgent help with this?

    I am using Access 2007.

    Quote Originally Posted by RetiredGeek View Post
    SteelJay,

    Welcome to the Lounge as a new poster.

    Basically you need to run your report from VBA code with a Where statement so you only get one Rep at a time this is done in a loop to work through all of them. The following code was written for Access 2003 so it has some code for creating a PDF which Access 2010 will do directly.

    The code opens an Outlook session, then opens the database as a recordset, loops through the recordset {looking for records with an email address} runs the report based on OwnerID if an email address is found. Writes the report to a .PDF file, creates an email then attaches the .PDF. I hope this isn't too complicated but it is the only example I have. Post back with any questions.
    Code:
    '                         +-------------------------+             +----------+
     '-------------------------|      EmailBills()       |-------------| 01/11/12 |
     '                         +-------------------------+             +----------+
     'Requires : PDFCreator {Open Source PDF Printer Driver}
     '           Sleep      {Windows API Function Declaration}
     'Called By: Switchboard
     'Calls: ClearPDFDirectory()
     '       SetDateForBills()
     '       [Utilities] SwitchPrinters()
     '       [Utilities] zGetDBPath()
    
     Sub EmailBills()
    
        Dim dbName     As Database
        Dim rst        As Recordset
        Dim lRecNo     As Long
        Dim lBillCnt   As Long
        Dim zWhere     As String
        Dim zMsgBody   As String
     #If LateBinding = 0 Then    'Early Binding
        Dim appOL      As Outlook.Application
        Dim miMail     As Outlook.mailitem
     #Else
        Dim appOL      As Object
        Dim miMail     As Object
     #End If
    
        Dim oMyAttach  As Object
        Dim zAttFN     As String
        Dim zBillPath  As String
       
        Forms![Switchboard].Visible = False
        If Not SetDateForBills() Then
          Forms![Switchboard].Visible = True
          Exit Sub
        End If
       
        MsgBox "Please Note:" & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is Closed the created Emails " & vbCrLf & _
               "will be sent to the INBOX folder." & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is OPEN {recommended} the created Emails " _
               & vbCrLf & "will be sent to the DRAFTS folder." & vbCrLf & vbCrLf & _
               "When OUTLOOK is properly set press OK", _
               vbOKOnly + vbInformation, _
               "IMPORTANT INFORMATION:"
              
        zBillPath = zGetDBPath() & "EmailBills\"
              
        ClearPDFDirectory
        strDfltPrt = Application.Printer.DeviceName
        SwitchPrinters "PDFCreator"
       
        Set appOL = CreateObject("Outlook.Application")
        Set dbName = CurrentDb()
        Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
        rst.MoveFirst
       
        lBillCnt = 0
        zMsgBody = "Please find your WPOA annual dues statement attached." & _
                   vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                   vbCrLf & "Attachment: "
        Do
          If (rst![EMailDocs] And rst![EMail] <> "") Then
         
            zWhere = "[OwnerID] = " & Str(rst![OwnerID])
        
     'Note: If acNormal is selected the report is send automatically to the
     '      Default printer!
     '      If acPreview is selected the report is sent to the screen.
    
            DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
       
     '******* Rename file with OwnerID
    
     On Error GoTo WaitForPDFCreator
     Try_Again:
    
            Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
              Sleep 1250           '** wait 1.25 secs before trying again **
            Loop
           
            Name zBillPath & "rptAnnualBilling.pdf" As _
                 zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
     On Error GoTo 0
     '******* Begin Send Email
    
     #If LateBinding = 0 Then
            Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
     #Else
            Set miMail = appOL.CreateItem(0)
     #End If
    
            With miMail
                .To = rst![EMail]
                .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
                .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                        " Owner: " & rst![OwnerLName]
                .ReadReceiptRequested = True
                zAttFN = zBillPath & "Bill" & _
                         Trim(Str(rst![OwnerID])) & ".pdf"
                Set oMyAttach = miMail.Attachments.Add(zAttFN)
                .Save
            End With   'miMail
    
            Set miMail = Nothing
            lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
    
     '******* End Send Email
    
          End If
         
          rst.MoveNext        '*** Move to Next Record ***
       
        Loop Until rst.EOF
       
        MsgBox Format(lBillCnt, "#,###") & " Email Bills Created." & _
               vbCrLf & vbCrLf & _
               "Maximize Outlook and Press F8 and select the" & _
               "SendAllDrafts macro then click Run." & _
               vbCrLf & vbCrLf & _
               "If Outlook wasn't open when you created the Email" & _
               vbCrLf & "Bills you will have to move them to the" & _
               vbCrLf & "Drafts folder from the Inbox BEFORE you" & _
               vbCrLf & "run the macro!", vbOKOnly + vbInformation, _
               "Next Step:"
        GoTo GetOut
    
     WaitForPDFCreator:
        Select Case Err.Number
              Case 75
                  Sleep 0.75  '*** Wait another 3/4 second. ***
                  Resume Try_Again
              Case Else
                  MsgBox "Module:" & vbTab & "BillingsCode" & vbCrLf & _
                         "Routine:" & vbTab & "EmailMailBills" & vbCrLf & _
                         "Error: " & Err.Number & " " & _
                         Err.Description, vbCritical + vbOKOnly, _
                         "Unexpected Error:"
                  Resume GetOut
        End Select
       
     GetOut:
        Set rst = Nothing     '*** Close RecordSet ***
        Set oMyAttach = Nothing
        Set miMail = Nothing
        Set appOL = Nothing
       
        SwitchPrinters strDfltPrt
        Forms![Switchboard].Visible = True
       
     End Sub                   '*** EmailBills() ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •