How can I automate a mail merge from Excel data & have it updateevery time?

[MagGyver]

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 spreadsheet for a mailing list she keeps.
She updates this spreadsheet 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 spreadsheet 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 spreadsheet (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.

 

[Peter Jamieson]

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 [" & ActiveSheet.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 sheet. If you want to open a specific named range or some other
sheet, you will need to modify this line:

sqlstatement:="SELECT * FROM [" & ActiveSheet.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?

 

[MagGyver]

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

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 [" & ActiveSheet.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 sheet. If you want to open a specific named range or some other
sheet, you will need to modify this line:

    sqlstatement:="SELECT * FROM [" & ActiveSheet.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

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 spreadsheet for a mailing list she keeps.
She updates this spreadsheet 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 spreadsheet 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 spreadsheet (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.

 

[Peter Jamieson]

Sorry, I had assumed you meant "start from Excel, because if you're starting
from Word, all you should have to do is open the Word document, ensure it
reconnects to the data source, then perform the merge. You might need to
save the Excel workbook first. To be honest, I try not to automate stuff
with as few steps as that except for myself, because for most people it's
easier to repeat the steps than struggle with all the additional stuff that
having a macro forces on you. As a minimum, I'd say that your friend should
a. save and close the Excel document
b. open Word
c. open the relevant Mail Merge Main document, which should already be
connected to the data source.
d. answer the security questions

If you want to automate the rest of it, you can put the following in a
module in that document:

Sub AutoOpen()
With ActiveDocument
.MailMerge.Destination = wdSendToPrinter
.MailMerge.Execute
End With
End Sub

and it should run after all those security questions have been answered.

Just my 2c-worth


--
Peter Jamieson
http://tips.pjmsn.me.uk

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

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 [" & ActiveSheet.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 sheet. If you want to open a specific named range or some other
sheet, you will need to modify this line:

sqlstatement:="SELECT * FROM [" & ActiveSheet.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

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 spreadsheet for a mailing list she keeps.
She updates this spreadsheet 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 spreadsheet 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 spreadsheet (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.

 

[MagGyver]

Peter, thanks so much for your help! That last bit of code you gave me
for automation was what did the trick. I created a macro within the
mail merge document (using ODBC to link to the Excel worksheet) and
substituted "wdSendtoNewDocument" for "wdSendtoPrinter" to tailor it
for my friend, and then created a shortcut to the mail merge document
using the /m switch to invoke the macro. Now all she needs to do is to
click on the shortcut, answer one confirmation about linking, and
voila! The labels appear as they should.

Thanks again.

Sorry, I had assumed you meant "start from Excel, because if you're starting
from Word, all you should have to do is open the Word document, ensure it
reconnects to the data source, then perform the merge. You might need to
save the Excel workbook first. To be honest, I try not to automate stuff
with as few steps as that except for myself, because for most people it's
easier to repeat the steps than struggle with all the additional stuff that
having a macro forces on you. As a minimum, I'd say that your friend should
 a. save and close the Excel document
 b. open Word
 c. open the relevant Mail Merge Main document, which should already be
connected to the data source.
 d. answer the security questions

If you want to automate the rest of it, you can put the following in a
module in that document:

Sub AutoOpen()
With ActiveDocument
  .MailMerge.Destination = wdSendToPrinter
  .MailMerge.Execute
End With
End Sub

and it should run after all those security questions have been answered.

Just my 2c-worth

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


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

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 [" & ActiveSheet.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

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

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

sqlstatement:="SELECT * FROM [" & ActiveSheet.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?