Opeing Mail Merge With VBA Issue

[abbaddon223]

Hi,

I've created a 2010 Word Mail Merge into a 2010 DB. All is fine. When I open the Word doc directly, it prompts me to select "Yes" to updating the mail merge information into the DB.

The issue is, when I open the Word doc from Access using VBA, there is no such prompt, it just opens the doc and the mail merage blocks are the last value left in there from when I last selected "Yes". Grrrrrr!!!!

Can anyone suggest how I get the doc to update when opened with VBA please?

Thanks!!!

 

[DavidAtWork]

Can you not just use a simple Msgbox with vbYesNo choice when the Word doc opens and if the choice is Yes, update the mail merge info.
You may have to look at the Word vba the fires when the mail merge info is updated
David

 

[abbaddon223]

Hi David,

Thank you for replying to my post. Unfortunately the Word dialogue box that appears when you open word directly does not appear when you open it with VBA.

Thanks for trying to help though.

 

[DavidAtWork]

I wasn't meaning the Word dialogue box, I was referring to a vba message box (which you can write into your own vba) and using the user's choice to fire Word vba code to update Merge info
David

 

[abbaddon223]

Hi David,

Thank you again for replying. I'm not an expert when it comes to VBA by any stretch > below is the code I am using. Would you be able to show me what and where to put in the lines you are talking about please? Thank you so much for your help - I've lost days on this!!! :banghead:

Dim wordApp1 As Word.Application
Dim wordDoc1 As Word.Document
Set wordApp1 = New Word.Application
With wordApp1
.Visible = False
Set wordDoc1 = .Documents.Open("D:\Remote Applications\Cube5 CMS\CMS\Mail_Merges\Pack Back Confirmation Letter.docx", , False)
wordDoc1.PrintOut background:=False, Copies:=1
wordDoc1.Close Word.wdDoNotSaveChanges
wordApp1.Quit
MsgBox "Print Process Now Confirmed", vbInformation, "User Notice"
End With
Exit Sub
End If

 

[DavidAtWork]

I'd put this line in after you have opened the document (.Documents.Open)
If Msgbox("Would you like to update Merge info",vbYesNo) = vbYes Then
With wordDoc1.MailMerge
.OpenDataSource = Name:=sDBPath, _
SQLStatement:="SELECT * FROM [myMergeDataTable]"
End With
End If
Can you see what this code is doing, it's updating the merge source data using a SQL statement, the database and path is held by the variable sDBPath. You just need to set a value for sDBPath and write a suitable SQL statement as the source.
Also above you do not save the changes, but here you will need to save it.
If you want to execute the merge then you need to add
.MailMerge.Execute somewhere after the .OpenDataSource statement
Hope this helps
David