Automating Mailmerge from Access

[PhilAJ]

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]

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]

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]

Hi Phil,

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

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).

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]

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

Cheers

Phil