Mail merge function with selected data

[nareshjoshy]

Dear Sir,

I have copied some code from your site. And now I want to change little bit thing in code.

Sub DoMailMerge()
'Nodte: 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
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open("C:\Users\DEO\Desktop\guarantee\123.doc", _
    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:="SELECT * FROM `Sheet2$`", _
        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 Sub

In this code I want to change two things.

1.

"C:\Users\DEO\Desktop\guarantee\123.doc"

I want to get this value from cell. i.e. I want to get this value from CellB28 of sheet2.
2. I don't want to merge all the data of sheet. I want to get option to enter from start record to end record. i.e. if I want to merge 2-5 record out of 1-10 record. each time I execute my function option should popup.

thank You in advance

 

[Macropod]

Your own code shows how to limit the range of records to process...

All you need do is change:

Dim i As Long, j As Long

to, for example:

Dim i As Long, j As Long, x As Long, y As Long

and change:

    For i = 1 To .DataSource.RecordCount

to:

      x = CLng(InputBox("Enter the First Record # to merge", , 1))
      y = CLng(InputBox("Enter the Last Record # to merge", , .DataSource.RecordCount))
      If y > .DataSource.RecordCount Then y = .DataSource.RecordCount
    For i = x To y

 

[chriswind]

Your own code shows how to limit the range of records to process...

All you need do is change:

Dim i As Long, j As Long

to, for example:

Dim i As Long, j As Long, x As Long, y As Long

and change:

    For i = 1 To .DataSource.RecordCount

to:

      x = CLng(InputBox("Enter the First Record # to merge", , 1))
      y = CLng(InputBox("Enter the Last Record # to merge", , .DataSource.RecordCount))
      If y > .DataSource.RecordCount Then y = .DataSource.RecordCount
    For i = x To y

Thanks macropod. =)