Mail Merge

D

david.roebuck

I hope someone can help me with this,I am trying to create a mail merge
from within my access database. The source of the data is a table which
I want to create mail merges based on a field name called Relationship
Manager. I want to open the word doc template and create the mail merge
then save it to a file then create the next mail merge and save that
etc.
The problem I am having is linking to the data source as it tries to
open the database again and also closing the word application
afterwards.
Public Sub Fire()
On Error GoTo Err_Fire

Dim db As Database
Dim rst As Recordset
Dim olkApp As Outlook.Application
Dim objmailitem As Outlook.MailItem
Dim objscript
Dim SafeItem
Dim utils
Dim strAttach As String
Dim strfilename As String
Dim objWord As Word.Document


Set objscript = CreateObject("Scripting.FileSystemObject")

If Not objscript.FolderExists("d:\" & "Fire\") Then
objscript.createfolder ("d:\" & "Fire\")
End If

If Dir("d:\Fire\*.doc") <> "" Then
Kill "d:\Fire\*.doc"
End If

Set objscript = Nothing

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("tblrelmanager")

Do Until rst.EOF

Set objWord = GetObject("C:\Documents and
Settings\David\Desktop\fire risk assessment.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the WSS database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _
LinkToSource:=True, _
Connection:="table tblAccenture", _
SQLStatement:="SELECT * FROM [tblAccenture] WHERE [Relationship
Manager] = '" & rst![Relationship Manager] & "'"
' Execute the mail merge.
objWord.MailMerge.Execute
ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship
Manager] & ""
ActiveDocument.Close

rst.MoveNext
Loop
ActiveDocument.Close
objWord.Quit
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Thanks in advance Dave
 
J

Justin Hoffman

I hope someone can help me with this,I am trying to create a mail merge
from within my access database. The source of the data is a table which
I want to create mail merges based on a field name called Relationship
Manager. I want to open the word doc template and create the mail merge
then save it to a file then create the next mail merge and save that
etc.
The problem I am having is linking to the data source as it tries to
open the database again and also closing the word application
afterwards.
Public Sub Fire()
On Error GoTo Err_Fire

Dim db As Database
Dim rst As Recordset
Dim olkApp As Outlook.Application
Dim objmailitem As Outlook.MailItem
Dim objscript
Dim SafeItem
Dim utils
Dim strAttach As String
Dim strfilename As String
Dim objWord As Word.Document


Set objscript = CreateObject("Scripting.FileSystemObject")

If Not objscript.FolderExists("d:\" & "Fire\") Then
objscript.createfolder ("d:\" & "Fire\")
End If

If Dir("d:\Fire\*.doc") <> "" Then
Kill "d:\Fire\*.doc"
End If

Set objscript = Nothing

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("tblrelmanager")

Do Until rst.EOF

Set objWord = GetObject("C:\Documents and
Settings\David\Desktop\fire risk assessment.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the WSS database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _
LinkToSource:=True, _
Connection:="table tblAccenture", _
SQLStatement:="SELECT * FROM [tblAccenture] WHERE [Relationship
Manager] = '" & rst![Relationship Manager] & "'"
' Execute the mail merge.
objWord.MailMerge.Execute
ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship
Manager] & ""
ActiveDocument.Close

rst.MoveNext
Loop
ActiveDocument.Close
objWord.Quit
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Thanks in advance Dave
Click to expand...


There are a number of things I could comment on here: why not set a
Word.Application object outside the loop? what are the Outlook objects for?
the error handling should be improved, etc
But for an immediate solution to stop another session of Access starting,
why not export the query (DoCmd.TransferText) as a temporary text file and
use that as the source for the mailmerge. I use that approach for a number
of reasons including overcoming user-level security complications.
If Word is not quitting you need to revise the routine (including error
handling) to make sure that you dispose with Word objects properly. If you
remove the On Error Resume Next line from your code, you may find you are
ignoring errors you should be dealing with e.g. ActiveDocument.Close. The
'On Error Resume Next' bit should only be used for a single line, or perhaps
a cleanup part at the end. As mentioned above, I would have a
Word.Application object so the final cleanup part might be:

Exit_Routine:
On Error Resume Next
If Not wdDoc Is Nothing Then
wdDoc.Close
Set wdDoc=Nothing
End If
If Not wdApp Is Nothing Then
wdApp.Quit
Set wdApp=Nothing
End If
Exit Sub
 
D

David

Please help, I still cannot drop winword on closure. Do I need to add
anything to my code? The mail merge works but winword.exe does not close.

Many thanks

Dave



Public Sub Fire()

'On Error GoTo Err_Fire

Dim db As Database

Dim rst As Recordset

'Dim olkApp As Outlook.Application

'Dim objmailitem As Outlook.MailItem

Dim objscript

'Dim SafeItem

'Dim utils

'Dim strAttach As String

'Dim strfilename As String

Dim objWord As Word.Document









Set objscript = CreateObject("Scripting.FileSystemObject")



If Not objscript.FolderExists("D:\" & "Fire\") Then

objscript.createfolder ("D:\" & "Fire\")

End If



If Dir("D:\Fire\*.doc") <> "" Then

Kill "D:\Fire\*.doc"

End If



Set objscript = Nothing



'On Error Resume Next



Set db = CurrentDb

Set rst = db.OpenRecordset("tblrelmanager")

Set objWord = GetObject("C:\Documents and Settings\David\Desktop\fire risk
assessment.doc", "Word.Document")


' Make Word visible.


objWord.Application.Visible = True

Do Until rst.EOF


' Set the mail merge data source as the WSS database.

objWord.MailMerge.OpenDataSource _

Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _

LinkToSource:=True, _

Connection:="table qryAccenture", _

SQLStatement:="SELECT * FROM [qryAccenture] WHERE [Relationship Manager] =
'" & rst![Relationship Manager] & "'"


' Execute the mail merge.

objWord.MailMerge.Execute


ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship Manager]

ActiveDocument.Close


rst.MoveNext

Loop


'objWord.Application.Quit False

objWord.Close

Set objWord = Nothing


rst.Close

Set rst = Nothing

db.Close

Set db = Nothing


DoCmd.SetWarnings True






Exit_Fire:

Exit Sub


Err_Fire:

MsgBox Err.Description

Resume Exit_Fire

End Sub

Justin Hoffman said:
I hope someone can help me with this,I am trying to create a mail merge
from within my access database. The source of the data is a table which
I want to create mail merges based on a field name called Relationship
Manager. I want to open the word doc template and create the mail merge
then save it to a file then create the next mail merge and save that
etc.
The problem I am having is linking to the data source as it tries to
open the database again and also closing the word application
afterwards.
Public Sub Fire()
On Error GoTo Err_Fire

Dim db As Database
Dim rst As Recordset
Dim olkApp As Outlook.Application
Dim objmailitem As Outlook.MailItem
Dim objscript
Dim SafeItem
Dim utils
Dim strAttach As String
Dim strfilename As String
Dim objWord As Word.Document


Set objscript = CreateObject("Scripting.FileSystemObject")

If Not objscript.FolderExists("d:\" & "Fire\") Then
objscript.createfolder ("d:\" & "Fire\")
End If

If Dir("d:\Fire\*.doc") <> "" Then
Kill "d:\Fire\*.doc"
End If

Set objscript = Nothing

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("tblrelmanager")

Do Until rst.EOF

Set objWord = GetObject("C:\Documents and
Settings\David\Desktop\fire risk assessment.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the WSS database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _
LinkToSource:=True, _
Connection:="table tblAccenture", _
SQLStatement:="SELECT * FROM [tblAccenture] WHERE [Relationship
Manager] = '" & rst![Relationship Manager] & "'"
' Execute the mail merge.
objWord.MailMerge.Execute
ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship
Manager] & ""
ActiveDocument.Close

rst.MoveNext
Loop
ActiveDocument.Close
objWord.Quit
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Thanks in advance Dave
Click to expand...


There are a number of things I could comment on here: why not set a
Word.Application object outside the loop? what are the Outlook objects
for? the error handling should be improved, etc
But for an immediate solution to stop another session of Access starting,
why not export the query (DoCmd.TransferText) as a temporary text file and
use that as the source for the mailmerge. I use that approach for a
number of reasons including overcoming user-level security complications.
If Word is not quitting you need to revise the routine (including error
handling) to make sure that you dispose with Word objects properly. If
you remove the On Error Resume Next line from your code, you may find you
are ignoring errors you should be dealing with e.g. ActiveDocument.Close.
The 'On Error Resume Next' bit should only be used for a single line, or
perhaps a cleanup part at the end. As mentioned above, I would have a
Word.Application object so the final cleanup part might be:

Exit_Routine:
On Error Resume Next
If Not wdDoc Is Nothing Then
wdDoc.Close
Set wdDoc=Nothing
End If
If Not wdApp Is Nothing Then
wdApp.Quit
Set wdApp=Nothing
End If
Exit Sub
Click to expand...
 
J

Justin Hoffman

David said:
Please help, I still cannot drop winword on closure. Do I need to add
anything to my code? The mail merge works but winword.exe does not close.

Many thanks

Dave
Click to expand...


Did you understand the advice about creating a Word.Application object?
Anyway, there are still a couple of decisions you need to make before you
start any coding. What happens if the user has Word open anyway? My advice
would be for your code to check whether Word is running - if it is you can
use that instance - just don't close it because you didn't open it and that
might irritate the user who did. If your code did open it, then you should
do your best to close it afterwards.









Public Sub Fire()

'On Error GoTo Err_Fire

Dim db As Database

Dim rst As Recordset

'Dim olkApp As Outlook.Application

'Dim objmailitem As Outlook.MailItem

Dim objscript

'Dim SafeItem

'Dim utils

'Dim strAttach As String

'Dim strfilename As String

Dim objWord As Word.Document









Set objscript = CreateObject("Scripting.FileSystemObject")



If Not objscript.FolderExists("D:\" & "Fire\") Then

objscript.createfolder ("D:\" & "Fire\")

End If



If Dir("D:\Fire\*.doc") <> "" Then

Kill "D:\Fire\*.doc"

End If



Set objscript = Nothing



'On Error Resume Next



Set db = CurrentDb

Set rst = db.OpenRecordset("tblrelmanager")

Set objWord = GetObject("C:\Documents and Settings\David\Desktop\fire risk
assessment.doc", "Word.Document")


' Make Word visible.


objWord.Application.Visible = True

Do Until rst.EOF


' Set the mail merge data source as the WSS database.

objWord.MailMerge.OpenDataSource _

Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _

LinkToSource:=True, _

Connection:="table qryAccenture", _

SQLStatement:="SELECT * FROM [qryAccenture] WHERE [Relationship Manager] =
'" & rst![Relationship Manager] & "'"


' Execute the mail merge.

objWord.MailMerge.Execute


ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship Manager]

ActiveDocument.Close


rst.MoveNext

Loop


'objWord.Application.Quit False

objWord.Close

Set objWord = Nothing


rst.Close

Set rst = Nothing

db.Close

Set db = Nothing


DoCmd.SetWarnings True






Exit_Fire:

Exit Sub


Err_Fire:

MsgBox Err.Description

Resume Exit_Fire

End Sub

Justin Hoffman said:
I hope someone can help me with this,I am trying to create a mail merge
from within my access database. The source of the data is a table which
I want to create mail merges based on a field name called Relationship
Manager. I want to open the word doc template and create the mail merge
then save it to a file then create the next mail merge and save that
etc.
The problem I am having is linking to the data source as it tries to
open the database again and also closing the word application
afterwards.
Public Sub Fire()
On Error GoTo Err_Fire

Dim db As Database
Dim rst As Recordset
Dim olkApp As Outlook.Application
Dim objmailitem As Outlook.MailItem
Dim objscript
Dim SafeItem
Dim utils
Dim strAttach As String
Dim strfilename As String
Dim objWord As Word.Document


Set objscript = CreateObject("Scripting.FileSystemObject")

If Not objscript.FolderExists("d:\" & "Fire\") Then
objscript.createfolder ("d:\" & "Fire\")
End If

If Dir("d:\Fire\*.doc") <> "" Then
Kill "d:\Fire\*.doc"
End If

Set objscript = Nothing

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("tblrelmanager")

Do Until rst.EOF

Set objWord = GetObject("C:\Documents and
Settings\David\Desktop\fire risk assessment.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the WSS database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\David\Desktop\WSS.mdb", _
LinkToSource:=True, _
Connection:="table tblAccenture", _
SQLStatement:="SELECT * FROM [tblAccenture] WHERE [Relationship
Manager] = '" & rst![Relationship Manager] & "'"
' Execute the mail merge.
objWord.MailMerge.Execute
ActiveDocument.SaveAs FileName:="D:\fire\" & rst![Relationship
Manager] & ""
ActiveDocument.Close

rst.MoveNext
Loop
ActiveDocument.Close
objWord.Quit
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Thanks in advance Dave
Click to expand...


There are a number of things I could comment on here: why not set a
Word.Application object outside the loop? what are the Outlook objects
for? the error handling should be improved, etc
But for an immediate solution to stop another session of Access starting,
why not export the query (DoCmd.TransferText) as a temporary text file
and use that as the source for the mailmerge. I use that approach for a
number of reasons including overcoming user-level security complications.
If Word is not quitting you need to revise the routine (including error
handling) to make sure that you dispose with Word objects properly. If
you remove the On Error Resume Next line from your code, you may find you
are ignoring errors you should be dealing with e.g. ActiveDocument.Close.
The 'On Error Resume Next' bit should only be used for a single line, or
perhaps a cleanup part at the end. As mentioned above, I would have a
Word.Application object so the final cleanup part might be:

Exit_Routine:
On Error Resume Next
If Not wdDoc Is Nothing Then
wdDoc.Close
Set wdDoc=Nothing
End If
If Not wdApp Is Nothing Then
wdApp.Quit
Set wdApp=Nothing
End If
Exit Sub
Click to expand...
Click to expand...
 
D

david.roebuck

I have decided to start again, as I am having so many problems. Using
your idea to 'TransferText' to a text file. I am going to take this one
step at a time. After creating this file how do I open it? Do I still
use ......objWord.MailMerge.OpenDataSource and if so how do i reference
it
 
J

Justin Hoffman

I have decided to start again, as I am having so many problems. Using
your idea to 'TransferText' to a text file. I am going to take this one
step at a time. After creating this file how do I open it? Do I still
use ......objWord.MailMerge.OpenDataSource and if so how do i reference
it
Click to expand...

Yes - look under the Word help file for OpenDataSource. You will see that
you should let the mailmerge know that plain text is used as the datasource.
WdOpenFormat constant is wdOpenFormatText (=4).

wdDoc.MailMerge.OpenDataSource _
Name:="C:\MyText.txt", _
Format:=4, _
ReadOnly:=True, _
LinkToSource:=False, _
AddToRecentFiles:=False

If you would like, and I can find some time, I could send you an example.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Mail Merge in Word Data Link Error 1
How to Control Which Printer MS Word Prints To 1
Code to fill Word form for multiple records from Access 1
Letter merge using backend data 2
Error automating mail merge using access query 1
Mail merge to MS Word 1
Word Merge From Access 2
If Statement with MsgBox 7

Top