Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I use MS Access 2003, SP2

    I have a membership database, and once a year I am required to send out reminder notices to members to review and update their contact details.

    In the past I have done this by merging an Access query into a Word document, printing reminders and then posting to members for their dealing.

    We are looking at sending the notices out via email.

    What I have done is to replicate the look and feel of the Word layout in an Access report, with a members details being contained on one page of the report.

    I would like to create either a PDF or Word document for each member, automatically naming the created file with details for that member's record, preferably surname and first name, resulting in file eg. AdamsJohn.pdf, BrownAlan.pdf, CollinsFred.pdf etc.

    Any suggestions gratefully received.

    Regards

  2. #2
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just a few observations

    You can certainly can do an emailing using Word 2003 and Access 2003 and use Outlook 2003 as the email client.

    First, create and run a MakeTable query in Access that gets you the records and columns you need and name the query something like: qryAnnualEmailing and have it create tblAnnualEmailing
    Next go into word and use the MailMerge Wizard to create an EMail merge using the table (tblAnnualEmailing) that you created in Access as the DataSource
    Be sure to fill in the subject and identify the field that contains the email address
    Make sure that Outlook is open
    Perform the Merge.

    Depending on your ISP/Mail Server, it may take a while (Some mail servers only allow a certain number of emails to be sent within a certain time frame (100 per 10 minutes, etc.)) but eventually Oiutlook will take care of sending them all out provide that you leave Outlook open

    If you want to automate all of this, you can do so from within Access by opening up an instance of Word, etc...but since you only do it once a year you may find that the effort involved in doing the coding is much more costly than just doing as I have illustrated above.

    Now this doesn't create a separate PDF but I think it accomplishes your goal of getting your emails out. Creating a separate PDF would involve the hassle of a) creating the PDF from withing Office 2003 b) Sending out emails with an attachment. Far more annoying than it sounds I'm afraid.

    So, that is how I would probably attack it given the frequency of your mailing.

    To simplify my life, when I have repeated mailings I need to do to specific groups I have chosen to use a subscription like Constant Contact. Upload, create and go...You can track it, it let's you know who opened it, what emails bounced, it is simple to update your list, people can unsubscribe, it puts the appropriate messages on there to comply with emailings, etc....so simple but it does cost a little $$

    Hope that helps.


    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Michael Holzinger View Post
    I use MS Access 2003, SP2

    I would like to create either a PDF or Word document for each member, automatically naming the created file with details for that member's record, preferably surname and first name, resulting in file eg. AdamsJohn.pdf, BrownAlan.pdf, CollinsFred.pdf etc.
    This can be done using VBA code to read through the appropriate recordset and for each record Open the report for the particular record. This creates the PDF file as long as the report has been defined as going to a PDF output such as PDF995. The filename can be established in the OpenEvent of the report by setting the Caption to the desired name. As each file is created you should use a Hans written function called SendMail (this should be able to be found in here by searching).
    I wrote a similar routine for a Organistion that had to send out an email to 650 outlets, there was a limit that could be sent at the one time.
    This is not a big task and will be well worth the effort as it can be run year after year for minimal effort each year.
    Hope this helps you get started.

  4. #4
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to both responses, which are equally valid.

    As I am not the one that will actually send the email, it will be the Treasurer of the Club, I'll opt for the creation by looping through the recordset, creating one pdf per member and forwarding for the Treasurer to 'do his thing'.

    Zinger

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Zinger,

    I just went through this process for our homeowner's association, with some Outlook help from other members of the lounge I have the working code shown below. This loops through a report I already had sending a particular OwnerID as a filter, creates a uniquely named .pdf file using PDFCreator {because it can be setup to create the file w/o any user prompts} and automatically creates an Outlook email item with the .pdf attached. Maybe you can get what you need from the code. BTW: I also have code to send all emails in the drafts folder automatically if you are interested.

    EDIT: I just read over this and realized it's not the latest version. I'm currently out in my RV using my laptop and thought I'd synced all my files over from the desktop...NOT! I'll add the latest code when I get home on Sunday! This code works but uses PrimoPDF which requires the user to enter information for each pdf saved...a real bummer when you have over 500 pdfs to create!

    EDIT: 11/14/10 - Ok, I'm back home and have posted the up-to-date code. Good Luck
    Code:
    'Declare Sleep API
    Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
    
    '                         +-------------------------+             +----------+
    '-------------------------|      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() ***
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Have you got the code for SwitchPrinters?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I suspect code is something like the code here.
    Regards
    John



  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by patt View Post
    Have you got the code for SwitchPrinters?
    Patt,

    Here it is:

    Code:
    '                          +---------------------+                 +----------+
    '--------------------------|  SwitchPrinters()   |-----------------| 07/30/10 |
    '                          +---------------------+                 +----------+
    'Called by     : Report_Open()  - From any form!
    '                Report_Close() - From any form!
    'Calls         : N/A
    'Function Calls: N/A
    'Globals Used  : N/A
    
    Sub SwitchPrinters(zSwitchToPtr As String)
    
      Dim prtName As Printer
      Dim iPrtNo  As Integer
      
      iPrtNo = 0
      
      For Each prtName In Application.Printers
         If prtName.DeviceName = zSwitchToPtr Then
           Exit For
         Else
           iPrtNo = iPrtNo + 1
         End If
      Next prtName
    
    '*** Uncomment next 2 lines for testing or visual verification of switch ***
    '  MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
    '         " " & Application.Printers(iPrtNo).DeviceName
             
      Application.Printer = Application.Printers(iPrtNo)
    
    
    End Sub    '*** SwitchPrinters ***
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by RetiredGeek View Post
    Patt,

    Here it is:

    Code:
    '                          +---------------------+                 +----------+
    '--------------------------|  SwitchPrinters()   |-----------------| 07/30/10 |
    '                          +---------------------+                 +----------+
    'Called by     : Report_Open()  - From any form!
    '                Report_Close() - From any form!
    'Calls         : N/A
    'Function Calls: N/A
    'Globals Used  : N/A
    
    Sub SwitchPrinters(zSwitchToPtr As String)
    
      Dim prtName As Printer
      Dim iPrtNo  As Integer
      
      iPrtNo = 0
      
      For Each prtName In Application.Printers
         If prtName.DeviceName = zSwitchToPtr Then
           Exit For
         Else
           iPrtNo = iPrtNo + 1
         End If
      Next prtName
    
    '*** Uncomment next 2 lines for testing or visual verification of switch ***
    '  MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
    '         " " & Application.Printers(iPrtNo).DeviceName
             
      Application.Printer = Application.Printers(iPrtNo)
    
    
    End Sub    '*** SwitchPrinters ***
    Thanks R.G.

  10. #10
    New Lounger
    Join Date
    Oct 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Zinger,

    I just went through this process for our homeowner's association, with some Outlook help from other members of the lounge I have the working code shown below. This loops through a report I already had sending a particular OwnerID as a filter, creates a uniquely named .pdf file using PDFCreator {because it can be setup to create the file w/o any user prompts} and automatically creates an Outlook email item with the .pdf attached. Maybe you can get what you need from the code. BTW: I also have code to send all emails in the drafts folder automatically if you are interested.

    EDIT: I just read over this and realized it's not the latest version. I'm currently out in my RV using my laptop and thought I'd synced all my files over from the desktop...NOT! I'll add the latest code when I get home on Sunday! This code works but uses PrimoPDF which requires the user to enter information for each pdf saved...a real bummer when you have over 500 pdfs to create!

    EDIT: 11/14/10 - Ok, I'm back home and have posted the up-to-date code. Good Luck
    Code:
    'Declare Sleep API
    Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
    
    '                         +-------------------------+             +----------+
    '-------------------------|      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() ***
    Dear RetiredGeek,

    I don't know if this is still applicable, as it's almost a year since this thread was posted, but I'll try and if you can help me I would greatly appreciate it.
    I'm not a professional programmer, just trying to figure out something very similar to what you did.
    I want to send Outstanding balances to each client at the end of every month.
    I currently have a Access report that has the Outstanding Balance for all my clients together in one report.
    So I need to split it to seperate pdfs and email to that specifice client.
    Can I use the code above? What do I need to do?
    Thank you so much,
    I appreciate any help.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I know we'd all like to stay with Access2003, but there comes a time when you need to consider upgrading. For one thing, you can now easily send an email with a report attached produced in PDF format. BTW, Acess2003 is up to SP3.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    You need to change the lines as indicated below:

    'Declare Sleep API Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
    ' +-------------------------+ +----------+
    '-------------------------| 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:"

    '*** Change line below to the path where the PDFs should be written assumes
    '*** it is located below the directory holding the .dbf file!
    zBillPath = zGetDBPath() & "EmailBills\"
    ClearPDFDirectory
    strDfltPrt = Application.Printer.DeviceName SwitchPrinters "PDFCreator"
    Set appOL = CreateObject("Outlook.Application")
    Set dbName = CurrentDb()

    '*** change Owners below to the name of your Client's table ***
    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
    '*** change OwnerID in both instances below to the key of your Client's table
    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.

    '*** Change "rptAnnualBilling" to the name of your report ***
    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
    '*** Change the 2nd line below to create the file name as you want them
    Name zBillPath & "rptAnnualBilling.pdf" As _ zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
    On Error GoTo 0
    '******* Begin Send Email
    '*** Change the parts of the email below to read as you want.
    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() ***
    ------------------
    Don't forget to install PDFCreator and configure it to save to a file.

    This should give you a start. After you have the code copied and changed as indicated post back if you are still having problems and include your code in the post.

    Update: Please note that the firs time I posted this I tried not to use the code tags and it took out all the line breaks. I went back in and used the Quote tags because they allow bolding, hopefully I got all the line breaks back in the right place. It might be good to print out the code in the previous post then use the bolded lines in this post to guide you where changes are necessary.
    Last edited by RetiredGeek; 2011-10-06 at 20:36.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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