Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Report to Email via PDF

    I have a report that has 96 pages and includes the email address for each person. I am trying to send the individual/multiple pages to each email address (person) via Outlook separately after converting to a PDF (I have the Adobe Acrobat 9 Pro). I have a attached a couple of the pages in PDF Format.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by brandy9271 View Post
    I have a report that has 96 pages and includes the email address for each person. I am trying to send the individual/multiple pages to each email address (person) via Outlook separately after converting to a PDF (I have the Adobe Acrobat 9 Pro). I have a attached a couple of the pages in PDF Format.
    Here's how I do it.

    I make sure the recordsource for my report is a named query. So if report is "rptWhatever", I make the query "qryrptWhatever" (the name is unimportant, it is just my personal standard). Then from a form I create a recordset of everyone who I want to get the report. I then cycle through the recordset and get a person, then I reformulate the qryrptWhatever so the Criteria only selects that person, then I use the DoCmd.SendObject method to send the report to this person. Then I move-on to the next person in the recordset.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Mark, How would this work if you are sending to over 96 people? It sounds like it would take just as much time as print and mail.
    Totally confused! I want to be able to either send them to Outlook into the drafts folder to review prior to sending or just send out straight from Access.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    We use essentially the same approach as Mark, although we save the PDF as a file and attach it to an email after the report generation is complete. And we send it to over 800 persons. If you are using Access 2007 or 2010, you don't actually need Acrobat, as you can export directly to a PDF file.
    Wendell

  5. #5
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Wendell, the report has 96 individual schedules that need to go to 96 different people. Using the "Acrobat - Create and Attach to Email" will send as one file, I need multiple emails.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts

    Code Example

    Here's the code I use to email annual billings out to 250+ owners in our HOA. The same report is used to either do the Email bills, the printed ones for those who don't have email, and a full listing for the Manager to file away.

    Code:
    '                         +-------------------------+             +----------+
     '-------------------------|      EmailBills()       |-------------| 11/03/10 |
     '                         +-------------------------+             +----------+
     '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
        Dim appOL      As Outlook.Application
        Dim miMail     As Outlook.mailitem
        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
    
            Set miMail = appOL.CreateItem(olMailItem)
            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() ***
    P.S. This code is written for Access 2003 that doesn't have the direct to PDF method in VBA.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    brandy9271 (2011-10-26)

  8. #7
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am using 2007 and have no clue on VBA (That is the next set of classes I want to take). Is there anything I need to change and where do I put this coding in my report?

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by brandy9271 View Post
    I am using 2007 and have no clue on VBA (That is the next set of classes I want to take). Is there anything I need to change and where do I put this coding in my report?
    Brandy,

    Yes there is quite a bit which would need to be changed plus a couple of things I didn't include in the example. The code is designed to run from a switchboard and requires PDFCreator {open source} and some Windows OS calls. If you don't have a clue about VBA this is probably a bit advanced for you to accomplish. And w/o a copy of your database impossible for me to do either. Sorry.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by brandy9271 View Post
    Wendell, the report has 96 individual schedules that need to go to 96 different people. Using the "Acrobat - Create and Attach to Email" will send as one file, I need multiple emails.
    The trick here is to filter the report as you create each individual PDF file - and as RG indicates, you will need VBA to do that. We run the VBA from a form similar to what RG does.
    Wendell

  11. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by brandy9271 View Post
    Mark, How would this work if you are sending to over 96 people? It sounds like it would take just as much time as print and mail.
    Totally confused! I want to be able to either send them to Outlook into the drafts folder to review prior to sending or just send out straight from Access.
    I forgot to mention that this technique works only with Access2007 or later. Fortunately one of the other fellows caught it.

    It is basically all automatic, done via code. It doesn't require any outside add-ins to create the pdf or send the email. The code is really pretty simple. If you need it, send me the SQL behind your report, making sure the ID# and email of the person is included in the query, and I'll work-up an example for you.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #11
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Mark,

    If you could provide the code that would be wonderful. Once I have code if you could let me know if I am creating a Macro or adding as an event in the report.

    Below is the SQL from the Query "Schedule by Individual", The ID# would be [Employee ID] and the email addres is [E-mail Address].

    SELECT [Contacts Extended].[Employee ID], [Contacts Extended].FULLNAME, [Contacts Extended].[First Name], [Contacts Extended].[Last Name], [Contacts Extended].Address, [Contacts Extended].City, [Contacts Extended].[State/Province], [Contacts Extended].[ZIP/Postal Code], SCHEDULE.POSITION, SCHEDULE.EVENTDATE, SCHEDULE.STARTTIME, SCHEDULE.ENDTIME, [Contacts Extended].[Secondary Address], [Contacts Extended].[Secondary City], [Contacts Extended].[Secondary State/Province], [Contacts Extended].[Secondary ZIP/Postal Code], [Contacts Extended].[Secondary Address Timeframe], [Contacts Extended].[E-mail Address], SCHEDULE.ROLE
    FROM [Contacts Extended] INNER JOIN SCHEDULE ON [Contacts Extended].FULLNAME = SCHEDULE.FULLNAME;


    Thank you again!

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I'll leave Mark to show you the code.
    Just a comment here, are you really joining both those tables on the Fullname?

Posting Permissions

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