Access 2010 executing Word VBA: Mail Merge (why is result a little diff than using Office 2003)

Hi EE Experts,

We are moving from Office 2003 to Office 2010, so am testing mdbs under Access 2010.

The Mail Merge doc created from the mdb under Access 2010 is a little different than what Access 2003.

Will point them out in screen shots below.... is there anything I can modify in the Word doc (that I use as a template)

Will also paste the components of this project:

a) mdb   - simplified by removing forms etc.... pls go to modules,  and run:  Test()

b) excel - test data

c)  word - mail merge doc

d) a student doc for Thomas Jefferson created by Access 2003 mail merge under XP

When you run Test(), it will execute a mail merge doc containing 3 students, then it breaks up the big doc to 3 docs for each student.

This is the Mail Merge Access 2003 version.
a) Please see the red circle      -    Date is all 1 font: Times New Roman 12
b) Please see the blue square -   Dates are aligned


Mail-Merge-xp.bmp
This is the Mail Merge - Access 2010
a) Please see the red circle - it's Arial now, and different font size between Month and Year
b) Please see the green square -  too much blank space, some blank space we want bec that's where letterhead goes
c) Please see  the blue square - it's hard to see with my test, but the dates are not aligned, bec the next date continues on the next page so have to give you a screen shot for page 2Mail-Merge-w7a.bmp
Showing the blue square of misalignment by taking a screen shot in Word itself (not print preview)

Mail-Merge-w7b.bmp
 

Your help is very much appreciated. Will paste the project components below....
tx,
sandra
2015-05-25-EE-ARG-05-May-MSPE-frontend.m
wMaster-MSPE-template.doc
xMSPE-Students.csv
mytfeinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mytfeinAuthor Commented:
Just checked,  the word doc that is used as template is entirely Times New Roman 12, yet it's creating a merge document in arial?

Should I modify any settings in Word 2010 such as default font.

I am new to Office 2010.

tx, sandra
0
GrahamSkanRetiredCommented:
We cannot see from your pictures exactly why that is. How is the alignment achieved?

One way would be to use a table (without printed borders). Another would be to use tabs and tab stops.

It is not reliable to use multiple spaces, since the size of the space can vary, especially if the font name, size or version is changed. It could even change with a change of printer driver.

It is likely that the font and the vertical spacing have changed because the Normal style has changed between Word versions.
0
GrahamSkanRetiredCommented:
Only just noticed the documents that you posted as well as the pictures. I will see how it runs here,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mytfeinAuthor Commented:
Hi Graham,

Thx so much for  for replying...

What you said here is probably the reason:
<It is likely that the font and the vertical spacing have changed because the Normal style has changed between Word versions.>

I see that even the vertical spacing between paragaphs when I run the merge against real data is larger than under Access 2003.

It's like it's doing double spacing, even though my source doc is single spacing between paragraphs.

Question, please:  Is there a way I can set a custom Word Style in the Access -> VBA.? If yes, so you have similar logic that you can paste here?

Maybe the default font for the Normal template is Arial, and that's what the o/p of the mail merge is even though the source template is times New Roman.... wish I could override it...

I inherited this mail merge doc from someone else...

I have to find the ruler to look for tab stops...

I will explore using a table without borders....

thx for the help, sandra
0
GrahamSkanRetiredCommented:
The output on my (2007) looks OK. It is in Times New Roman.

I think it might be a Styles issue, so I'll try an experiment or two.
0
mytfeinAuthor Commented:
Hi Graham,

I placed the text in a 2 column table. Am keeping the borders on for now.

2 questions, please:
1) In print preview, the right border is not showing
2) It does not allow me with the mouse to narrow the width of the rows (to remove blank space)

In Word doc:

screen shot 1

In print Preview:

screen shot 2

3) Another issue, is like the table has a mind of it's own, it expands horizontally the cell if I type
    It added a blank row, and does not let me delete it.....

Here is a print preview of the extra row that it does not let me delete:

screen shot 3    (I deleted paragraphs of Student Info following the table for privacy)


I copied the table to a brand new doc, and the extra blank row disappeared.!!!

but the right border now showing in print preview, and that it does not allow me to decrease row size with a mouse is in this new doc too which uploaded below....

