MAIL MERGE IN MS ACCESS WITH WORD DOC POSSIBLE? (1 Viewer)

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
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

Forum Troll
Local time
Today, 07:08
Joined
Oct 6, 2019
Messages
2,884
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.
 

Attachments

  • mail merge example.zip
    23.3 KB · Views: 108

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
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

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 12:08
Joined
Sep 21, 2011
Messages
14,051
Why have you not dimmed oApp?
 

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
It is done here already.....

Option Compare Database

Dim WithEvents oApp As Word.Application
 

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
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/
Code:
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.
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 12:08
Joined
Sep 21, 2011
Messages
14,051
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

AWF VIP
Local time
Today, 08:08
Joined
Jan 11, 2013
Messages
3,852
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.

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
 
Last edited:

vba_php

Forum Troll
Local time
Today, 07:08
Joined
Oct 6, 2019
Messages
2,884
ash,

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

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
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.
 

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
Run-time error '13' Type mismatch

Set oApp = CreateObject("Word.Application")
 

moke123

AWF VIP
Local time
Today, 08:08
Joined
Jan 11, 2013
Messages
3,852
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

AWF VIP
Local time
Today, 08:08
Joined
Jan 11, 2013
Messages
3,852
heres a sample similiar to what I use
(note that the document may be hidden behind the access window)
 

Attachments

  • AKWordDemoLib (2).zip
    204.9 KB · Views: 118

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
Moke123,

It produces Run-Time error 3340 - Query Corrupt

db.Execute strInsert, dbFailOnError
rs.MoveNext
 

vba_php

Forum Troll
Local time
Today, 07:08
Joined
Oct 6, 2019
Messages
2,884
ash,

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

code used:

code used.jpg


result:

result.jpg


do you have a reference to the word program set?

word_ref.jpg
 

Attachments

  • result.jpg
    result.jpg
    333 KB · Views: 85

Ashfaque

Student
Local time
Today, 17:38
Joined
Sep 6, 2004
Messages
894
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.
 

Users who are viewing this thread

Top Bottom