Free Microsoft Excel 2013 Quick Reference

Controlling Word Mail Merge with VBA

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.


Post your answer or comment

comments powered by Disqus
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!

We will need to do 2,000 mail merge letters with Excel/Word. Not normally a problem. However, on this job every letter will contain two different graphics - yes, that's 2,000 records and 4,000 additional graphics files! How do we define a graphics file name within a cell so that Word will select the correct file for printing?

Thank you

Hi

We started using word 2003 recently.

In Word 97, we did mailshots merging up to 30,000
records with a problem.

With Word 2003, this recipient list box appears -
it takes an age to populate 20,000 records and then
when we go to look at the mail merge fields in the
word document, Word crashes.

Any suggestions ?

Tks
John

Hi,

I am trying to insert information (figures) contained in an Excel
worksheet into a Word letter (in the example I have 100 clients with
their monthy bill information and have to send out letter to every one
of them with his particular billing information). I did it with the
Word command Mail Merge withing TOOLS, inserted the fields and worked
pretty well. The only problem is that the figures appear in the Word
letter with 8 decimals, which of course looks very bad. I could not
manage to delete the decimals in the Excel spreadsheet, they are always
there, even if you format the cell, as a number with cero decimals or if
you customize the cell with cero decimals. They don't disappear.(This
happens in Excel 2000 and 2003)

What can I do to insert figures without decimals into a Word letter?
I hope you can help me.
Alejandra

--
Alejandra
------------------------------------------------------------------------
Alejandra's Profile: http://www.excelforum.com/member.php...o&userid=31933
View this thread: http://www.excelforum.com/showthread...hreadid=516590

Word 2003
Excel 2003
Windows XP

Why would a mail merge show only every other Excel row when merging with a
Microsoft Word form?

I am also cross-referening this to the Word forum.

Thanks in advance!

I have an Excel spreadsheet of names and addresses that I am trying to mail merge with Word. Does anyone out there know how you only pull certain info from the spreadsheet?

Example:

Name: Address: Phone: Age: DOB:
Jon Doe 123 East 123-4567 29 Yesterday

I only need the name and address for the label how do you cut the phone, age and DOB out?

I appreciate any and all help!!

Hi,

I am trying to insert information (figures) contained in an Excel worksheet into a Word letter (in the example I have 100 clients with their monthy bill information and have to send out letter to every one of them with his particular billing information). I did it with the Word command Mail Merge withing TOOLS, inserted the fields and worked pretty well. The only problem is that the figures appear in the Word letter with 8 decimals, which of course looks very bad. I could not manage to delete the decimals in the Excel spreadsheet, they are always there, even if you format the cell, as a number with cero decimals or if you customize the cell with cero decimals. They don't disappear.(This happens in Excel 2000 and 2003)

What can I do to insert figures without decimals into a Word letter?
I hope you can help me.
Alejandra

I'm trying to I create a Microsoft Word mail merge that uses an IF statement to control whether or not to merge a particular record from an Excel file. It will only merge the current record if the name is different than the previous record. It goes something like this:
{If {Name1} {Name2} "code to do mail merge" ""}

The last set of double quotes has nothing in it. I have used that in
other mail merges to have the merge continue with no action when the
IF statement condition is not met. In this case it is not merging
the record (which is good) but it is adding a page break resulting in
many empty pages being inserted into the merge (which is bad). How do
I get it to not add the extra empty pages?

Mail Merge with Outlook

When I try to do a mail merge in word using outlook I get the following error mesage "Current mail client cannot fufill message request"  I have double chekc that Outlook is my defaul mail client.  I don't know what else to do.  HELP!

I have an excel workbook with information that acts as a header source for
word mail merge documents. In excel I am able to open word and the mail merge
document that I want.
But... when I ask word (still through excel) to open the workbook an attach
it as the header, it takes about 2 minutes to process the request.
Also word wants to open another copy of excel when one is already running.
Any suggestions
Thanks for taking a look at this
Alfred

I'm not sure where to go for help. I'm under pressure to help a collegue but don't have the skill. I've been here for myself and gotten wonderful help so I thought I'd try this.

They have an excel data structure of client records for this non-profit. They are trying to collect on a lot of late payments that were promised to them. I'm simply trying to help.