Any help you could provide would be greatly appreciated, thx, sandra
Date-of-Initial-Matriculation-in-Medical
0
mytfeinAuthor Commented:
typo above: the right border is ** not** showing in print preview

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

think found answer to the extra row, by uncheck off in this screen shot:

to disallow row to break across pages

screen shot  - dis - allow row to break across pages
tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I found this screen to set the row height manually

I chose from dropdown exactly... then row adjusted... then it allowed me to use the mouse to resize....


manually set row height
Is this the correct approach or is their a better approach... Is there a way I can modify so that "exactly" is the default.....  or to set all rows in one shot to "exactly" instead of manually one row at a time...

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I see in the row dialogue box that it allows you to click next to step through to the next row ...

below is a screen shot

screen shot
tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I managed to drag the right border more to the left, maybe it was in the page margin

and I see the right border in print preview

Below is a screen shot

screen shot
tx, sandra
0
mytfeinAuthor Commented:
oops, wrong screen shot of corrected border

here it is....

screen shot
0
mytfeinAuthor Commented:
Hi Graham,

So ran the mail merge with real student data.

Removed the table grid lines.

Below is a sample of the new doc template

 screen shot 1

but when I run the mail merge in Word 2010, it added some space between lines, which you'll see in red

screen shot 2
If you have any thoughts, would greatly appreciate,
tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

Could it be because I am running a .mdb/.doc   on a Windows7/A2010  pc?

Work is cutting of XP pcs from the network in a few days.

My users are using Windows 7.  They remote into another XP machine to use Access 2003 custom system that I designed.

I have been testing the mdbs on A2010, some tiny changes to vba was necessary.

This is the only Mail Merge project I have.

With your table idea, it looks presentable..... just the line spacing issue, and even that the merged document is Arial when the source is Times New Roman remains....

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I am looking closely:

Am using Style: Normal   (is there any way i can see the line spacing)

It seems like it's doubling line spacing.... so many blank lines after Sincerely,    for example

tx, sandra
0
GrahamSkanRetiredCommented:
Sorry Sandra,
I had to go and cook dinner. Someone was getting anxious.
I'll try to catch up with what you've been posting
0
mytfeinAuthor Commented:
ok, so i checked:  

In the source doc:

 the font dialogue box shows Times New Roman/12

and the line spacing button shows 1.0

In the merge doc:  (do not understand how the below happened)

font is +body
line spacing 1.15

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

Oh no worries.... thx so much for tables suggestion... it helped....

I did a shift+enter, in the column that has a wider spacing, so I deleted the text and retyped it to get rid of the shift + enter

I see that it's 1.15 spacing in the new merge doc, so am trying to delete spaces in the source doc to make it look presentable...

i worry why it's not 1.0 spacing, as well.

wondering if i would convert to accdb/docx would the whole situation improve

i am definitely learning the tables area of word2010 so that's good :-)

at least i have working merge that is somewhat acceptable...

now i have to mail a copy of what i have to my colleague who prints them to see if it lines up with the letterhead.....

if that goes well, at least we're not in trouble as we run these letters in June-July

but what the 1.15 line spacing....hmmmm....

hearty appetite, thx, sandra
0
GrahamSkanRetiredCommented:
If you use the Show/Hide(¶) button to display the formatting marks, you will see that the paragraph tab stops all at the default settings, which is only 1.27 cms apart, so the text can easily run past the target stop and be positioned at the next. I think that tables are a bit easier, but if you wanted to stay with tabs, surplus stops should be cleared.

I postponed opening the database file that you posted because the name had been truncated and I didn't realise that it was a database. Even when I did open it, it didn't seem relevant. However, I now see that there is some code in it where the output result document is split into individual documents. The code looks good (I think I wrote something rather like it a while ago), but the new documents it creates are based on your current Normal template - which is why you are seeing these problems. The default paste option uses the styles of the destination document.

As a workaround, try changing the Paste Option. It can be done in code. This changes it for the run and resets if afterwards

Public Sub w005_MSPE_MailMerge()
  ...

