title: Automating Mail Merge using Excel VBA
body: <p>I created a macro in Excel where I can mail-merge data from Excel into Word Letter Template and save the individual files in the folder.</p>
  
  <p>I have Employee data in Excel and I can generate any Employee letter using that Data and can save the individual Employee letter as per the Employee name.</p>
  
  <p>I have run mail-merge automatically and save individual files as per the Employee name. And every time it runs the file for one person it will give the status as Letter Already Generate so that it wont duplicate any Employee records.</p>
  
  <p>The problem is the output in all the merged files the output is same as the first row. Example: if my Excel has 5 Employee details I am able to save the 5 individual merged files on each employee name, however the merged data if of the first employee who is in Row 2.</p>
  
  <p>My rows have the below data:</p>
  
  <blockquote>
    <p>Row A: has S.No.<br>
    Row B: has Empl Name<br>
    Row C: has Processing Date<br>
    Row D: has Address<br>
    Row E: Firstname<br>
    Row F: Business Title<br>
    Row G: Shows the status (if the letter is generated it shows "Letter Generated Already" after running the macro or it shows blank if it is new record entered.</p>
  </blockquote>
  
  <p>Also how can I save the output (merged file) also in PDF other than DOC file so the merged files will be in two formats one in DOC and the other one in PDF formats?</p>
  
  <pre><code>Sub MergeMe()
  
  Dim bCreatedWordInstance As Boolean
  Dim objWord As Word.Application
  Dim objMMMD As Word.Document
  Dim EmployeeName As String
  Dim cDir As String
  Dim r As Long
  Dim ThisFileName As String
  lastrow = Sheets("Data").Range("A" &amp; Rows.Count).End(xlUp).Row
  r = 2
  For r = 2 To lastrow
  If Cells(r, 7).Value = "Letter Generated Already" Then GoTo nextrow
  EmployeeName = Sheets("Data").Cells(r, 2).Value
  
  ' Setup filenames
  Const WTempName = "letter.docx" 'This is the 07/10 Word Templates name,  Change as req'd
  Dim NewFileName As String
  NewFileName = "Offer Letter - " &amp; EmployeeName &amp; ".docx" 'This is the New 07/10 Word Documents File Name, Change as req'd"
  
  ' Setup directories
  cDir = ActiveWorkbook.path + "\" 'Change if appropriate
  ThisFileName = ThisWorkbook.Name
  
  On Error Resume Next
  
  ' Create a Word Application instance
  bCreatedWordInstance = False
  Set objWord = GetObject(, "Word.Application")
  
  If objWord Is Nothing Then
    Err.Clear
    Set objWord = CreateObject("Word.Application")
    bCreatedWordInstance = True
  End If
  
  If objWord Is Nothing Then
  MsgBox "Could not start Word"
  Err.Clear
  On Error GoTo 0
  Exit Sub
  End If
  
  ' Let Word trap the errors
  On Error GoTo 0
  
  ' Set to True if you want to see the Word Doc flash past during construction
  objWord.Visible = False
  
  'Open Word Template
  Set objMMMD = objWord.Documents.Open(cDir + WTempName)
  objMMMD.Activate
  
  'Merge the data
  With objMMMD
  .MailMerge.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT *  FROM `Data$`"   ' Set this as required
  
  With objMMMD.MailMerge  'With ActiveDocument.MailMerge
  .Destination = wdSendToNewDocument
  .SuppressBlankLines = True
  With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
  End With
  .Execute Pause:=False
  End With
  End With
  
  ' Save new file
  objWord.ActiveDocument.SaveAs cDir + NewFileName
  
  ' Close the Mail Merge Main Document
  objMMMD.Close savechanges:=wdDoNotSaveChanges
  Set objMMMD = Nothing
  
  ' Close the New Mail Merged Document
  If bCreatedWordInstance Then
  objWord.Quit
  End If
  
  0:
  Set objWord = Nothing
  Cells(r, 7).Value = "Letter Generated Already"
  nextrow:
  
  Next r
  
  End Sub
  </code></pre>