Free Microsoft Excel 2013 Quick Reference

MAIL MERGE PROBLEMS

Hello -

I'm very familiar with how to do mail merges....but when I do a mail merge
from our excel database (excel 2002 SP3) to Word.. (word 2002 SP3) to create
labels, I have to specify which values I want to use because I don't want to
use them all (ex: merge 1000-2000) For some reason... it will merge up to
the last number I specify... but then it will go PAST that number just enough
to fill up the last page.

It never did this until fairly recently. Actually I think it started when
we upgraded from 2000 to XP. The original excel database was created when we
used 2000. When I use my original templates that I created in word 2000, it
does not do this. But if I start a brand new label template in Word and
connect it to my excel database to merge... it gives me this problem.

I REALLY need to rectify this problem.

Any advice would be great. Thanks!


Post your answer or comment

comments powered by Disqus
I am running XP Home with Office 2002 applications.

I am not sure if this is an Excel programming problem or a Word Mail Merge
problem. Here is the setup. In an Excel file via Excel programming, I have
opened a Word document. The Word document opened is a mail merge document
that uses a second, different, Excel file as a data source. That 2nd Excel
file (the data source) happens to be open already at the time the Word
document is loaded. The mail merge is via ODBC.

Here is the problem: When the Excel code is run and any record is merged
into the Word file, the date mergefield is merged as a serial value, not the
format I desire ("dd Mmm yy"). All other (non-date)merged fields are merged
in the correct format. This occurs even though the cell in the Excel file
the data came from is formatted as a custom "dd Mmm yy" and the date
mergefield in the Word document uses the field codes @ "dd MMM yy". If I
open the Word document directly and perform the mail merge, the date is
merged in the correct format, not a serial value. I only obtain the serial
value when merged through the Excel programming.

I have searched the NGs and Google but have not found any posts or
references that correct the problem. I am not sure where else to look or
what else to do at this point. Thank you in advance for any assistance or
advice.

Boog

Hello -

Please note that I already know how to do a mail merge and that this seems
to be a deeper problem.

When I do a mail merge from our excel database (excel 2002 SP3) to Word..
(word 2002 SP3)... to create labels, I have to specify which values I want to
use because I don't want to use them all (ex: merge 1000-2000) For some
reason... it will merge up to the last number I specify... but then it will
go PAST that number just enough to fill up the last page.

It never did this until fairly recently. Actually I think it started when
we upgraded from 2000 to XP. The original excel database was created when we
used 2000. When I use my original templates that I created in word 2000, it
does not do this. But if I start a brand new label template in Word and
connect it to my excel database to merge... it gives me this problem.

Any ideas?

Any advice would be great. Thanks!

I am trying to get some data from an Excel worksheet into Word by mail
merge. Two of the fields I have are telephone numbers (formatted as text to
retain the preceding 0). The second phone field goes into the merged
document OK, but the first only displays 0 whenever the number begins with a
0 or is blank.

Can anyone explain reason for and a way round this problem?

--
Ian
--

Hi all,

I hope that someone can offer me some advice.

I have been using an excel (2007) template and a word (2007) template to create documents for my business using a mail merge. All has been working well for the last few months.

However, I have an issue that the date that is being input in excel e.g. 17/07/09 is coming out as 40011 (or similar) when the mail merge is complete. I haven't changed either of the template documents and the cells have been formatted properly in the excel sheet so there is no reason for this problem.

I have googled it and searched other forums but I cannot find anyone having the same problem.

Any suggestions would be gratefully recieved as whilst I am not a novice with computers this has me at a loss!

I have a big problem with excel and word when using a mail merge. Im trying to send out letters for some property a company is interested. All of the data was copied off a website, so there are two fields in excel, and address and the property name.

Like this:

Address Property
213 lane 123 Riverbay
4th ave.
sre, ME 2123

Now when you try to mail merge in Word, the address block comes out all in one line.
Like this:

