Can't merge mail from Excel

Mahmed1 · #1 04-05-2014, 03:53 AM

Hi

Are you saying create a mail merge main document and save it (Do i finish Merge as this would send to new document)?

i.e

Mail Merge Document

Mailings
Letters
Use Existing List
Select Spreadsheet
Insert Merge Fields

Do i press save at this point or finish and merge? as if i press finish and merge and send to new document this will create a new document so then which document am i saving for default TEMPLATE?

And with this Template, it has all the records so i guess my question is , when i run the code you provided, will it append on the Template mail merege record or replace and use the fields and contents of that?

Mahmed1 · #2 04-05-2014, 06:58 AM

I have followed the steps above but when i run the SELECT * part my whole system keeps crashing and the Excel file freezes up

When i record the macro manually from start to finish this is the code i get but when i run it manually and step through. nothing happens

Code:

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\Helal\Desktop\MergeMe.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=C:\Users\Helal\Desktop\MergeMe.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mod" _
        , SQLStatement:="SELECT * FROM `Address`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "FirstName"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "LastName"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Address"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Address"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "PostCode"
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Mahmed1 · #3 04-05-2014, 07:57 AM

This line freezes my computer

.OpenDataSource Name:=strWorkbookName, AddToRecentFiles:=False, _
Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _
& strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'"
.SuppressBlankLines = True

macropod · #4 04-05-2014, 02:27 PM

Quote:

Originally Posted by Mahmed1

I have followed the steps above but when i run the SELECT * part my whole system keeps crashing and the Excel file freezes up

Did you create the mailmerge main document as described? Dis you give it the same name as the code uses (or change the name in the code to suit)? Did you save the document to the same folder as the Excel workbook?

I am also not surprised the code I provided doesn't work, because it's based on the code you originally supplied, which used "SELECT * FROM 'Sheet1'" but your recorded macro has "SELECT * FROM `Address`". You can't expect the macro to work if you tell it to get data from a worksheet named 'Sheet1' (which I suspect doesn't exist in your workbook) when you really want it to get the data from a worksheet named 'Address'.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
mail merge in excel to send pdf forms	heyullama	Excel	7	12-01-2013 03:32 PM
Excel Mail merge	Joren	Word	0	07-09-2013 07:38 AM
mail merge with excel and words	don0712	Mail Merge	1	01-29-2013 06:53 PM
Templates, pictures, Mail Merge and Excel	kckay	Word VBA	1	05-17-2012 08:51 PM
Mail Merge from Excel	Kieroncsmith	Mail Merge	2	02-16-2012 10:35 PM