Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Houston, Texas, USA
    Thanked 0 Times in 0 Posts

    Angry Merging from Access to Outlook. Office 2003

    I have to do a mail merge from Access to Outlook via Word. A simple merge works great. However, I have to contact about 2000 vendors with each one having multiple users.
    I want the vendor listed once and below the description in the Word Letter I want the list of the users for that vendor.

    Is there a way to do so? I have the data in 2 different tables in Access.

    Please help!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,606 Times in 1,450 Posts

    What I did for our Homeowners Association billing was to create a report with all the necessary information. In our situation this included Name, Address, List of Properties, Boat Docks, etc. along with the assessments for each and a grand total. This was a very involved Report containing several subreports and queries for totals.

    Once that was created I wrote VBA to run through the database selecting only those owners who had both an email address and indicated they would accept billing via email. Then ran the report using the filter option to create a single owner report, printed it to a PDF file then created an email and attached the PDF.

    Here's the VBA:
    '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, _
       zBillPath = zGetDBPath() & "EmailBills\"
       strDfltPrt = Application.Printer.DeviceName
       SwitchPrinters "PDFCreator"
       Set appOL = CreateObject("Outlook.Application")
       Set dbName = CurrentDb()
       Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
       lBillCnt = 0
       zMsgBody = "Please find your WPOA annual dues statement attached." & _
                  vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                  vbCrLf & "Attachment: "
         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
           Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
             Sleep 1250           '** wait 1.25 secs before trying again **
           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)
           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
       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
       Set rst = Nothing     '*** Close RecordSet ***
       Set oMyAttach = Nothing
       Set miMail = Nothing
       Set appOL = Nothing
       SwitchPrinters strDfltPrt
       Forms![Switchboard].Visible = True
    End Sub                   '*** EmailBills() ***
    '                         +-------------------------+             +----------+
    '-------------------------|   ClearPDFDirectory()   |-------------| 10/28/10 |
    '                         +-------------------------+             +----------+
    'Called By: EmailBills()
    'Calls: N/A
    'Purpose: Clear out directory so that the NAME command doesn't cause errors!
    Sub ClearPDFDirectory()
       Dim zEmailBillFN   As String
       Dim zEmailBillPath As String
       zEmailBillPath = zGetDBPath() & "EmailBills\"
       zEmailBillFN = Dir(zEmailBillPath & "*.pdf")
       Do Until zEmailBillFN = ""
         Debug.Print zEmailBillFN
         Kill zEmailBillPath & zEmailBillFN
         zEmailBillFN = Dir()
    End Sub                 '*** ClearPDFDirectory() ***
    '                          +---------------------+                 +----------+
    '--------------------------|  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
           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 ***
    Last edited by RetiredGeek; 2011-05-03 at 16:26.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Houston, Texas, USA
    Thanked 0 Times in 0 Posts
    What I am looking for is a way to merge from word, with a query containing many vendors with their users (one to many). The vendors and their users are in Access. I can merge easily with each vendor. However, I also need to get the individual user into the merge. I do not want to write code for that.
    I can create a report and make a pdf file out of it. The question is, how to I get the right pdf to the correct email.
    If you have any answer for that, please let me know.



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts
    I presume your two tables contain the Vendors in one table, and the Users, with a link to a Vendor in another table. (If not, post back, as your problem becomes more complicated.) Do you want to send an email to each of the vendors, or each of the Users? If you want to send it to each user, then you need to have an email address in each User record. Otherwise the email address should be in the Vendors table, and you only have to send 2000 or so emails. Your initial post suggested that you wanted to simply list the vendors for each user - if that's the case you can create a report that does a group by Vendor and export that out to either PDF or RTF. That presents some challenges however, as you don't have the ability to output to PDF with an automated file name in Access 2003, so RTF may be the better choice. To only use Access, you would need to automate Outlook, create an email body and attach the appropriate file and send it. That was the approach that RetiredGeek was suggesting.

    You indicate in your response, that you really want to do this as a Word merge. Word does have the ability to create a merge document that can be either sent as the body of an email, or as an attachment to an email. In that case you need to look at doing catelog merges where you are putting several User records into a single merge document. It is not as trivial as it might seem but can be done. Another option is to Automate the merge from Access, which gives you some additional tools. However it does involve writing some rather complex code. Let us know if you want to pursue that option and we will give you some links that tell you how to do that. In the meantime, take a look at Macropod's Mail Merge Tutorial in the Word forum. It contains extensive advice on how to proceed with various kinds of merges.

Posting Permissions

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