213 lane[]4th ave.[]sre, ME 2123

I know this is because of how the data is all in ONE cell, but my question is: Is there any way to get the data into different cells, or arrange it so that it works right WITHOUT having to do it all by manually copy>pasting over and over again. I have 160 some odd addresses to do.

I really appreciate any help i can get.

I have created mail merge in word file, and data source is in excel.
My problem is whenever I open word file, every time it ask for find data
source file. Then I will have to give data source path(i.e., excel file)
I don’t want to find data source every time…is there any permanent solution
for this.
vishu

So i have the equation

=IF((C6="Yes"),"«Term_Date» (the date of «EE_First_Name» «EE_Last_Name» ’s separation of service)",IF((C8="Yes"),"«BCD», (the next available distribution date)",IF((C10="Yes"),"«BCD», (the next available distribution date)", "")))

this works fine,

but when i run the mail merge, it puts this output when C6 is yes.
this is what i want to happen, but when i run mail merge, it does not regocnize

My first post! Any help is appreciated. I'm using Excel 2010.

I'm trying to mail merge just a portion of 1,890 addresses. In Excel, I successfully highlight and save the wanted addresses as a defined name. Then, in Word, after the Confirm Data Source dialog step and in the Microsoft Office Excel dialog box where you select "Named or cell range" my defined name does not appear. I can only get it to merge the entire 1,890-entries-long document.

I have been at this for hours reading help docs and watching tutorial videos on YouTube (which have been helpful) but I cannot figure out why my document is not doing what the Excel help doc says it should. I could cut and paste the wanted addresses into a new document but why do that if I don't have to and when there is already a function to avoid doing just that?

Any help is appreciated!

Hi,

I'm hoping someone can help me because I'm completely stuck!
I have been using Office at work (which I think is 2003) and trying to mail merge in Word using an Excel data source.

I have 3 different formats of letters in Word. I have 4 sheets in the workbook - one for each of the different types of letters, and 1 that I use as a lookup table for the others.

2 of the 3 letters have merged beautifully, but when it comes to the final merge document:

I get the "Browse" part to find my data source
I locate the file and select it
I get varying results in the list of sheets in the workbook - I get "NSW - FC Proposals", "NSW - FC Proposals_", "NSW - FC Proposals - Print Area", and most disturbingly "NSW - FC Proposals-FilterDatabase". These results seem to vary each time I try the merge, without me having made any changes to the Excel file.

No matter which option I select, the next dialog box lists NO tables and I cannot go any further. It tells me that it was unable to open the data source.

The spreadsheet that I'm having all this trouble with is the only one that uses the lookup table (done using =INDEX and matched to 2 criteria) and so is quite formula-heavy. I have also used every column available in the spreadsheet, so I don't know if that is a problem.

I don't understand why the other sheets in the workbook merge perfectly and this one refuses to work - any suggestions?

Thank you!

Simple issue. In Excel I have dates in the form 26 June 2006 and I wish
to mail merge them with iother data into a word file. However, when I
do this, the dates appear in the word file in American date format
06/26/2006 ... not 26 June 2006.

Strangely even if I put the date in the excel cell as 26/06/2006
(Australian date formatting), it still turns up in the word file as
06/26/2006!

Any ideas how to fix this annoying problem?

--
CFD

------------------------------------------------------------------------
CFD's Profile: http://www.excelforum.com/member.php...o&userid=27306
View this thread: http://www.excelforum.com/showthread...hreadid=560905

I have just been teaching a familiar topic: using data from Excel to mail merge into Word to make Invoices.

However, my demo went wrong, and several of the merged fields ended in the wrong part of the Invoice table, in Word.

It also happened to 4 of the students, but not the rest. Trying to isolate the source of the problem, we tried again with a simpler spreadsheet and Invoice layout, and the problem vanished, but we could reproduce it by merging the original Invoice layout with the original data in Excel.

Any help in re-inflating my ego much appreciated !!

Felix

Simple issue. In Excel I have dates in the form 26 June 2006 and I wish to mail merge them with iother data into a word file. However, when I do this, the dates appear in the word file in American date format 06/26/2006 ... not 26 June 2006.

Strangely even if I put the date in the excel cell as 26/06/2006 (Australian date formatting), it still turns up in the word file as 06/26/2006!

Any ideas how to fix this annoying problem?

Hi all,

I've been working with this "typical" marco for some time. Once my company have updated my office application to 2003 version, I found out I've problems working in my old way with this macro, so I need experienced users to help me solving the problems.

The problem happens when I was asked to open the "distribution list" after I run the macro. Usually I got an excel file which contains all the email address. Everytime I just need to update the file path in the list against each email address before sending out. In the past version of Word, when it asked me to open the source data file after I ran the macro, the Word will help me to "CONVERT the EXCEL Table" to Mail Merge Format. However, after it's been updated to Word 2003, it couldn't convert the excel table correctly. The "send-out" list will now be converted to some meaningless codes... so, the operation will fail.

Anyone knows how to sort this out?

Thanks,
NC

Hello friends,

I am using word 2003.

I have a mail merge document which is getting information from a spreadsheet. The form is great and works properly.The merge mail toolbar has the ability to navigate records and also shows you in which record you are.

But the Problem is that

when you are in record 100 or higher it shows you 2 digits only.
example:

record shows
101 10 (last number is missing)
99 99
123 12 (last number is missing)

Many thanks

Hi

New to the forum and very happy to be here.

Quick question. I'm doing a mail merge using an Excel file as the source. My problem is that if a cell is blank (containing no value) in the spreadsheet I would like it to return a blank in the Word merge, but it returns a zero. I am convinced that it can be fixed but have ran out of ideas. Please, won't the guru's of this fine forum help me out here? Much appreciated in advance!

Hello,

I have some code that runs a mail merge between excel and microsoft word. However, it is only successful about 50% of the time. During the other 50% I get a run-time error that says "Remote Server Machine Does Not Exist or Not Available":


	VB:
	
 FillLabels_Click() 
    Dim BlankRow As Long 
    Dim lbs As Worksheet 
    Set lbs = Worksheets("Labels") 
     
    Dim strThisWorkbook As String 
    strThisWorkbook = ThisWorkbook.FullName 
     ' create the word document:
     
    Dim oWORD As Word.Application, wrdDoc As Word.Document, wrdTable As Word.Table 
    Set oWORD = New Word.Application 
    Set wrdDoc = oWORD.Documents.Add 
     ' so we can see what is happening in word:
     
    oWORD.Visible = True 
    wrdDoc.Activate 
     ' adjusting the page setup properties first:
     
    With wrdDoc.PageSetup 
        .Orientation = wdOrientPortrait 
        .TopMargin = CentimetersToPoints(1.3) 
        .BottomMargin = CentimetersToPoints(1.3) 
        .LeftMargin = CentimetersToPoints(0.5) 
        .RightMargin = CentimetersToPoints(0.5) 
        .Gutter = CentimetersToPoints(0#) 
        .HeaderDistance = CentimetersToPoints(1.25) 
        .FooterDistance = CentimetersToPoints(1.25) 
        .PageWidth = CentimetersToPoints(21) 
        .PageHeight = CentimetersToPoints(29.7) 
    End With 
     
     ' creating the table of labels (in this example, 3 columns, 10 rows):
     
    Set wrdRange = wrdDoc.Range 
    Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=10, NumColumns:=3, _ 
    DefaultTableBehavior:=wdWord9TableBehavior, _ 
    AutoFitBehavior:=wdAutoFitFixed) 
     ' adjusting the table properties:
     
    With wrdTable 
        .Columns.PreferredWidth = CentimetersToPoints(9.9) 
        .TopPadding = CentimetersToPoints(0) 
        .BottomPadding = CentimetersToPoints(0) 
        .LeftPadding = CentimetersToPoints(0.3) 
        .RightPadding = CentimetersToPoints(0.3) 
        .Rows.HeightRule = wdRowHeightExactly 
        .Rows.Height = CentimetersToPoints(2.48) 
        .Rows.Alignment = wdAlignRowCenter 
        .Spacing = 0 
        .AllowPageBreaks = True 
        .AllowAutoFit = True 
        .AutoFitBehavior (wdAutoFitFixed) 
        .AutoFitBehavior (wdAutoFitFixed) 
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone 
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone 
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone 
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone 
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone 
        .Borders(wdBorderVertical).LineStyle = wdLineStyleNone 
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone 
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone 
        .Borders.Shadow = False 
    End With 
     ' getting the range of data from out excel data tabel:
     
    Range(Rng("Labels")).Select 
     ' finally, propagate the data into your labels with a mail merge:
     
    With oWORD.MailingLabel.Application.ActiveDocument 
        .mailmerge.MainDocumentType = wdMailingLabels 
         ' choosing the right document and data table:
         
        .mailmerge.OpenDataSource ThisWorkbook.FullName, ConfirmConversions = "False", ReadOnly = "False", _ 
        LinkToSource = "True", AddToRecentFiles = "False", , , , , , , _ 
        "Data Source=" & strThisWorkbook & ";Mode=Read", _ 
        "SELECT * FROM `Labels$`" 
        .mailmerge.DataSource.ActiveRecord = wdFirstRecord 
        .mailmerge.DataSource.ActiveRecord = wdFirstRecord 
         ' iterate through each label:
         
         ' for each row (in excel):
         
        For r = 1 To .mailmerge.DataSource.RecordCount 
             ' for each field (in excel):
             
            For f = .mailmerge.DataSource.DataFields.Count To 1 Step -1 
                .Application.Selection = .mailmerge.DataSource.DataFields.ITEM(f).Value & vbCrLf 
            Next f 
             ' go to the next row (in excel):
             
            .mailmerge.DataSource.ActiveRecord = (r + 1) 
             ' go to the next label (in word):
            .Application.Selection.MoveRight Unit:=wdCell 
        Next r 
         ' to be sure your data is visible:
         
        .mailmerge.ViewMailMergeFieldCodes = wdToggle 
    End With 
     ' no closures for your word object needed as you close your word app later on yourself.
    lbs.Range("A2:P90").Value = "" 
    Unload Me 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The error appears at the first With Statement:

	VB:
	
 wrdDoc.PageSetup 
    .Orientation = wdOrientPortrait 
    .TopMargin = CentimetersToPoints(1.3) 
    .BottomMargin = CentimetersToPoints(1.3) 
    .LeftMargin = CentimetersToPoints(0.5) 
    .RightMargin = CentimetersToPoints(0.5) 
    .Gutter = CentimetersToPoints(0#) 
    .HeaderDistance = CentimetersToPoints(1.25) 
    .FooterDistance = CentimetersToPoints(1.25) 
    .PageWidth = CentimetersToPoints(21) 
    .PageHeight = CentimetersToPoints(29.7) 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Could someone help me find the problem?

Thank You For Your Help!

Hi all,

due to a change of our directory structure I have to update the link to the Access data source for about 400 mail merges.
In our database we use parameter queries. All main documents work with such a parameter query (all to the same query). Therefore, we must connect to our database using DDE and not ODBC. How can I define this DDE connection in VBA? I have tried it via wdMergeSubType:

	VB:
	
[CENTER][LEFT]Sub ChangeDataSource() 
 
ActiveDocument.MailMerge.OpenDataSource Name:= _ 
"C:Finale Datenstrukturneue Dateien   LaptopDiagnostikSerienbriefaktualisierung Testdatenquelle2.mdb", _ 
ConfirmConversions:=True, ReadOnly:=True, LinkToSource:=True, AddToRecentFiles:=False, _ 
Connection:="QUERY qry_SEQ_Befund", SQLStatement:=   "SELECT * FROM [qry_SEQ_Befund]", _ 
SubType:=wdMergeSubTypeOther 
End Sub 
[/LEFT] 
[/CENTER] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Here the correct query seems to be selected (at least I don’t get an error message) and the modification of the linked data source seems to work well as the correct (changed) data source is returned upon the following macro:

	VB:
	
 ReturnDataSource() 
     
    If ActiveDocument.MailMerge.DataSource.Name  "" Then _ 
    MsgBox ActiveDocument.MailMerge.DataSource.Name 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, when executing the macro I get a dialog which asks for the choice of a table, which is not what I intended. If I select the table on which my query is based, everything works fine so far. I only just don't completely understand why this choice is necessary at all. The problem which arises from it, is that the system doesn't ask for my parameter any more if I close the document and reopen it. I get the message that the table which was selected before is being opened, but I need the query to be linked to my main document otherwise there is no way to enter my parameter in order to select a certain data set.

Does anybody have a clue? I would be grateful for any ideas as I just don't get any further here.

Many thanks in advance!!!

Hello,
would like to pick your brain for a sec please. I am pretty good with mail merge where I can set up the source in excel, use the mail merge wizard in word and do what I need to do. Now I came across a problem where I am trying merge a picture along with the data. Here is what I am doing. I have some pictures saved in c drive as lets say ab.jpg, cd.jpg ef.jpg and so forth.
In excel I have 3 column as below
Last Name First Name Photo
a b ab.jpg
c d cd.jpg
e f ef.jpg

When I do the mail merge in Word, I can get the last name and first name to update, however I cant get the picture to show up. I am using { IncludePicture "c:{ MERGEFIELD Photo }" d }
for the photo field. I am merging to a new document and cant get the picture to show up. What am i doing wrong. Any help would be appreciated. Thanks

I'm trying to create some Excel code to eventually produce a mail merge document in Word.

The code works fine, and a mail merged document comes out of other end, but, my problem is that I don't want to return all records in the range that I've defined in the Excel 'table'. This works fine if doing the process manually in Word using the 'select recipients' mail merge option but I can't get the code into Excel. All help I've come across suggests that a SQL statement is the answer, but whatever I put in I still get all rows returned.

An extract from my code (edited to make a bit easer to read) is below


	VB:
	
 Word.Application 
Dim WordWasNotRunning As Boolean 
Dim wdDoc As Word.Document 
 
 'Get existing instance of Word if it's open; otherwise create a new one
 
On Error Resume Next 
Set wdApp = GetObject(, "Word.Application") 
If Err Then 
    Set wdApp = New Word.Application 
    WordWasNotRunning = True 
End If 
 
On Error Goto Err_Handler 
 
wdApp.Visible = True 
wdApp.Activate 
Dim t 
t = "template name.dot" 
Set wdDoc = wdApp.Documents.Add(t) 
 
Dim sql 
sql = "SELECT * FROM `NamedRange` WHERE `FieldName` IS NOT NULL" 
With wdDoc.MailMerge 
    .OpenDataSource _ 
    Name:="source file.xls", _ 
    Connection:="NamedRange", _ 
    SqlStatement:=sql 
    .Destination = wdSendToNewDocument 
    .Execute 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Note that NamedRange is a range named on a worksheet, and FieldName is the name of one of my Columns

Any suggestions would be much appreciated.

Hi,

The scenario is as follows. Assume you have an excel file with a column filled with some paper titles.These titles are hyperlinked to the corresponding pdf files. Now assume that you want to use the mail merge in Word to create a text that will contain the data of that column. My problem is that when i use the mail merge, everything works fine BUT the hyperlinks dissapear from the titles. They are just plain text. To help you even more i need that because i am creating a CD with the proceedings of a conference and when someone will go to the program guide i am creating i would like him to press the title of each paper and the corresponding pdf will open. Any ideas???

--------------------------------------------------------------------------------
I used mail merge to merge the data stored in a Excel file to a Word document that is far better looking... Along with the data, i also want to merge pictures in specific places in the letter. [By the way, the best guide i fould to do this is http://www.computorcompanion.com/LPMArticle.asp?ID=126].

The picture is merged without any problem and is updated when i change record, select the picture and press F9. The problem is that when i save the document and close it, the picture wont update even if i do the above mentioned procedure. I have to merge the picture every time i open my file. Thats not the biggest problem. I have written a macro to do that.

The real problem is that when i print the letters, in all letters are used the same pictures as in the letter shown by word at the time. It doesnt update the picture in every letter printed.

Can anyone give me a hint plz? I am using Microsoft Word XP.

Thanx in advance

Johnny B.

I have a large spreadsheet which needs to be sorted prior to mail merge. It contains details of thousands of clients and their investment holdings.
The way the data has been extracted from our mainframe means that its cant be used as it is for a mailmerge.
Part of the mailmerge includes a table which details member specific information - the policy manager (eg Norwich Union etc). the policy number, the policy type (eg Pension) and the premium. Each client needs to have their own table.
The problem I have is that if, for example, if Mr Smith has 20 policies, he appears 20 times in Excel, running down for 20 lines.
For the purposes of the mailmerge, I need each clients details to appear on one continuous line, so Mr Smith could have one long line that read 'Norwich Union, PR12345678, Pension, £5000, Scottish Widows, PR98765432, Bond, £10,000 and so on.
For every line on the current spreadsheet, a letter is generated in the mailmerge. This means that the unfortunate Mr Smith would receive 20 letters, each one with a table with one line on it. Not hugely professional.
Can anyone help???

Good day

I am such a beginner that it is proving to be very sad. I am working on a number of spreadsheets in excel compiling data (contact information) that I can use for mail merge to send out direct mail.

Out of four separate excel files I am working with, I would require being able to sort data based on columns for all. For instance if labeled “Name / Address / Phone / Zip” I would like to be able to sort and look at data concurrent with the zip code. So all information in a row would be transferred according to at place when sorted by zip code. If I do it the only way I know how, the zip codes will in fact sort ascending to descending, however; no other information will move, so basically I have improper addresses.

I have followed other posts listed on the MB’s, and I have successfully implemented the ability to omit duplicates.

In addition to the sorting, I believe I have a problem. On 2 of the 4 spreadsheets the address for instance it is not properly done, to have a row dedicated to all fields for a specific customer.
It does however, have:
One row listing name / city, state zip / email
Another row listing physical address / telephone #

Is it possible to mail merge with this type of entry? If not, is there any other way to add every other rows information, into the one before it?

And finally, I have 2 documents, one for instance carries information about company names…..and the other lists employees with in the company. Is there anything that can be done to take all employees with one company and have information placed into one final document that would list companies and its corresponding employees in one?

Apologies, that my writing is so primitive. I am so very clueless. I understand that I could not put in all information you might need, please feel free to reply or e-mail if more info is needed to help. Thanks again.

Hi,

I have a spreadsheet of ~1000 items, that I would like to do a mail merge document for.

I have a template document in Word at the moment, (I could do it in excel if it is easier, but at the moment, it is a word document) but the problem i have is that there is a summary section of a maximum of three stocks (three different columns).

e.g. in Excel

Stock A Stock B Stock C
1000 1500 2000

should go in to the word mail merge as:

Stock A 1000
Stock B 1500
Stock C 2000

BUT, and here is where the problem lies: if i dont have a holding in e.g. Stock B, I don't want to see it on the mail merge document.

Is there anyway that I can do this? In excel i would do something like IF(quantity of stock B = "", give me "", "Stock B")

james


No luck finding an answer? You could always try Google.