MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
123Next
Return Post new threads
View: 940|Reply: 20

Where do I find the mail merge vba code? [SOLVED]

[Copy link]

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
Post on 9-14-2016 18:32:46 | All posts |Read mode
I am working on learning how to create a mail merge from Excel, but to do so one needs a chunk of code that I need to create from different merges to study, and so far can not find where the heck the code is. As an example I extracted the salient block from another post as follows:
appWd.MailMerge.OpenDataSource Name:=C:\My Documents\mydata.csv _
, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:=, PasswordTemplate:=, WritePasswordDocument:=, _
WritePasswordTemplate:=, Revert:=False, Format:=wdOpenFormatAuto, _
Connection:=, SQLStatement:=, SQLStatement1:=I need to learn how to define different DataSources and SQLStatements, which I hope to do by choosing different datasources and creating different searches and reading how this general block gets altered, just like learning from my own recorded macros.
As an added note, I intend to use this for eMail merges, for where it makes a difference, and the MSDN version/explanation(...) of MailMerge.OpenDataSource kind of flies right over my head. No, not kind of. More like absolutely.
Can this be done? If so, where the heck do I look?!? and if not, how can I learn how to accomplish this?
I am old, so yes, I am open to even purchasing and reading books...
Thank-you!




Reply

Props Report

13

Topics

884

Posts

1937

Integral

King

Rank: 6Rank: 6

Integral
1937
Post on 9-14-2016 20:01:58 | All posts
Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-14-2016 20:44:14 | All posts



