Can't merge mail from Excel

macropod · #16 04-06-2014, 02:41 AM

Try:

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", _
            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 · #17 04-06-2014, 02:51 AM

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 · #18 04-06-2014, 03:04 AM

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.

Mahmed1 · #19 04-06-2014, 03:18 AM

Sorry

I am new to this and apologise for posting incorrectly

Thank you for all your help

I didn't realise the ' shape difference because normally in an SQL string it is '

I guess I needed to add the extra info on the provider bit to establish the connection.

The letter works fine thank you.

I have created 3 documents and have a msgbox to prompt me with which type I'd like and then have added this to the path string to open and had this stored in a variable and have used that variable to change the main document type.

Would I need to add anymore code depending on what type of format I select?

Ps what is the dollar sign in sheet$

Many thank sir

I can see you put a lot of effort on this forum and I especially appreciate all your help

macropod · #20 04-06-2014, 03:32 AM

Unless you use three different 'RunMerge' macros (e.g. RunMerge_Letter, RunMerge_Label, RunMerge_Envelope) you would need whatever code you use to choose the merge type to change these two lines:
Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Mail Merge Main Document.docx")
and:
.MainDocumentType = wdFormLetters

The first line tells the macro which document to use (each merge requires a different document) and the second line tells the macro what kind of merge to do with that document.

Mahmed1 · #21 04-06-2014, 03:52 AM

Hi Thank You

Im aware that the document to open and main document type will have to change. i wernt sure if the syntax for each type was different so my question is do i only have to change the highlighted parts to suit?

Would that be the only change or are the syntaxes or code need adding if i change from letter to envelope to label etc.

And can i add 1 line of code to tick the Dont add space between paragraphs

Code:

Sub RunMerge()
    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As Word.Application
    Set wdapp = 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
            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 · #22 04-06-2014, 04:46 AM

Thank you sir

Would you recommend to use 3 different macros for each type
or
Change the file to open and main document based on selection

and what does the sheet$ do? $ And normally the SELECT statements requires '
I did not realise that with mail merge that's not actually a '.
*SubType:=wdMergeSubTypeAccess? I guess the linked source to false does not prompt me with that message box ?

1 last bit before I completely move away from mail merge :-) what bit of code can I have to Tick the line after paragraph?

How did you know which provider to use for sql ? Is this based on experience?

macropod · #23 04-06-2014, 05:10 AM

Yes, those are the parts of the code that would need changing. For example:

Code:

Sub RunMerge()
    Dim strWorkbookName As String, Rslt
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    Rslt = InputBox("Please choose a merge type:" & vbCr & _
      "1. Letter merge" & vbCr & _
      "2. Label merge" & vbCr & _
      "3. Envelope merge")
    If Rslt = vbCancel Then Exit Sub
    Rslt = Trim(Rslt)
    If Not IsNumeric(Rslt) Then Exit Sub
    If (Rslt > 3) Or (Rslt < 1) Then Exit Sub
    With wdapp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        'Open the mailmerge main document
        Select Case Rslt
          Case 1
            Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Letter Mail Merge Main Document.docx")
          Case 2
            Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Label Mail Merge Main Document.docx")
          Case 3
            Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Envelope Mail Merge Main Document.docx")
        End Select
        With wddoc
            With .MailMerge
                'Define the mailmerge type
                Select Case Rslt
                  Case 1
                    .MainDocumentType = wdFormLetters
                  Case 2
                    .MainDocumentType = wdMailingLabels
                  Case 3
                    .MainDocumentType = wdEnvelopes
                End Select
                '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

Note the change in the naming of the mailmerge main documents, one for each type.

The $ on the end of 'Sheet1' is necessary to tell the SQL statement that its using a worksheet. If you want to use a worksheet named Address, use Address$.

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

Mahmed1 · #24 04-06-2014, 07:23 AM

Thank you soooooooooooo much

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