Can't merge mail from Excel

Mahmed1 · #1 04-04-2014, 04:01 PM

Hi,

What is wrong with code :-(

Nothing is happening with this code

All i am trying to do is from my excel worksheet (Active worksheet)
Mail merge Sheet 1 /Letters/Address block/Finish merge and rather than send to new document, send to document i am opening.

Is there any bits of code i can take out

I also have a Named range called Address to mail merge if that helps but if not then the range is on sheet1

Here is my code

Code:

Sub RunMerge() 
    Dim strWorkbookName As String 
    Dim wdapp As Word.Application 
    Set wdapp = New Word.Application 
    Dim wddoc As Word.Document 
    Set wddoc = wdapp.Documents.Add 
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name 
    With wddoc.MailMerge 
        .MainDocumentType = wdFormLetters 
        .OpenDataSource _ 
        Name:=strWorkbookName, _ 
        AddToRecentFiles:=False, _ 
        Revert:=False, _ 
        Format:=wdOpenFormatAuto, _ 
        Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _ 
        SQLStatement:="SELECT * FROM 'Sheet1'" 
        .Destination = wdSendToNewDocument 
        .SuppressBlankLines = True 
        With .DataSource 
            .FirstRecord = wdDefaultFirstRecord 
            .LastRecord = wdDefaultLastRecord 
             'Add address block field and update
        End With 
    End With 
    wd.Visible = True 
End Sub

macropod · #2 04-04-2014, 05:10 PM

Hi Mahmed1,

As coded, you macro won't do anything because it doesn't:
a) reference an existing document with the mergefields - instead, it creates a new, empty document ;
b) add any mergefields or other data to the new, empty document; and
c) Execute the merge.

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab at the bottom of this screen.

Mahmed1 · #3 04-04-2014, 08:18 PM

Thankyou

Any chance you could help me sir as i amabitstuck nowinto how to encorporate what you have said into mycode

Manythanks

macropod · #4 04-04-2014, 08:37 PM

What parts do you need help with?

Do you already have a document that you want to use for the mailmerge? You should have, since that's far easier to work with and maintain than trying to do everything in code. Then, instead of using 'Set wddoc = wdapp.Documents.Add' you would use 'Set wddoc = wdapp.Documents.Open("C:\MyDocuments\Document.docx ")', where "C:\MyDocuments\Document.docx" is the full name & path to the document.

As for item c, surely you can add .Execute to the code...

Mahmed1 · #5 04-05-2014, 02:15 AM

Firstly thank you

The problem im having is getting to merge my fields and use the address block, add a greeting block and then execute the code.

I would like it send to a new document and then save that copy that was send to a new document and then send that new saved copy to a printer to print.

I only created a new document because ultimately i am creating a new mail merge each time so i thought i could add a document and do it in that document but i can if its easier have a
Blank document to open and use each time as a template to mail merge in

The code i have is all i have managed to get to with.

I am not fully understanding what each part of the code does as i had some help with google to put this together.

If you could help us with the code abd just a comment to each line so i could understand if that would be awesome

I am creating a letter/envelope and labels as i need to do all 3 but id imagine it would be a bit of tweaking for all 3

Ps thank you so much once again

macropod · #6 04-05-2014, 03:02 AM

Quote:

Originally Posted by Mahmed1

The problem im having is getting to merge my fields and use the address block, add a greeting block and then execute the code.

That is why you should use a mailmerge main document that already has all the fields and content set up how you want them. You should create that document exactly as you would do for a mailmerge without macros. Regarding the Address Block, that often does not give good results and you may do much better to insert the mergefields you want to use, exactly where you want them to be.

Once you have the mailmerge main document set up correctly, you can then run the following code:

Code:

Sub RunMerge()
    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    With wdapp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        'Open the mailmerge main document
        Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Mail Merge Main Document.docx")
        With wddoc
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdFormLetters
                'Connect to the data source
                .OpenDataSource Name:=strWorkbookName, AddToRecentFiles:=False, _
                Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _
                & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'"
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                End With
                'Define the output
                .Destination = wdSendToNewDocument
                'Excecute the merge
                .Execute
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = wdAlertsAll
        'Print the output document
        .ActiveDocument.PrintOut
        'Display Word and the document
        .Visible = True
    End With
End Sub

As coded, the macro now looks in the same folder as the workbook, with a mailmerge main document in the .docx format and named "Mail Merge Main Document".

Mahmed1 · #7 04-05-2014, 03:19 AM

Hi thank you

Are you saying have a default Mail merge document already set up with all the fields and records ?

Ps what would the difference be in not having a template already set up?

What would do these lines of code do?
.DataSource
.FirstRecord = wdDefaultFirstRecord
.Lastrecord = wdDefaultLastRecord

And with the select statement, i know we are selecting everthing from excel sheet but where is the code for connecting to a provider.

Can this be easily amended so i can use a label or envelope?

Mahmed1 · #8 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 · #9 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 · #10 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 · #11 04-05-2014, 02:17 PM

Quote:

Originally Posted by Mahmed1

Are you saying have a default Mail merge document already set up with all the fields and records ?

Yes, I've said this twice already.

Quote:

Ps what would the difference be in not having a template already set up?

It is not a template, it is a document. The difference in having it set up is that you don't then have to try to construct everything with code. The setup and maintenance are much easier for you that way.

Quote:

What would do these lines of code do?
.DataSource
.FirstRecord = wdDefaultFirstRecord
.Lastrecord = wdDefaultLastRecord

They tell the merge to use all the available records.

Quote:

And with the select statement, i know we are selecting everthing from excel sheet but where is the code for connecting to a provider.

That's what ".OpenDataSource Name:=strWorkbookName" does.

Quote:

Can this be easily amended so i can use a label or envelope?

Yes, by changing ".MainDocumentType = wdFormLetters".

Instead of asking questions all the time and adding in code that you don't understand, you should spend a little time: (a) learning how mailmerge works when it's not using vba; and (b) seeing what the VBA help tells you about each parameter. If you don't take the time for (a) you'll never master (b).

macropod · #12 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'.

Mahmed1 · #13 04-05-2014, 03:35 PM

Hi

I have changed sheet name to sheet1 and added a mail merge document without clicking mail merge finish
I have also saved both documents in same folder
The freeze comes and after ages it says still connecting OLE message and freezes

macropod · #14 04-05-2014, 08:43 PM

I think you will need to provide a copy of the mailmerge document you're using, plus the workbook with the code you're using and some sample data. Without actually seeing those, it can be difficult for anyone to diagnose the issue. You can attach files to a post (delete anything sensitive) via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Mahmed1 · #15 04-06-2014, 01:21 AM

Hi,

Firstly i want to thank you again for helping me

Please see attached files (Word doc and excel file with code)

Both these files are in the same folder

Mail Merge Main Document.docx

MergeMe.xlsm

and now when ever i open up the Word document, i get this message "Opening the document will run the SQL command, SELECT * FROM ADDRESS, do you want to continue. I dont have sheet called Address. Just a named range for the data called address which i can delete.

Should this message keep popping up and have i set up the mail merge doc properly?

Again many many thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to 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