Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-28-2018, 02:36 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default what code sets table cell inner margins


I have been trying to record a macro that uses mail merge to create labels. I am able to select the label template, select the source file (an Excel spreadsheet), edit the recipient list, and insert a merge field. What I then want to do is set the top inner margin of each cell in the table to .05. However, while in recording mode I am not able to make this adjustment to the table. I don't have VBA programming. Could someone provide a snippet of VBA code that I can insert into the code that I have been able to record?

Thanks for any help!
Reply With Quote
  #2  
Old 03-28-2018, 02:40 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Why are you trying to use VBA for this? With a mailmerge, it's a once-off change made to the mailmerge main document.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 03-28-2018, 04:24 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default

The Excel data that I am merging into my labels is the content of one Excel cell. It has 3 or 4 lines of text (wrapped). I now start recording a macro. When I insert the Excel data into my labels table it is inserted as one merge field that, when the labels are printed, will be printed as 3 or 4 lines of text. I want this block of 3 or 4 lines to be placed on the label a bit lower than Word would automatically place it. I want all 3 or 4 lines to be single spaced. If I set the paragraph spacing at .5 pt above, which starts the block of text where I want it placed in the label, it also separates each line by .5 pts, not the single spacing that I want. If each line were its own separate merge field there would not be a problem as I could adjust the paragraph spacing for each line. But it is one merge field with 3 or four lines of text. If I could set the top inner margin or each cell in the table, and set the paragraph spacing for the merge field at 0 pts, that would solve the problem (I think), but I cannot do that in macro record mode. The first of the three images I have attached show the setting that starts the block where I want it. The second image shows the result. The third image shows the result I want.
Attached Images
File Type: png mailing1.PNG (8.3 KB, 6 views)
File Type: png mailing2.PNG (2.2 KB, 6 views)
File Type: png mailing3.PNG (2.2 KB, 6 views)
Reply With Quote
  #4  
Old 03-28-2018, 09:14 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You do not need a macro for this! Simply click anywhere in the table in the mailmerge main document and choose Table Tools|Layout|Properties>Options and set the cell top margin to whatever you want it to be, then save the changes. Done. All cells will now have the required setting for any mailmerge you run using that document from now on.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-28-2018, 09:29 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default

Paul, what you suggest is the first thing I tried but with the macro recording turned on, clicking anywhere in the table, either left mouse button or right mouse button, has no effect whatsoever. Nothing comes up for me to select. Perhaps it works for you on your computer but it sure doesn't work for me on mine.
-- Art
Reply With Quote
  #6  
Old 03-28-2018, 09:50 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Why are you trying to do anything with the macro recorder? This is a once-off exercise, for which a macro is quite inappropriate. Were you able to record it as a macro, by the time you'd done that you'd already have completed the task and you'd no longer need the macro.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-28-2018, 10:26 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default

I'm recording this macro for someone who works in our church office whose computer skills are limited. She is very busy with multiple duties, and will stick with what she knows, such as keeping our members list up to date by typing in changes rather than doing a mail merge from an Excel workbook. But if I give her a word document with a macro such that all she has to do is click on it and generate labels for a mail out based on an Excel workbook that I am also creating for her, I am betting she will go along with what I am recommending. The office will run more efficiently.

Yes, if it were me doing her job, I wouldn't bother creating a macro by recording. This is an organizational issue more than a computer issue. You'll have to trust that what I am trying to do is what will work best with the people involved.
-- Art
Reply With Quote
  #8  
Old 03-28-2018, 11:58 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You don't seem to be paying attention. All you need do is give the user a document with the correct table setup. This is a once-off exercise for which a macro is overkill. Indeed, having done it once, any such macro code would be redundant (since the table would already have the required formatting).

As for what you're trying to achieve, your penchant for using a macro will actually make things harder for anyone else to use and maintain the document & workbook in the longer term; a mailmerge is far simpler for anyone to use with minimal training.

