Can't merge mail from Excel

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