Can't merge mail from Excel

[Mahmed1]

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

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]

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]

Thankyou

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

Manythanks

[macropod]

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]

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]

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:

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]

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]

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?

[macropod]

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.

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.

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

They tell the merge to use all the available records.

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.

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).

[Mahmed1]

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]

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]

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

[macropod]

Try:

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", _
            ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
        With wddoc
            .ActiveWindow.View.Type = wdNormalView
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdFormLetters
                'Connect to the data source
                .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
                  AddToRecentFiles:=False, LinkToSource:=False, _
                  Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "User ID=Admin;Data Source=strWorkbookName;" & _
                  "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                  SQLStatement:="SELECT * FROM `Sheet1$`", _
                  SubType:=wdMergeSubTypeAccess
                .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

[Mahmed1]

Thank you

I will give that a go

Just out of curiosity, what was causing that error for freezing up and what changes made to the code should prevent that?

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?

Ps have I set up the word document correctly and say I wanted an ENVELOPE OR LABEL structure, would you advise to set up another 2 documents with the correct layout (so 3 template documents) if you like
Letter
Envelope
Label

And then in the code have a msgbox to say what format I would like and then based on that, change the correct document to open and change format to based on which 1 I selected. Is that the best way to go about it?

Again many many thanks

[macropod]

The issue was mainly to do with your SQL statement, especially using 'Sheet1' instead of `Sheet1` (note the shape of the quote marks). You'll see that I've also added some more code to the overall OpenDataSource statement, which now spans 7 lines, to ensure all relevant parameters are used.

As for your document, simply answer 'no' to the prompt, then save.

You would definitely need a different document for labels and envelopes. The code would also need modification if it were to handle each kind, since it would need to call those different documents and tell Word what kind of merge to execute. That entails rather more than just a message box.

PS: Please don't keep quoting entire previous posts in your replies. If you need to quote something, quote only that part; otherwise don't quote anything - it just wastes screen space and board storage.