' Copy the full Mail Merge to a new Word doc called DOC A
    Set DocA = appWord.ActiveDocument
    
    Dim lngSectionsCount As Long
    Dim lngLenText       As Long
    Dim iCurrentOption As Integer
    
    iCurrentOption = appWord.Options.PasteFormatBetweenStyledDocuments
    appWord.Options.PasteFormatBetweenStyledDocuments = 0 'wdKeepSourceFormatting
    lngSectionsCount = DocA.Sections.Count - 1
    Debug.Print DocA.Sections.Count
    
    For Each sec In DocA.Sections
      ....
        
    Next sec

    appWord.Options.PasteFormatBetweenStyledDocuments = iCurrentOption
    
    
  ' Close copy of Mail Merge Doc
DocA.Close savechanges:=False

...

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mytfeinAuthor Commented:
Hi Graham,

I do not know how to search my old questions.

I believe that you did provide the code to break up into smaller sections.

The workday is over in this part of the world, so with a fresh head will study your code.

I did not realize to check the vba for clues.... thx so much....

I like the tables idea instead of tab stops... I just don't feel "in control" of tab stops...

Will write back tommorrow... good evening....thx again, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I pasted the additional lines of code relating to Paste Options that you provided.

The source formatting is not carrying over to the target document, though....

Will keep testing and googling using the statements you provided.

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

I think other(s) have had trouble  ... for example: here

https://social.msdn.microsoft.com/Forums/vstudio/en-US/7d0f2b0e-5ad5-4b31-a562-0a9a807130f9/wdpasteoptionswdkeepsourceformatting-options-is-not-working-with-word-document-customization?forum=vsto

I do not know how to apply a combination of your and their idea for my situation....
tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

Modifying the suggestion in the web page above, did this:

     'DocB.Range.Paste
       
       
        DocB.Range.PasteAndFormat (Word.WdRecoveryType.wdFormatOriginalFormatting)

It worked! I close one of the student word doc files, then

I opened up Word, and it did NOT permanently change the Normal.dotm template.

So think we are good.

However, Is there a way I can change the Normal.dotm template that it should be:

Times New Roman 12   line spacing 1.0  (I can open a new post on EE,  if you'd like :-)   )

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

Can the margin settings get copied from source to target document, as well?

will google...

tx, sandra
0
mytfeinAuthor Commented:
Hi Graham,

So found code to set margins, and added it to my code like this:

' http://www.mrexcel.com/forum/excel-questions/397646-how-do-i-set-word-margins-visual-basic-applications.html
        'https://msdn.microsoft.com/en-us/library/office/ff836129.aspx
       
        With appWord.ActiveDocument.PageSetup
            .TopMargin = appWord.InchesToPoints(0.31)
            .BottomMargin = appWord.InchesToPoints(0.17)
            '.LeftMargin = WordApp.InchesToPoints(1)
            '.RightMargin = WordApp.InchesToPoints(1)
        End With

tx, sandra
0
GrahamSkanRetiredCommented:
Sorry, I've been out today.

So glad you were able to get it working. There seems to be a bug with that option setting. I haven't actually used it. I just knew that it was there and I was too short of time to test it yesterday.

It is possible to modify the Normal style in the Normal template. Note that the Normal style isn't linked with the  Normal template in particular. Other templates also have a Normal style. In both instances, Normal just means 'default'.

To modify a template style, make sure that active document is one created from the template in question. Right click on the style on the Home tab and choose 'Modify...'. At the bottom of the Modify Style dialogue, choose 'New documents based on this template'. You can select your font in this dialogue. For the other settings you will have to click on the Format button.
0
mytfeinAuthor Commented:
Just wanted to let the public know.... that had to modify Graham's suggestion to this, in order to carry formatting to destination document

 DocB.Range.PasteAndFormat (Word.WdRecoveryType.wdFormatOriginalFormatting)

thx so much Graham for all your help!

G-d bless you and yours.... your solution saved my life :-)

sandra
0
mytfeinAuthor Commented:
thx so much Graham for all your help!

G-d bless you and yours.... your solution saved my life :-)


I have to leave early, will read your last comment on Monday  :-)    thx!

sandra
0
GrahamSkanRetiredCommented:
OK. Have a good weekend.
0
mytfeinAuthor Commented:
Hi Graham,

read your last suggestion re:Normal style  with a clearer head.

I followed your directions, and now when I open word 2010 the document has the defaults that requested:

Time New Roman 12
Single spacing

For the public:  
To Modify line spacing:

       right click on Normal Style, select Modify, select Format, select paragraph

Thx again Graham, sandra
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.