Can't merge mail from Excel

Mahmed1 · #1 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 · #2 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 · #3 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 · #4 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 · #5 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 · #6 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 · #7 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'.

macropod · #8 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).

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