If there is somewhere else I should go please advice me of such. Thanks thanks thanks.....
Mike O

Excel File
Address First Last Item Purchase
1234 Street Bill Johnson XYZ $4.50
1234 Street Bill Johnson RST $5.60
1234 Street Bill Johnson ABC $3.50
3344 Lane Wanda Wilson XYZ $4.50
5789 Drive Jim Franklin NOP $0.99
5789 Drive Jim Franklin EFG $5.89

I'd like to have all the individual records and the total in three Windows word mail merge letters. I do not have Query or Access or understand VBA.

Dear Bill,

As of today you owe $13.60 for:
XYZ $4.50
RST $5.60
ABC $3.50

----------------------------------------------------------------------------------

Dear Wanda,

As of today you owe $4.50 for
XYZ $4.50

----------------------------------------------------------------------------------

Dear Jim,

As of today you owe $6.98 for
NOP $0.99
EFG $5.89

I have an excel spreadsheet #1 with 20+/- differnent columns, some columns have a vlookup to another spreadsheet that has names and address. I use this spreadsheet for the unique information that will be needed in my word mail merge. I also have another spreadsheet #2 that after printing my mail merge, I will cut copy and paste spreadsheet #1 to the record keeper spreadsheet #2. Is there a way I could put another column in my spreadsheet #1, that when entered a Y for printed it will automatically post over to spreadsheet #2? Also, I just want the value on spreadsheet #2 not any of the formulas.

Hi,

I hope someone can help.

I am trying to set up a mail merge in word using my outlook contact details to generate the address for the letter I have created.

However, when using the word mail merge wizard I get to step three where it gives me the option to select recipients - I select "Select from outlook conatcts" and then it gives me the option to choose contact folder. When I go to the next step I get the following error message...

"The operation can not be completed because of a dialog or database engine failure. Please try again later."

Any ideas what this means and how to fix it?

Many thanks.

Hi,

I'm trying to create a macro that runs from an excel data source, opens Microsoft Word and merges specified fields into a letter (the fields/data are in the excel document).

What is my best approach for attempting this?

Thanks

Hi,

I am using excel figures in a word mail merge and they keep showing up in the word document as the wrong format.

I would like the figure to be displayed in the mail merge as 14,316 however it is coming up as 14316.709999999999.

I don't seem to be able to format the figure in word, does anyone have any suggestions on how I would format it in excel?

Thanks

Ginny

I have exported my customer list from Peachtree and opened the .csv file into
Excel. What do I do to the file now to make it's contents flow into my
letters in Word mail merge?

I have a series of macros in an Excel file that users use to create spreadsheets. When they are finished creating spreadsheets they can print "reports" in Word. To do this I am controlling Word from Excel with VBA. This works fine for all of the associates in my office. However, I have an associate in another office (about 1500 miles away) that has Word crash whenever he tries to run reports. He gets the error box "Word has encountered a problem and needs to close" and is then asked to send or not send an error report. Once he clicks either of these options the Excel macro fails because it is trying to talk to Word that isn't there. Since he is so far away I cannot tell what is going wrong. Like I said, no one else has these problems and the rest of the macro works fine for him.

I talked to our corporate "help" people but they of course were no help whatsoever. The excel file with all the macros is located on our shared drive that this associate also has access to. Any ideas of what could be causing this or how to fix it?

Hi I need to know how do I perform a Mail-merge with the 07 excel. I need to print the worksheet on small labels for a mass mailing that I am doing. Thank you for your time and have a nice day.

*BIG Mike*

Hi All,

I have a very simple macro that will open a word mail merge doc from excel, however I can't get it to apply the data source.

When I open the word doc through word, it asks me to confirm the data source but when I use the macro it skips that step but also doesn't allocate the chosen data source.

I've searched on the forum and found several macros that will successfully open the document, but none apply the data source.

What am I missing??

Sub Open_Invoice()
 Dim objWord As Object
 
Set objWord = CreateObject("Word.Application")
 objWord.Visible = True
 
objWord.Documents.Open "P:BUSINESS DOCSInvoice.doc"
 
End Sub
Thanks,
Oz

