Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2014, 04:01 PM
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 Can't merge mail from Excel

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

Last edited by macropod; 04-04-2014 at 04:59 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 04-04-2014, 05:10 PM
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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-04-2014, 08:18 PM
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

Thankyou

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

Manythanks
Reply With Quote
  #4  
Old 04-04-2014, 08:37 PM
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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-05-2014, 02:15 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

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
Reply With Quote
  #6  
Old 04-05-2014, 03:02 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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Mahmed1 View Post
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:
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".
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-05-2014, 03:19 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

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?

Last edited by macropod; 04-06-2014 at 02:53 AM. Reason: Deleted quote of most of entire post replied to
Reply With Quote
  #8  
Old 04-05-2014, 03:53 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

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?
Reply With Quote
  #9  
Old 04-05-2014, 06:58 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

I have followed the steps above but when i run the SELECT * part my whole system keeps crashing and the Excel file freezes up

When i record the macro manually from start to finish this is the code i get but when i run it manually and step through. nothing happens

Code:
Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\Helal\Desktop\MergeMe.xlsm", ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Helal\Desktop\MergeMe.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mod" _
        , SQLStatement:="SELECT * FROM `Address`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "FirstName"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "LastName"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Address"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Address"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "PostCode"
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub
Reply With Quote
  #10  
Old 04-05-2014, 07:57 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

This line freezes my computer

.OpenDataSource Name:=strWorkbookName, AddToRecentFiles:=False, _
Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _
& strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'"
.SuppressBlankLines = True
Reply With Quote
  #11  
Old 04-05-2014, 02:17 PM
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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Mahmed1 View Post
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.
Quote:
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.
Quote:
What would do these lines of code do?
.DataSource
.FirstRecord = wdDefaultFirstRecord
.Lastrecord = wdDefaultLastRecord
They tell the merge to use all the available records.
Quote:
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.
Quote:
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).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 04-05-2014, 02:27 PM
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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Mahmed1 View Post
I have followed the steps above but when i run the SELECT * part my whole system keeps crashing and the Excel file freezes up
Did you create the mailmerge main document as described? Dis you give it the same name as the code uses (or change the name in the code to suit)? Did you save the document to the same folder as the Excel workbook?

I am also not surprised the code I provided doesn't work, because it's based on the code you originally supplied, which used "SELECT * FROM 'Sheet1'" but your recorded macro has "SELECT * FROM `Address`". You can't expect the macro to work if you tell it to get data from a worksheet named 'Sheet1' (which I suspect doesn't exist in your workbook) when you really want it to get the data from a worksheet named 'Address'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 04-05-2014, 03:35 PM
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

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
Reply With Quote
  #14  
Old 04-05-2014, 08:43 PM
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: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 04-06-2014, 01:21 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,

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
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
Can't merge mail from Excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:14 PM.


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