Improving my mail merge! 1

[colinmitton]

Below is my code to run a mail merge in word 2010 using a template with the links to the DB and relevent field inserted.

It works great and I'm happy with it, well nearly!

For every different mail merge I do from different table sources i.e. 3rd party business or contacts or clients, etc...

Will require its own merge template with the relevent fields for the different tables in place. this could end up with loads of different template letters which will get out of hand. There is a way of creating 'generic' fields in word but how do I tell access that a field from a table to the generic name (i.e. 'cl_Address1' from the client table to 'add1' field in the word document).

I hope this makes sense. my code...

Private Sub Cmd_merge_to_word_Click()
Dim strBusID As String
Dim strContID As String
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document
' Get the Info for the where statement on the SQLStatement
    strBusID = "[3rdPartyBusinessID] = " & [3rdPartyBusinessID] & ""
    strContID = "[3rdPartyContactID] = " & [3rdPartyContactID] & ""
    
' Open the merge document and give it a name!
Set wdMMDoc = wdApp.Documents.Add("c:\Access\Merge.dotx")
'    wdApp.Visible = False
    wdApp.Visible = True
' Connect to datasource and run sqlstatement to pick the info from the form
    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=c:\Access\DBv2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strBusID & " And " & strContID
' Run the merge
    wdMMDoc.MailMerge.Execute
' Close the Merge Document
    wdMMDoc.Close False
    wdApp.Visible = True
' Bring the merged letter to the front
    wdApp.Activate
' tidy up
    Set wdApp = Nothing
    Set wdMMDoc = Nothing
End Sub

 

[PHV]

I'd use 'generic' alias in the SELECT clause, eg:
SELECT cl_Address1 AS add1, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

PHV,

Thanks for the quick reply, so would I do the 'full' SQL Statement instead of doing what I've done above a query to link the relative tables. Or have I got the wrong eng of the stick?

 

[colinmitton]

New problem, I know have a string over 255 Character and get an error '9105' when I run the code?

How can I shorten the code?

    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=c:\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT tbl_3rd_P_Projects.ProjectID, tbl_3rd_P_Projects.ProjectTitle AS Reference, tbl_3rd_P_Projects.a3rdPBusID, tbl_3rd_P_Projects.a3rdPContactID, tbl_3rd_P_Projects.DFAEmployeeID, tbl_3rd_P_Business.[3rdPartyBusinessName] AS BusName," & _
        "tbl_3rd_P_Business.Address1 AS Add1, tbl_3rd_P_Business.Address2 AS Add2, tbl_3rd_P_Business.City AS Add_City, tbl_3rd_P_Business.COuntyLookup AS Add_County, tbl_3rd_P_Business.PostCode AS Add_PostCode, tbl_3rd_P_Contacts.SalutationsList AS Add_Sal," & _
        "tbl_3rd_P_Contacts.FirstName AS Add_FName, tbl_3rd_P_Contacts.LastName AS Add_LName, tbl_DFA_Employees.FirstName AS EMP_FName, tbl_DFA_Employees.LastName AS EMP_LName, tbl_DFA_Employees.Initials As EMPInitials, tbl_DFA_Employees.EmailAddress AS EMP_Email," & _
        "tbl_DFA_Employees.DDI AS EMP_DDI, tbl_DFA_Employees.Department AS EMP_Dept, tbl_DFA_Employees.JobTitle AS EMP_Title" & _
        "FROM (tbl_3rd_P_Business INNER JOIN (tbl_DFA_Employees INNER JOIN tbl_3rd_P_Projects ON tbl_DFA_Employees.[EmployeeID] = tbl_3rd_P_Projects.[DFAEmployeeID]) ON _tbl_3rd_P_Business.[3rdPartyBusinessID] = tbl_3rd_P_Projects.[a3rdPBusID]) INNER JOIN tbl_3rd_P_Contacts ON tbl_3rd_P_Projects.a3rdPContactID = tbl_3rd_P_Contacts.[3rdPartyContactID] Where " & strBusID & " And " & strContID

As you can see its massive!

 

[PHV]

Why not modify the SQL code of qry_3rd_P_address_merge ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

PHV,

Thanks, I realised that at 10pm last night, tried using sqlstatement2: and things like that but to no avail then it finally dawned on me! Was too tired to post it by the time I finished testing!

Thanks once more!