The following early-binding Excel macro runs a Word mailmerge.
The data source is defined by the string starting with 'strWorkbookName ='. As coded, it uses the workbook the macro is stored in.
The mailmerge main document is defined on the line starting with 'Set wdDoc ='. As coded, it uses a document named MailMergeMainDocument stored in the same folder as the Excel workbook from which the macro is run as the mailmerge main document.
The mailmerge SQL query is defined on the line starting with Const strQry As String ='. As coded, it gets everything from a worksheet named 'Sheet1'.
Sub MailMergeEarly()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
Const strQry As String = SELECT * FROM `Sheet1$`
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(ThisWorkbook.Path  \MailMergeMainDocument.docx, _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:=Provider=Microsoft.ACE.OLEDB.12.0;  _
        User ID=Admin;Data Source=strWorkbookName;  _
        Mode=Read;Extended Properties=HDR=YES;IMEX=1;, _
        SQLStatement:=strQry, SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      '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
  'Display Word and the document
  .Visible = True
End With
End SubIf you actually do the setup of your mailmerge main document as such (so you can do all the correct mergefield insertions), the simplest method of identifying the data source and the connect  query strings it employs so you can add them to the macro above is to use a Word macro like:
Sub Test()
With ActiveDocument.MailMerge
  If .MainDocumentType  wdNotAMergeDocument Then
    MsgBox Mail Merge Data Source Name:  vbCr  .DataSource.Name
    MsgBox Mail Merge Connect String:  vbCr  .DataSource.ConnectString
    MsgBox Mail Merge Query String:  vbCr  .DataSource.QueryString
  Else
    MsgBox Not A Merge Document
  End If
End With
End Sub











Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-14-2016 21:52:34 | All posts



Thank-you Gentlemen!
I am studying both the video and the code.
I find the video very interesting as his method would be great for the single-send merges that I need to incorporate. I now have to figure out if I can augment it to: Pull in some additional fields and incorporate them into the body of the text, andHow to direct the output to an email rather than printing envelopes.[/ol]
Regarding sending to multple recipients using full mailmerge:
...and with all examples I have found using mailmerge they all appear to be outputting to documents; I need to define (in my head) what would re-direct the framework to be oriented to an email and direct output to an email.
Any help on these two methods are greatly appreciated!




Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-14-2016 22:00:38 | All posts



Only a few minor coding changes are needed to send the outputs to email. Of course, had you said up-front that's what you were wanting to automate, I might have provided the appropriate code...
Insert:
, i As Long
after:
wdDoc As Word.Document
Replace:
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocumentwith:
      'Define the mailmerge type
      .MainDocumentType = wdEMail
      'Define the output
      .Destination = wdSendToEmailReplace:
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      .Executewith:
      For i = 1 To .DataSource.RecordCount
        With .DataSource
          .FirstRecord = i
          .LastRecord = i
          .ActiveRecord = i
          If Trim(.DataFields(Last_Name)) =  Then Exit For
        End With
        .MailFormat = wdMailFormatHTML
        .MailSubject = Subject Line
        .MailAddressFieldName = Email field
        .Execute Pause:=False
      Next iNow all you need to do is provide the appropriate:
'MailSubject' details - which could even be in a field in the data source; and
'MailAddressFieldName' reference from the data source.









Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-14-2016 22:49:49 | All posts



I had mistakenly thought that all that i needed was in that block i was asking about, but once again i in retrospect i find i just might have been wrong... Thank-you for coming to my rescue and enlightenment!!




Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-14-2016 23:10:40 | All posts



Note that the additional code I posted has the line:
If Trim(.DataFields(Last_Name)) =  Then Exit For
I should have explained that that code is to provide an exit if, as often happens with Excel workbooks, the used range extends beyond the data. You could use any suitable field name, rather than just one named 'Last_Name'.
Come to think of it, depending on what you're doing you may not even need the loop - just the change in type  destination, plus the parameters for the 'MailFormat', 'MailSubject' and 'MailAddressFieldName'.





[b]
Last edited by macropod; 05-14-2016 at 01:37 AM.







Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-15-2016 00:25:05 | All posts



Since I am now more among the living I belayed studying the code until this morning (still need another cup of coffee, but...). I find this most helpful and am learning much from it!
Yes, I noted that as an exit, and that I would need to match up field names. I also converted all that I understood necessary to late binding (the objects, and learned how to look up the enumerations).
At present I do have a question about the SQL statement - the use of 'sheet1$' in the datasource - In the implementation of what I am working I first was going to filter a table and once happy with the filtered results, try to pump that out for the datasource. When I did the merge from Word step-by-step I could select the sheet by name and then do some matching of fields, as my field names were not on the top row of the sheet, they were in the top row of the table on that sheet (row 2...).
I suspect it will be cleaner to now perform my filtering on the table then by code select the table range and copy only the visible cells in it to a sheet purposed for this that simply gets wiped clean each time it is used, starting with Row 1 for what was the HeaderRowRange, then replace what you have as 'sheet1$' with the name of that sheet. Does this seem like it will work or am I trying to reinvent the wheel?
Next, can you advise if the reference: Provider=Microsoft.ACE.OLEDB.12.0 might run into problems with different versions of Office? I run 2016, but my two friends both run 2007 (which is why I am using late instead of early binding - I have yet to research how I can integrate prior libraries, but it is on my to-do list...).
Also parenthetically wondering about the $ at the end of the term 'Sheet1$' if you could let me know the purpose of that.
Much appreciate the ongoing support; I always try to find examples to learn from out there before asking!




Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 9-15-2016 01:00:12 | All posts



Ignore - mouse bounce or more likely the ExcelForum lurgies...





[b]
Last edited by cytop; 05-14-2016 at 12:28 PM.







Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 9-15-2016 02:00:35 | All posts



SQL can extract data either from Excel Sheets or Named ranges (and probably a defined range like 'A100:F250 - but I've never had a need to use that).
If you are using a sheet as the source then that is identifed to the database driver with the '$' after the sheet name. If a named range is the source the '$' is omitted...
Take it that you don't. The convention is develop on the older, slower machine with the earlier versions. References will always be upgraded automatically if moved to a machine with a later version, but will never be down-graded.





[b]
Last edited by cytop; 05-14-2016 at 12:31 PM.







Reply Support Opposition

Props Report

123Next
Return Post new threads

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-20-2017 00:37 , Processed in 0.202073 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!