#1
|
|||
|
|||
Word Doc Macro (mail Merge)
I have created the below macro and it works fine except two things.
1. I get an error when it closes a file 2. I want the macro to fire off when this document is opened First here is what it does. It links the file in mail merge to a data source then finalizes the merge in a separate file. Within that it closes the original file it started with (.docx). This is where I get the issue number one. I get a error saying the file cant be saved due to permissions issues. I don’t want the file save I just want it to close without any error message appearing. I highlighted the code where the error occurs below. The second issue I have is I want this macro to run when this document is opened. Not when any document is opened in word only this document. Any help will be greatly appreciated. Below is the macro: ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:= _ "F:\DB_Docs\DB_2010\PI_Sys.accdb", 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=F:\DB_Docs\DB_2010\PI_Sys.accdb;Mode=Read;E xtended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global" _ , SQLStatement:="SELECT * FROM `TY_Letter`", SQLStatement1:="", SubType:= _ wdMergeSubTypeAccess ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle ActiveDocument.MailMerge.Check With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With Selection.MoveDown Unit:=wdLine, Count:=7 ActiveWindow.View.ShowFieldCodes = Not ActiveWindow.View.ShowFieldCodes Windows("Thank_You_Letter.docx").Activate ActiveWindow.Close |
#2
|
||||
|
||||
Hi ajolson,
You can execute the mailmerge automatically by making the macro an AutoOpen macro. Without knowing more about the error you referred to, it's difficult to advise. What did the error message say? When you check the code, what line is highlighted? On the assumption it's an error related to trying to close the file, try the following: Code:
Sub AutoOpen() Dim StrConnection As String, MailMergeMainDoc As Document Set MailMergeMainDoc = ActiveDocument With MailMergeMainDoc.MailMerge StrConnection = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=F:\DB_Docs\DB_2010\PI_Sys.accdb;" StrConnection = StrConnection & "Mode=Read;E xtended Properties="""";Jet OLEDB:System database="""";" StrConnection = StrConnection & "Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;" StrConnection = StrConnection & "Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global" .MainDocumentType = wdFormLetters .OpenDataSource Name:="F:\DB_Docs\DB_2010\PI_Sys.accdb", ConfirmConversions:=False, _ ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _ PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _ Revert:=False, Format:=wdOpenFormatAuto, Connection:=StrConnection, _ SQLStatement:="SELECT * FROM `TY_Letter`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False .Close SaveChanges:=False End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need macro to merge files in Word and preserve formatting | Carolin | Word VBA | 3 | 12-14-2014 04:01 AM |
I need MS Word Mail Merge Help! | mikecincotta | Mail Merge | 1 | 10-04-2010 05:22 PM |
help with mail merge in MS Word 2003 | Dgbhunter | Mail Merge | 0 | 10-05-2009 03:16 PM |
Word 2007 Mail Merge | halfempty | Mail Merge | 1 | 03-03-2009 07:45 PM |
mail merge in word help | jngovols | Mail Merge | 0 | 04-07-2008 05:40 AM |