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]

1. Change:
"C:\Users\DEO\Desktop\guarantee\123.doc"
to:
Sheets("Sheet2").Range("B28").Text

2. Change:
.FirstRecord = wdDefaultFirstRecord
to:
.FirstRecord = InputBox("Enter the First Record # to merge", , 1)
and change:
.LastRecord = wdDefaultLastRecord
to:
.LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount)

 

[nareshjoshy]

Thank you for reply.

1. Change:
"C:\Users\DEO\Desktop\guarantee\123.doc"
to:
Sheets("Sheet2").Range("B28").Text

while using this change to code error occur as object variable or with block variable not set.

I want to change little bit else:

I want to to join in two parts in open source path. 1st part should be file location where my excel file is and 2nd part is my excel cell.
i.e. My file location as "File Location & "" & Sheets("Sheet2").Range("B28").Text"


and

In my date there are Amount column. But after mail merge this amount shows as text without comma sepration nor dot. i.e 5,000.00 shows as 5000 but I want to this value as 5,000.00.

 

[Macropod]

while using this change to code error occur as object variable or with block variable not set.

That's undoubtedly due to the cell not containing the document's full path & name - including the extension.

I want to change little bit else:

I want to to join in two parts in open source path. 1st part should be file location where my excel file is and 2nd part is my excel cell.
i.e. My file location as "File Location & "" & Sheets("Sheet2").Range("B28").Text"

In that case, change:

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

to:

File Location & "\" & Sheets("Sheet2").Range("B28").Text

In my date there are Amount column. But after mail merge this amount shows as text without comma sepration nor dot. i.e 5,000.00 shows as 5000 but I want to this value as 5,000.00.

You need to add the appropriate numeric picture switch to the mergefield in your mailmerge main document. See Mailmerge Number & Currency Formatting in the Mailmerge Tips and Tricks threads at:
http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

 

[nareshjoshy]

Thank You Macropod, I used this code and working well. But I need one more change in code.

SQLStatement:="SELECT * FROM `Sheet2$`"

I want to change `Sheet2$` automatic as active sheet.

I mean to say, If I'm working Sheet1 then it automatic change to 'Sheet1$` and if I'm working Sheet2 then it automatic change to `Sheet2$`.

Thank You.

 

[Macropod]

In that case, change:
SQLStatement:="SELECT * FROM `Sheet2$`"
to:
SQLStatement:="SELECT * FROM `" & ActiveSheet.Name & "$`"

 

[nareshjoshy]

Thank You Macropod. Thank you very much. This code is very helpful for me.
Thank you once again.

 

[chriswind]

1. Change:
"C:\Users\DEO\Desktop\guarantee\123.doc"
to:
Sheets("Sheet2").Range("B28").Text

2. Change:
.FirstRecord = wdDefaultFirstRecord
to:
.FirstRecord = InputBox("Enter the First Record # to merge", , 1)
and change:
.LastRecord = wdDefaultLastRecord
to:
.LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount)

Hi Macropod,
I am trying to merge also select record in the word by using your suggestion 2.
however I still only able to save 1 current record, even I type the record number.
Any help will be much appreciate? my code as below
Code:
Const foldersave as string ="\\(my save destination folder)\\"
Sub DoMailMerge()
With ActiveDoc
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = InputBox("Enter the First Record # to merge", , 1)
.LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount)
End With
With ActiveDocument
SaveAs FileName:=foldersave & .mailmerge.datasource.datafield("NAME")).Value & ".pdf", FileFormat:=wdFormatPDF
End With
End Sub

 

[Macropod]

If you want each record saved to a new file, see Run a Mailmerge from Excel, Sending the Output to Individual Files at: Mailmerge Tips & Tricks

 

[chriswind]

If you want each record saved to a new file, see Run a Mailmerge from Excel, Sending the Output to Individual Files at: Mailmerge Tips & Tricks

Hi Macropod,
Thanks for your reply, but I am not looking to save each record as my excel have a list of record (as of now 500).
Then it will be adding more, sometime only 1 , sometime 3-10 new entry. Then I like to mailmerge the new entry only (maybe 1 or maybe 2-3 or more).

Any possible way to make it? Thanks a lot