Furthermore, regardless of whether the user types into the labels, employs a mailmerge, or runs a macro to populate the labels, the solution is the same once-off exercise: adjust the table margins.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 03-29-2018, 02:18 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default

Paul, I assure you I have been paying attention.

I thought the person I am trying to help would have to invoke the mail merge wizard, start with selecting the label template, and go though the following steps to complete the mail merge. I did not realize she could open a Word document with a pre-formatted table with all the merge fields entered. I have created such a document. She will have to click on Yes and then Finish and Merge to produce the printable labels.

To ensure her acceptance of a process that she has never used I wanted to come up with a one-click solution by recording a macro.

It turns out that the recorded macro does not work when run in an empty document. It produces one merged label per page instead of the 30 labels per page that the labels template calls for.

I would be interested in learning why. I have recorded macros that work fine and are most useful, so this puzzles me. Perhaps you or someone else who has been following this thread can offer insight. I have copied the code below.

Thank you for your patience with a novice.-- Art

Code:
Sub Macro3()
'
' Macro3 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\Centennial contacts.xlsm" _
        , 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=E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\Centennial contacts.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Eng" _
        , SQLStatement:="SELECT * FROM `MailingContacts$`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""MAILING_ADDRESS"""
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Last edited by macropod; 03-29-2018 at 02:31 PM. Reason: Added code tags
Reply With Quote
  #10  
Old 03-29-2018, 02:30 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by amenu View Post
To ensure her acceptance of a process that she has never used I wanted to come up with a one-click solution by recording a macro.

It turns out that the recorded macro does not work when run in an empty document. It produces one merged label per page instead of the 30 labels per page that the labels template calls for.

I would be interested in learning why.
At best, a macro will save just two mouse clicks in this case - one to answer 'Yes' to the SQL prompt, the other to choose the destination (e.g. printer vs new document). The macro also wouldn't let the user choose which records to process if only a few labels were required.

The reason your macro doesn't work as expected is that it lacks any code to tell Word that it's a label merge. Furthermore, you're adding a single mergefield to the document and are not propagating that to all the labels. It also seems clear to me that you're not actually starting off with an empty document - it already has the empty labels in place. Had you populated the labels manually (again, a once-off exercise), there'd be no need for your code to add any mergefields or to propagate them to all the labels.

The last thing to note is that, if your user was to save the document after running your macro, it would now be a mailmerge main document and would thus generate the SQL prompt next time the document is opened...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 03-29-2018, 03:07 PM
amenu amenu is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Mar 2018
Posts: 6
amenu is on a distinguished road
Default

Hi Paul,

Quote:
Originally Posted by macropod View Post
It also seems clear to me that you're not actually starting off with an empty document - it already has the empty labels in place.
You are right. I did start off with a preformatted table.

Since my previous reply I have recorded a macro that seems to work. It opens the Word document ("MAILING.docx") that contains my latest preformatted table, asks for a Yes, and when Yes is clicked, creates the labels ready for printing. The disadvantages of using a macro are as you say, although reducing the process to one click is more likely to induce acceptance by the person I am hoping to help.

Here's the code:

Code:
Sub Macro4()
'
' Macro4 Macro
'
'
    ChangeFileOpenDirectory _
        "E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\"
    Documents.Open FileName:="MAILING.docx", ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Last edited by macropod; 03-29-2018 at 03:08 PM. Reason: Added code tags
Reply With Quote
Reply

Tags
cell padding, macro, mail merge

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't modify cell margins Sztrogacsev Word Tables 1 11-19-2015 04:27 AM
how adjust cell width manually when table extends beyond paper margins? dylansmith Word 1 11-06-2014 12:01 AM
Colour code mail merge header table cell backgrounds ScotsMaverick Mail Merge 3 06-13-2014 03:05 AM
Formatting imported data into a table with variable record sets ash6540 Excel 6 03-27-2014 04:13 AM
Problem with Cell Margins Aston Word Tables 6 07-15-2011 12:02 PM


All times are GMT -7. The time now is 01:43 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft