Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Mail merge without Word

How do I do a mail merge using Excel only? I am an absolute beginner at
Excel. My issue is that I need to make a chart that is specific to each each
entry to merge into the document and can't figure it out in Word. It has been
suggested that using Excel to do the entire thing would be easiest. Where can
I find step-bystep directions on how to do this? Thank you.


Post your answer or comment

comments powered by Disqus
Hi Everyone,

I am trying to create a mail merge so that I can produce documents quickly.

I have a standard template that I use, and change about 8 or 10 details each
time. E.g. Date, Name, Location etc.

This document is not emailed or sent to anyone, it is simple saved as a
Microsoft Word Document.

Can I do a mail merge without recipients or do I need another method?

Either way I could do with a link to how to do either, so that would also be
much appreciated.

Thanks!!
Dave

MAIL MERGE in WORD using EXCEL RESULTS as database

http://www.srands.co.uk/Word&ExcelMailMerge.zip

OPEN ZIP FILE and EXTRACT to chosen FOLDER, however on opening word file, select correct excel file: 'exoftable4.xls' , i.e: Pointing to the correct FOLDER that the EXCEL file is now saved in, as opposed to the FILE DIRECTORY LOCATION when last saved/created.

OPEN CREATED WORD MAIL MERGE DOCUMENT:
YES to open selected data, DATA LINK PROPERTIES, CONNECTION will appear
In 1. Browse all FILE TYPES, and browse to folder, where files are saved to: 'exoftable4.xls', OK.
VIEW, TOOL BARS, MAIL MERGE (Tick if NOT ticked), <<ABC>>, to toggle between FIELDS and ACTUAL DATA.

VIEW MAIL MERGE:
When finished to toggle between CODE and ACTUAL FIELDS, press the MAIL MERGE ICON <<ABC>> 'VIEW MERGED DATA'.
|< Start of records
< Backwards through records
> Forwards through records
|> Finish of records

OR

CREATE YOUR ON MAIL MERGE LETTER from any EXCEL WORKBOOK or ACCESS db:
TOOLS, LETTERS and MAILINGS, MAIL MERGE, LETTERS, NEXT, USE CURRENT DOCUMENT,
NEXT, SELECT RECIPIENTS, USE AN EXISTING LIST, BROWSE, SELECT EXCEL WORKBOOK,
SELECT EXCEL TAB within WORKBOOK, select 1ST COLUMN, NON BLANKS, SELECT ALL, OK.

VIEW, TOOL BARS, MAIL MERGE (Tick if NOT ticked), INSERT MERGE FIELDS (A4 icon), DATAFIELDS, then select your chosen EXCEL HEADINGS, 1 field at a time!

MAIL MERGE RESULTS:
Press the MERGE TO NEW DOCUMENTS icon or MERGE TO PRINT.

QUESTION 1). Is there a FORMULA and/or VB CODE way for MAIL MERGING to WORD from EXCEL? Hence a word document would be created, however I guess this would cause some issues:
~ If started in EXCEL instead of WORD then LETTER without elabourate HEADER, FOOTER, logos, unless all paper in printer is already LETTERHEADED.
~ Alternatively prompt EXCEL to use a designated TEMPLATE

http://www.srands.co.uk/Word&ExcelMailMerge.zip

Hello all.

Ive seen this question posted everywhere and so far i have seen no working solution.

I have a spreadsheet and im would like to mail merge it at the click of a button. I am fully aware of how to create a mail merge from word to excel but not from excel to word.

The main reason for this is user friendliness. I'd rather the user press the button and presto, rather than open word --> make doc --> mail merge --> select fields ...

I'd be greatfull for a solution

Thanks B

I have a spreadsheet that contains all loans with problems for a certain day.

I would like to create a word doc and have excel output each loan into a seperate mailmerge addressed to the branch manager associated with the Loan (Column H) and if possible, combine all loans under that branch manager in a single email.

I am looking to use a mail merge within word due to six legal paragraphs that must be included within each document.

Any help would be great.

I'm trying to do a Mail merge in word to have Photos match with a name,
however I cant seem to get the photo to happen. i can get the hyperlink to
work, but nothing else. any advise, l8ike maybe being able to have a picture
in the cell

Hi all,
I am not sure where to post this but I will start here.
I have an Access table created from several queries that supplies data for a mail merge in word to send out specific performance reports.
Also in the Acess database I produce a query that supplies Excel with data from which I run a VBA code to produce several charts that are named with the unique identifier for each person getting the reports.
I now want to add the appropriate chart to the mail merge letter for each person.
I once had the code/syntax to do this in Office 95 but it didn't work in the newer versions and I can't locate code that will
I assume it will be a VDO/OBE system or can I import into the Access table and use that
Thanks for any help
Richard

How do I embed a picture in excel so that it will mail merge into word?

I am having difficulty in mail merging(MS Word 2000). Can anyone help me.

I need to do a mail merge using simple letter form for reminder of Outstanding Payment to Customer.

I want to add records of Invoice details i.e. Inv.No., Inv.Date & Inv.Amt. of each Customer. Each customer having 4 to 8 Invoice. So how can I manipulate more invoice record in one customer form.

I am attaching herewith both outstand_julsep06.doc file for your understanding.

Any help will be most greatfully received.

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!!

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!

Hello all,

I have an excel database for a sports league that Id like to use with Word for a mail merge. In my database, I have several families that have more than one child registered. I would only need to create one address label for each family, but I need to know how many kids are registered in that family so I can send them the correct amount of forms. Each child needs their own line with health card # and other specific information.

Any thoughts on how I can work this without physically going through it and counting how many in each family, and not creating duplicate mailing labels?

Thanks in advance!

Fluffy

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!

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

I'm trying to make labels and envelopes but I want Excel as my data source to
be linked in the Word mail merge in case of changes to the data source and
then it will automatically change in the mail merge. I can do that part but
when I ready to share this information with other people within the
organization, it states it can't find the data source that I've used. Please
help, if would save me a lot of time. Thanks.

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

Hi

I can't figure this one out. I have a jpeg image in the My Pictures folder. I have a mail merge source file (excel file) and a word document I'm trying to insert the picture into using mail merge. The example on the msdn website doesn't work as this is what I'm doing below.
I've tried to refresh the field by hitting F9 but still nothing. It just comes up with a blank picture frame in my word document.

In the excel file (source file) in the "Pictures" column I put the below.



In the word document field where I want to insert the picture I put the below.

I've tried with and without the D at the end but it makes no difference.
I have also tried with and without quotation marks around "Pictures". Still no difference.

Any clues as to how I can get this to work?

Thanks so much.

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


Hello all,

I am trying to macro the use of an Excel list and Mail Merge through Word. I am wondering if you can set a macro to the entire Mail Merge process through Excel.

For example, the code below is what I currently have to save and close Excel and open the Word document. I want to take that a step further and point the merge at Sheet2 of the Excel document, start the merge process, then close Word.

Is this possible?

Thank you so much!


	VB:
	
 Open_Word_Document() 
     'Opens a Word Document from Excel
    Dim objWord As Object 
    Set objWord = CreateObject("Word.Application") 
    objWord.Visible = True 
     'Change the directory path and file name to the location
     'of your document
    objWord.Documents.Open "File path here" 
    ActiveWorkbook.Save 
    Application.Quit 
End Sub 

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


Hi Friends,

I use Excel Spreadsheet for mail merge in Word. My Excel book is password protected. Everything goes fine if i am using this excel spreadsheet and do mail merge with ms word. But problem starts if i open another workbook either in the same application or by opening a new excel application. If two excel applications or more than one workbook is opened and i run the mail merge from excel, a password screen pops up for the same excel workbook from where i am running this mail merge.

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

Hi, hopefully someone can help!

I'm using the following code to start a mail merge in excel. I'm sure it used to work, but now when I try and run the macro I get the message "Run Time Error 4248, Command is not available because no document is available". I can see that the word document opens - does anyone have any clues as to what's going on? Any help would be appreciated...


	VB:
	
 MMerge() 
     '
     ' MMerge Macro
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = False 
    Set WordApp = New Word.Application 
     
    WordApp.Visible = True 
    WordApp.Documents.Open Filename:="J:OPGPendinG CeaseCPS Pending CeaseCPS Cease Letter" 
     
    With WordApp 
         
        ActiveDocument.mailmerge.OpenDataSource Name:="J:OPGPC dataAddLook.xls" _ 
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ 
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ 
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ 
        Format:=wdOpenFormatAuto, Connection:= _ 
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=J:OPGPC dataAddLook.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLEDB:Engine Type=" _ 
        , SQLStatement:="SELECT * FROM `Data$`", SQLStatement1:="", SubType:= _ 
        wdMergeSubTypeAccess 
        With ActiveDocument.mailmerge 
            .Destination = wdSendToNewDocument 
            .SuppressBlankLines = True 
            With .DataSource 
                .FirstRecord = wdDefaultFirstRecord 
                .LastRecord = wdDefaultLastRecord 
            End With 
            .Execute Pause:=False 
        End With 
    End With 
     '
End Sub 

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


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.

I have a mail merge in word that is linked to an excel database. I also have two dynamic charts per mail merge recipient. I know that you chart mail merge a chart so to combat this issue i have added a column to the data table that has the individual chart name. I'm trying to use this merge field in a link but I'm not sure how. Something like {LINK Excel.Chart.8 "File Path" }. Is there a way to write a link rather then using the Paste Special Link feature?

Hi All,

I have the following macro which performs a mail merge from excel to word.

Code:
Sub mergedoc()
Dim coord As Integer
Dim appWd As Word.Application
Dim WdDoc As Word.Document

coord = Range("A1").End(xlDown).Row
ActiveSheet.PageSetup.PrintArea = "A1:AN" & coord

Set appWd = CreateObject("Word.Application")

With appWd
.Visible = True
Set WrdDoc = appWd.Documents.Open(Filename:="C:test data.doc", ReadOnly:=True)
'Set WrdDoc = wrdApp.Documents.Open(Filename:=Path, ReadOnly:=False)
WrdDoc.Mailmerge.OpenDataSource Name:="C:merge data.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Print_Area", SQLStatement:="", _
        SQLStatement1:=""
    With WrdDoc.Mailmerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With

End With
Set WdDoc = Nothing
Set appWd = Nothing
 End Sub
This works exactly how i want it to in terms of it acually performs a mail merge, however when it reaches the stage whereby it opens the data source, it attempts to re-open the excel file i am using and takes forever.

If i do this myself in word its then its a fairly quick process and also word it doesn't attempt to re-open the exel file.

Does anybody have any suggestions as to how i can rectify this?

Thanks.


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