Microsoft Office Forums Can't merge mail from Excel

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 04-06-2014, 02:41 AM
macropod's Avatar
macropod macropod is offline Can't merge mail from Excel Windows 7 32bit Can't merge mail from Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,524
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #17  
Old 04-06-2014, 02:51 AM
Mahmed1 Mahmed1 is offline Can't merge mail from Excel Windows 7 64bit Can't merge mail from Excel Office 2010 32bit
Novice
Can't merge mail from Excel
 
Join Date: Apr 2014
Posts: 17
Mahmed1 is on a distinguished road
Default

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

Last edited by macropod; 04-06-2014 at 02:53 AM. Reason: Deleted quote of entire post replied to
Reply With Quote
  #18  
Old 04-06-2014, 03:04 AM
macropod's Avatar
macropod macropod is offline Can't merge mail from Excel Windows 7 32bit Can't merge mail from Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,524
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #19  
Old 04-06-2014, 03:18 AM
Mahmed1 Mahmed1 is offline Can't merge mail from Excel Windows 7 64bit Can't merge mail from Excel Office 2010 32bit
Novice
Can't merge mail from Excel
 
Join Date: Apr 2014
Posts: 17
Mahmed1 is on a distinguished road
Default

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
Reply With Quote
  #20  
Old 04-06-2014, 03:32 AM
macropod's Avatar
macropod macropod is offline Can't merge mail from Excel Windows 7 32bit Can't merge mail from Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,524
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #21  
Old 04-06-2014, 03:52 AM
Mahmed1 Mahmed1 is offline Can't merge mail from Excel Windows 7 64bit Can't merge mail from Excel Office 2010 32bit
Novice
Can't merge mail from Excel
 
Join Date: Apr 2014
Posts: 17
Mahmed1 is on a distinguished road
Default

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
Reply With Quote
  #22  
Old 04-06-2014, 04:46 AM
Mahmed1 Mahmed1 is offline Can't merge mail from Excel Windows 7 64bit Can't merge mail from Excel Office 2010 32bit
Novice
Can't merge mail from Excel
 
Join Date: Apr 2014
Posts: 17
Mahmed1 is on a distinguished road
Default

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?
Reply With Quote
  #23  
Old 04-06-2014, 05:10 AM
macropod's Avatar
macropod macropod is offline Can't merge mail from Excel Windows 7 32bit Can't merge mail from Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,524
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #24  
Old 04-06-2014, 07:23 AM
Mahmed1 Mahmed1 is offline Can't merge mail from Excel Windows 7 64bit Can't merge mail from Excel Office 2010 32bit
Novice
Can't merge mail from Excel
 
Join Date: Apr 2014
Posts: 17
Mahmed1 is on a distinguished road
Default

Thank you soooooooooooo much
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't merge mail from Excel 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
Can't merge mail from Excel Templates, pictures, Mail Merge and Excel kckay Word VBA 1 05-17-2012 08:51 PM
Can't merge mail from Excel Mail Merge from Excel Kieroncsmith Mail Merge 2 02-16-2012 10:35 PM


All times are GMT -7. The time now is 01:09 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft