MAIL MERGE IN MS ACCESS WITH WORD DOC POSSIBLE?

[Ashfaque]

Dear Genius Members,

I just want to know if the subjected method is possible in MS Access?

The tbl data I want to print in word documents using VBA.

Any help in this regard shall be appreciated.

With warm regards,
Ashfaque

 

[vba_php]

of course it's possible. all office products are interconnected, and VBA is the language to use for the purpose. I have this in my files from long ago. take a look. the instructions might be outdated though, as it says to put the sample letter document on the C:\ drive. but as I have learned from other people here, microsoft has done something with security on the c drive, and it might not work being place there. you might have to put the .doc somewhere else in the dirs.

 

[moke123]

Take a look at Albert Kallal's Super Easy Mail Merge.
Scroll down on this page .... http://www.kallal.ca/msaccess/msaccess.html

Its very easy to use and incorporate into your project.

 

[Ashfaque]

Dear vba_php,

Thanks for the help. Yes you are correct place files in C:\ has been stopped therefore, I kept it somewhere in D:\ drive.

Accordingly changed path into the code with no error. But it produces Run-Time Error 91 - Object Variable or With Block Variable not set.

This is happening on following line ...
Set oMainDoc = oApp.Documents.Open("D:\Ashfaque\Test Letter")

Kindly extend your help.

Regards,
Ashfaque

 

[Ashfaque]

Now it it producing.. Run-Time Error 13 - Type Mismatch on following....(I m using 2016 office). Does it require to your db need convert into 2016 and then try?
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

rivate Sub Form_Load()

Set oApp = CreateObject("Word.Application")

End Sub

 

[Gasman]

Why have you not dimmed oApp?

 

[Ashfaque]

It is done here already.....

Option Compare Database

Dim WithEvents oApp As Word.Application

 

[Ashfaque]

Below is the full code lines as per first answered by vba_php

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Option Compare Database

Dim WithEvents oApp As Word.Application

Private Sub Command0_Click()

    Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String

    Set oMainDoc = oApp.Documents.Open("D:\Ashfaque\Test Letter")
oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "D:\Ashfaque\Letter Database.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblcompanies]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1

End Sub

Private Sub Form_Load()
Set oApp = CreateObject("Word.Application")
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub

Please see the attachment in first answer.

 

[Gasman]

I have not played with WithEvents, so cannot help there, sorry.

A quick Google seems to me to infer the Withevents as you have it needs to be in a class module.?

WithEvents

I've only ever dimmed for objects locally within scope.

I'll let vba_php continue to assist and drop out.

 

[moke123]

As gasman stated withevents needs to be in a class module or a form module. It wont work in a standard module.
Additionally there is nothing raising any events so it is not needed.

I also dont see that you have declared Option Explicit. You should add it.

your path to the document is wrong. Set oMainDoc = oApp.Documents.Open("D:\Ashfaque\Test Letter") This should be Letter.doc

I made changes as follows which work however I wouldn't reccomend using it as is.
(note the changes to the path which point to the folder containing the mdb and doc. Should work regaurdless of location)
It opens 2 instances of word and leaves them open. you should close 1st instance.
I believe Albert when he recommends that you never let a document connect directly to a database.
Again I would recommend you look at Alberts code.

Option Compare Database
Option Explicit



Private Sub Command0_Click()

    Dim oApp As Word.Application
    Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String


    Set oApp = CreateObject("Word.Application")

    Set oMainDoc = oApp.Documents.Open(CurrentProject.Path & "\Test Letter.doc")
    oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = CurrentProject.Path & "\Letter Database.mdb"
        .OpenDataSource Name:=sDBPath, _
                        SQLStatement:="SELECT * FROM [tblcompanies]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1

    Set oApp = Nothing

End Sub

 

[vba_php]

ash,

there are a lot of replies here. did you solve the problem?

 

[Ashfaque]

Not yet ....I am trying otherway but still not success...I just want to run vba code thru a btn and get the record to word document or even direct to PDF file and save it at the desired destination.

 

[vba_php]

well what exactly is the issue?

 

[Ashfaque]

Run-time error '13' Type mismatch

Set oApp = CreateObject("Word.Application")

 

[moke123]

Did you try what I posted?
Comment out all your code including what you have in the declarations and put what I posted under a new button click.
Note that all code is within the one procedure including - Dim oApp As Word.Application

 

[moke123]

heres a sample similiar to what I use
(note that the document may be hidden behind the access window)

 

[Ashfaque]

Moke123,

It produces Run-Time error 3340 - Query Corrupt

db.Execute strInsert, dbFailOnError
rs.MoveNext

 

[moke123]

Sounds like you didn't update your program after the MS security update debacle from late last year

see this link https://www.fmsinc.com/MicrosoftAccess/Errors/query_is_corrupt/index.htm

 

[vba_php]

ash,

this is what I did, and it works just fine:

code used:

result:

do you have a reference to the word program set?

 

[Ashfaque]

I came to know from my IT the 2016 office is pirated and will not accept any update.

I dont know how to solve this out. May be I need to purchase it for my own.