Word 2007 - Mail Merge Help!

[psctornado]

Hi All,

Hope everyone is having a nice holiday season. I'm a bit stumped on an issue, that I was hoping to get help on. Recently my group migrated to Win 7, and upgraded to Office 2010 from Office 2003. This has required us to update our automated tools a bit to accomodate for the upgraded Office suite.

The code below is giving me a sintax error every time I try to run it. Regardless of the minor tweaks I do, I have been unable to get this code to run properly.

Would anyone here be able to advise as to what I can do. In short, I have an excel document, with a button that triggers a macro, the macro of course launches word, and word has the rest of the VBA to finish the merge.

Any help would be greatly appreciated!


Private Sub Document_Open()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook7.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:=
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook7.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLE" _
, SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.WindowState = wdWindowStateMaximize
Windows("SRP Election Form_LS.doc").Activate
ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
End Sub

[macropod]

The code you've posted evidently isn't an exact replica of what you were using in the past, because a missing continuation character means it couldn't have run in the former environment. Replace:
Connection:=
with:
Connection:= _

[psctornado]

Thanks Macropod!

With your help I was able to remove that syntax error. I do admit that prior to posting the code I had been modifying to see if I could solve it myself. While the code now does not give me a syntax error, it is requiring me to do the merge manually (ie - it opens the word template, but I still need to complete the merge). Any thoughts of how I could automate it with my updated vba?


Sub Merge1()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook7.xls", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook7.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Loc" _
, SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
With .ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailMerge.Execute
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Windows("SRP Election Form_LS.doc").Activate
ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
End Sub

[macropod]

I'm not sure what you mean when you say:
[quote]
though you did have two .Execute statements, the first of which was before you'd finished setting the parameters.

However, it does appear your code could be improved:

Sub Merge1()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook7.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Windows("SRP Election Form_LS.doc").Activate
Application.DisplayAlerts = wdAlertsAll
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End SubPS: When posting code, please use the [I]code tags (not the HTML tags).

[psctornado]

What I meant by 'it is requiring me to do the merge manually' is that, when I launch start the macro from excel it then opens up the standalone word template. The template comes up, and then requires me to go into the mailing tab, and go through the mail merge wizard. Does the code above require a valid ODBC connection at all? I verified that I have the MS Office 14.0 Object Library checked, so I'm guessing it is a small piece that I'm missing to automate the mail merge. I appreciate the updated code as I agree it is much cleaner.

I hope I conveyed the issue I am having effectively.

[macropod]

Have you tried running the code I posted? And are you sure the datasource is correctly defined (see the MMSource variable - is it still an xls file, or should it now be an xlsx file)?

[psctornado]

Thanks again Macropod!

With a bit of tweaking on my end with the source document I was able to automate the mail merge. The only issue I have now (small one) is that when the mail merge is complete the document is minimized. This is the current code that is working on my end. The maximize row does not seem to be doing its intended job. Would you happen to have any thoughts?

Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.DisplayAlerts = wdAlertsAll
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
Application.WindowState = wdWindowStateMaximize
End With
End Sub

[macropod]

With a bit of tweaking on my end with the source document I was able to automate the mail merge. The only issue I have now (small one) is that when the mail merge is complete the document is minimized. This is the current code that is working on my end. The maximize row does not seem to be doing its intended job. Would you happen to have any thoughts?
What are you trying to maximize? 'Application.WindowState = wdWindowStateMaximize' maximises the Word window; it has no effect on a document.

Also, from where is this code being run? If it's being run from your mailmerge main document, that line will never execute as it's after the '.Close SaveChanges:=wdDoNotSaveChanges' line, which also kills the macro.

[psctornado]

Thanks Macropod! Sorry for the late follow up as I was away for the recent holiday. I just adjusted where the maximize row was and it solved the problem. I appreciate the help. Happy New Year!

Cheers!