Can't merge mail from Excel

Mahmed1 · #1 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 · #2 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?

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