Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2011, 10:37 AM
ajolson1964 ajolson1964 is offline Word Doc Macro (mail Merge) Windows XP Word Doc Macro (mail Merge) Office 2010 32bit
Novice
Word Doc Macro (mail Merge)
 
Join Date: May 2011
Posts: 1
ajolson1964 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-10-2011, 10:15 PM
macropod's Avatar
macropod macropod is offline Word Doc Macro (mail Merge) Windows 7 32bit Word Doc Macro (mail Merge) Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
PS: When posting code, please use code tags. You'll also note I've broken up your Connection line into a series of shorter strings. That should make it easier to review & maintain the code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Doc Macro (mail Merge) Need macro to merge files in Word and preserve formatting Carolin Word VBA 3 12-14-2014 04:01 AM
Word Doc Macro (mail Merge) 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 Doc Macro (mail Merge) 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft