Automating Mailmerge from Access

PhilAJ · #1 05-19-2011, 04:26 AM

Hi

I've written some code that automates a mailmerge from a form in MSacess - and it works fine...BUT...

straight after the code line

Set objWord = GetObject(stDocName, "Word.Document")

the word document opens and prompts with "Opening this document will run with the following command....etc etc " and asking for a YEs/No answer; even though my code will then the datasource and do the merge,

objWord.MailMerge.OpenDataSource Name:="C:\Temp\abc.txt", linktosource:=True
objWord.Application.Visible = True
objWord.MailMerge.Execute

Does anyone know of any way to STOP word prompting - and just continue and do the merge?

Thanks in advance

Phil

macropod · #2 05-19-2011, 07:32 PM

Hi Phil,

You could suppress that message via Application.DisplayAlerts = False, but that would also cause Word to convert the document from a mailmerge main document to an ordinary document (but with the mergefields in situ).

However, since you're driving this from Access, why are you using a Word mailmerge main document? If you use an ordinary document with the mailmerge fields etc (eg delete your Word mailmerge main document's mailmerge header), then you can programmatically make it a mailmerge main document and add the query when you open it. When you're finished processing, close the original Word document without saving.

PhilAJ · #3 05-23-2011, 02:16 AM

Thanks for response MacroPod. Tried the DisplayAlerts = false but never helped. The prompt is coming up as soon as the "Set objWord = GetObject(stDocName, "Word.Document")" line is executed.

Not sure what you mean about "programmatically make it a mailmerge main document " - How can you insert the fields where you want them without it being a MMerge document?

Phil

macropod · #4 05-23-2011, 03:12 AM

Hi Phil,

As I said, you wouldn't want to use 'Application.DisplayAlerts = False' anyway. As for:

Quote:

Not sure what you mean about "programmatically make it a mailmerge main document " - How can you insert the fields where you want them without it being a MMerge document?

1. You would insert the SQL query code into the '.MailMerge.OpenDataSource' expression
2. You wouldn't need to - you can use your existing mailmerge main document (which is presumably defined via 'stDocName') if you simply convert it to an ordinary document. Indeed, you can insert mergefields into any document - it just won't function as a mailmerge document unless you do something to cause the document to behave as one.

Here's some sample code. I've included a comment with the full range of the 'OpenDataSource' expression's arguments (and a commented-out 'DisplayAlerts = False' line).

Code:

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim strMainDocNm As String
Dim strDataDocNm As String
Set wdApp = Word.Application
strMainDocNm = "C:\Users\PhilAJ\Documents\Temp\MailmergeMainDocument.doc"
strDataDocNm = "C:\Temp\abc.txt"
With wdApp
  '.DisplayAlerts = False
  .Application.Visible = True
  Set wdDoc = .Documents.Open(Filename:=strMainDocNm)
  With wdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      'OpenDataSource(Name, Format, ConfirmConversions, ReadOnly, LinkToSource, _
        AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, _
        WritePasswordDocument, WritePasswordTemplate, Connection, _
        SQLStatement, SQLStatement1, OpenExclusive, SubType)
      .OpenDataSource Name:=strDataDocNm, LinkToSource:=True
      .Execute
    End With
    .Close SaveChanges:=False
  End With
End With

PhilAJ · #5 05-23-2011, 04:42 AM

Thanks very much Macropod - i'll give that a go.

Cheers

Phil

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automating Outlook from Access	RMittelman	Outlook	1	02-07-2011 03:11 PM
Automating document updates with new text	wordfoolish2	Word	0	01-04-2011 01:01 PM
Automating an address from an Abbreviation	Panzer	Word	0	04-12-2010 06:11 AM
Access MailMerge DataSource in VB	advin	Mail Merge	1	01-04-2010 01:34 AM
Automating Cel Population of .jpg	skoz55	Excel	0	01-21-2009 03:43 AM