Re: How can I automate a mail merge from Excel data & have it update every time?

Wow, Peter, that's quite a mouthful. Thanks ever so much for the quick
response. To be honest I've never seen the VB editor in action before
and quite frankly am having a hard time figuring out how to customize
the code you provided, where I need to insert my own paths etc. When I
talked about recording a macro, I was doing so in MS Word, visually
using the macro recorder, rather than on the backend of the code
through VB editor. I'm not entirely sure I have the knowledge or
skills to operate on this level. I will show this to a programmer
friend of mine, but in the meantime, is there any way you can see that
someone could use the macro recorder in Word to accomplish what I'm
after?

Thanks :)

On Jul 23, 2:19 am, "Peter Jamieson" <p...@xxxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
You can use an Excel macro such as the following. But please see the notes
afterwords.

Sub mergeme()
Dim bCreatedWordInstance As Boolean
Dim objWord As Word.Application
Dim objMMMD As Word.Document
On Error Resume Next
bCreatedWordInstance = False
Set objWord = GetObject(, "Word.Application")
If objWord Is Nothing Then
  Err.Clear
  Set objWord = CreateObject("Word.Application")
  bCreatedWordInstance = True
End If

If objWord Is Nothing Then
  MsgBox "Could not start Word"
  Err.Clear
  On Error GoTo 0
  Exit Sub
End If

' Let Word trap the errors
On Error GoTo 0
' During testing. make sure we can see what we are doing
objWord.Visible = True

Set objMMMD =
objWord.Documents.Open("C:\Users\pjj.DOMAINX\Documents\mergefromxl.docx")
objMMMD.Activate
With objMMMD
  .MailMerge.OpenDataSource _
    Name:=ActiveWorkbook.FullName, _
    sqlstatement:="SELECT * FROM [" & Active***.Name & "$]"
  ' Set this as required
  .MailMerge.Destination = wdSendToNewDocument
  .MailMerge.Execute
End With

' If you need to work with the output document,
' it is now the ActiveDocument, unless there were errors
objWord.ActiveDocument.SaveAs "my output document 2.docx"
objWord.ActiveDocument.Close savechanges:=wdDoNotSaveChanges

' Close the Mail Merge Main Document
objMMMD.Close savechanges:=wdDoNotSaveChanges
Set objMMMD = Nothing

' If you have dealt with the new document and want
' to close Word, use something like this...
If bCreatedWordInstance Then
  objWord.Quit
End If
Set objWord = Nothing

End Sub

Notes.

1. In the Excel VB Editor, with the relevant Excel document open, you need
to use Tools->Reference make a reference to the Microsoft Word 12.0 Object
Library

2. You should create the Mail Merge Main Document and go through the usual
steps of connecting to the data source then laying out the labels. Then you
should se the document back to being a "Normal Word Document", then select
the Labels option and (probably) cancel the dialog. Do not reconnect the
data source, because...

3. ...you need to save the Mail Merge Main Document with no data source
attached.

4. You probably also have to make the registry change described in

http://support.microsoft.com/kb/825765/en-us

5. The OpenDataSource code above assumes you want to open the currently
selected ***. If you want to open a specific named range or some other
***, you will need to modify this line:

    sqlstatement:="SELECT * FROM [" & Active***.Name & "$]"

You may also have to deal with other security issues, the business of how
your friend initiates the macro, etc. etc.

Not much, eh? :-)

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"MagGyver" <maggy...@xxxxxxxxx> wrote in message

news:21b7efad-47b3-4dda-a62f-97401377d6a7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I know I must be just a step or two away from resolving this thing -
let's hope this one's easy for someone out there.

My friend has an Excel 2007 spread*** for a mailing list she keeps.
She updates this spread*** once or twice a month and wants to print
labels from it using mail merge, but would like it as automated as
possible. I'm assuming this must be done with a macro.

I can get the mail merge to work correctly if I do it manually;
however if I record a macro of the merge process, it seems to freeze
the spread*** in time to that moment and any subsequent updates to
the list are not reflected in the next mail merge.

After perusing this newsgroup a bit and Googling for various results,
I've tried a few things but cannot get it to work. I've tried creating
an ODBC data source from the spread*** (which was missing a key
field when I tried to link to it) and I've tried importing the data
into Access or Outlook instead (not going to work either).

Once I solve this issue of the data not being updated in the merge,
then I need to make the merge creation process as streamlined as
possible for my friend (a computer novice). The advice I found on the
Web was to create a Word shortcut using the /t and /m switches to load
a new file and start the macro. I'm envisioning this to be the
simplest way for her to access her labels. Does that sound about
right?

Many thanks for any feedback you can offer. I'm banging my head
against the wall on this one.

.