Right, so my forms are complete and I just now need to create a macro-assigned button to execute a mail merge with MS Word.

Having created the required document in MS Word with all the merge fields present, is there a way I can just allow a user to click a button on MS Excel to automatically run the merge into word of the data inserted by them ready to print off?

Will it be a question if building code in VBA? Not too acquainted with that!

I seem to be the only one in the office who understands mail merging and don't want to have to train all the n00bs how to do it!! Rather just have a cheeky button!

Thanks ever so helpful excel gurus

We are working on a project where we generate hundreds of reports, we have to indivdually generate the graphs for all the data and each report contains 2 different graphs from different excel files. We are wanting to speed the process along and we already have the macros set up to generate the graphs themselves, now we just want build on that macro to take the graphs from excel and pastes them into word in their proper location with the proper dimensions. We use mail merge to generate each report to that plugs in a mass quantity of unique data for each report, and we want to combine this mail merge with the macro as well. So instead of having to run the merge then the macro to add the graphs, we would just like to click one time to generate the reports with the correct template and plug in all the data that the mail merge did, and copy the graphs into the report as well as to resize them in their proper place. Any suggestions?

I have an excel tracker that holds the datasource for Word mail merge. I want to tell control from excel though. How can I tell excel to open the word doc (which is already set to read that datasource) and merge? Here is the script that I am using to using the word document so far.
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("D:Unique IDMy Mail Merge.doc")

wdApp.Visible = TrueWhat is the excel command to tell the word document to merge?

Hey,

I am using Excel 2010 and Word 2010 and trying to complete a mail merge in Word. I am having issues with the Excel not bringing over the formatting in Excel over to the mail merge. In past versions, 2003, I have had the option to bring over the Excel data in DDE, which keeps the formatting the same but no such luck so far in 2010. I am looking for a simple solution to pull over the Excel data in its format into the mail merge versus having to manually update the formatting in the mail merge with $#,## etc.

THANKS!

Hey guys (and gals),

I'm really frustrated, I had this working, lost my code, and now I can't figure out where I going wrong trying to get it working again. The code below opens Microsoft Word, uses mail merge, then outputs the Word Doc in PDF, and it names that PDF and saves it to a specified location...

The first time I run the Macro it works perfectly, but the second time I run it it either crashes or Excel freezes on me. If it crashes and I hit debug, and then run the macro again without changing anything- it works perfectly. I'm really stumped here and frustrated.

When I step through it...every time the mail merge runs it is creating a new document (IE "Letter1") and each subsequent run of the macro creates Letter2, Letter3, etc. I don't know why it's doing this. Originally the way it worked it just used the source Doc for mail merge without creating a new document...but I can't seem to get it working again.

Lastly...as you can see in my IF Len(Dir function...if the file exists it creates another file with "-2" on the end of the file name. I would like to make it such that it continues this...(either up to -10 or infitinitely if possible) but my skills are limited so I just have "-1" and "-2". Thank you all for your time!

Sub
Merge()

    Dim wd As Object
    Dim wdocSource As Object
    Dim strWorkbookName As String

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdocSource = wd.Documents.Open("C:Documents and Settingschristopher.klineDesktopWork
FilesProjectsPendingAutomated Bank MemoAIB Bank Memo.doc")

    strWorkbookName = ThisWorkbook.Path & "" & ThisWorkbook.Name
    wdocSource.MailMerge.MainDocumentType = wdFormLetters

    wdocSource.MailMerge.OpenDataSource _
        Name:=strWorkbookName, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
        SQLStatement:="SELECT * FROM `Data$`"

    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        
        With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
        End With
        
        .Execute Pause:=False
    End With

    wdocSource.Close SaveChanges:=True
    wd.Visible = False

    Set wdocSource = Nothing
    Set wd = Nothing

    If Len(Dir("P:LNL Finance 2008BANK MEMOSCreated and Sent" & "" & "BankTEST- " &
Format(Date, "mm-dd-yy") & "-1.pdf")) Then
        ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" &
"" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-2.pdf", ExportFormat:=
_
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    Else
        ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" &
"" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-1.pdf", ExportFormat:=
_
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    End If

    ActiveDocument.Close (False)
    Word.Application.Quit

End Sub



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