Merging from Access to Outlook. Office 2003

Topic

New Reply

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!
Thanks,
itconc

Reply | Quote

Replies

Itconc,

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:

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() ***

‘                         +————————-+             +———-+
‘————————-|   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()
   Loop
   
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
     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 ***

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks.
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.

Thanks,

itconc

Reply | Quote

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.

Reply | Quote