Mail merge function with selected data

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Dear Sir,

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

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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,446
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)
 
Last edited:

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,446
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:
Code:
"C:\Users\DEO\Desktop\guarantee\123.doc"
to:
Code:
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
 
Last edited:

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23

ADVERTISEMENT

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,446
In that case, change:
SQLStatement:="SELECT * FROM `Sheet2$`"
to:
SQLStatement:="SELECT * FROM `" & ActiveSheet.Name & "$`"
 

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Thank You Macropod. Thank you very much. This code is very helpful for me.
Thank you once again.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,367
Messages
5,528,271
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top