Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mail merge with ms access 2007 to word 2007

Posted on 2011-10-13
6
Medium Priority
?
407 Views
Last Modified: 2012-05-12
I'm trying to create a mail merge using MS access 2007 to word 2007.  The code attached doesn't work anymore but it does work on 2003 and below.  I need this code to work with 2007.

Dim myWord As Object
Dim sql As String

sql = "select * from qryLetterCustomer where loadid = '" & strLoadId & "' and lettersent = false and province <> 'QC'"

DoCmd.Echo True, "GENERATING MASS MAIL LETTER....Please Wait......Thank You...."
DoCmd.Hourglass True

Set myWord = CreateObject("Word.Application")
myWord.Documents.Add EngLetter, , True
myWord.Application.DisplayAlerts = wdAlertsNone
   
    With myWord.ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource Name:=CurrentDb.Name, sqlstatement:=sql, SubType:=8
            .ViewMailMergeFieldCodes = False
            .Destination = wdSendToNewDocument
           .Execute True
    End With
       
    myWord.Application.Visible = True
    myWord.Application.WindowState = wdWindowStateMaximize
   
Set myWord = Nothing

DoCmd.Hourglass False
DoCmd.Echo True, Null

Open in new window

0
Comment
Question by:Lucas
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 36962937
Please check that you are not missing a reference file.

In Visual Basic check references.  You may only need to add the current Word library to fix this.
0
 
LVL 13

Author Comment

by:Lucas
ID: 36963221
I did.  I tried the following as well by adding the MS Word 2007 reference

Dim mywrd As New Word.Application
Dim mydoc As Word.Document
Dim sql As String

DoCmd.RunCommand acCmdWordMailMerge

sql = "select * from qryLetterCustomer where loadid = 'L11-3327' and lettersent = false and province <> 'QC'"

mywrd.Documents.Open (EngLetter)
Set mydoc = ActiveDocument
mywrd.DisplayAlerts = wdAlertsNone
mywrd.Visible = True
With mydoc.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=CurrentDb.Name, sqlstatement:=sql, LinkToSource:=True, SubType:=8
    .ViewMailMergeFieldCodes = False
    .Destination = wdSendToNewDocument
   .Execute
End With

Set mywrd = Nothing
     
    

Open in new window

0
 
LVL 12

Expert Comment

by:danishani
ID: 36963648
You are using Late binding, so no references needed. However you are using Word Constants, which need the Word Object Library to get it to work.

What you might try convert them into Numeric equivelants.

In case you want to go for Early binding, try this:
Dim mywrd As Word.Application
Dim mydoc As Word.Document
Dim sql As String

Set mywrd = New Word.Application
DoCmd.RunCommand acCmdWordMailMerge

sql = "select * from qryLetterCustomer where loadid = 'L11-3327' and lettersent = false and province <> 'QC'"

mywrd.Documents.Open (EngLetter)
Set mydoc = ActiveDocument
mywrd.DisplayAlerts = wdAlertsNone
mywrd.Visible = True
With mydoc.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=CurrentDb.Name, sqlstatement:=sql, LinkToSource:=True, SubType:=8
    .ViewMailMergeFieldCodes = False
    .Destination = wdSendToNewDocument
   .Execute
End With

' cleaning up
Set mywrd = Nothing
Set mydoc = Nothing

Open in new window


Hope this works,
Daniel
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Accepted Solution

by:
danishani earned 2000 total points
ID: 36963670
Here the late binding version:

Dim myWord As Object
Dim sql As String

sql = "select * from qryLetterCustomer where loadid = '" & strLoadId & "' and lettersent = false and province <> 'QC'"

DoCmd.Echo True, "GENERATING MASS MAIL LETTER....Please Wait......Thank You...."
DoCmd.Hourglass True

Set myWord = CreateObject("Word.Application")
myWord.Documents.Add EngLetter, , True
myWord.Application.DisplayAlerts = 0 'wdAlertsNone
   
    With myWord.ActiveDocument.MailMerge
            .MainDocumentType = 0 'wdFormLetters
            .OpenDataSource Name:=CurrentDb.Name, sqlstatement:=sql, SubType:=8
            .ViewMailMergeFieldCodes = False
            .Destination = 0 'wdSendToNewDocument
           .Execute True
    End With
       
    myWord.Application.Visible = True
    myWord.Application.WindowState = 1 'wdWindowStateMaximize
   
Set myWord = Nothing

DoCmd.Hourglass False
DoCmd.Echo True, Null

Open in new window


Hope this helps,
Daniel
0
 
LVL 13

Author Comment

by:Lucas
ID: 36964985
i guess i forgot to mention i'm using access 2007 db in ACCDB format not MDB.  Now it's complaining that it cannot find an MDB file.
0
 
LVL 12

Expert Comment

by:danishani
ID: 36965584
My guess is that you need to change your Datasource for your template as well.
Its problably still attached to the 'old' MDB instead of the new ACCDB.

Hope this helps,
Daniel
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question