Ok, no one replied and I kept trying . Now I got the solution. I will put it here so that others can get the idea when they encounter the same situation.
So here is VB.NET code.
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
Dim excelFilePath As String = DirectCast(str_excelFilePath, String)
Dim wordApp As New Microsoft.Office.Interop.Word.Application()
Dim wordFilePath As String = DirectCast(str_wordFilePath, String)
Dim pdfFilePath As String = DirectCast(str_pdfFilePath, String)
Dim objFilePath As Object = DirectCast(str_wordFilePath, Object)
Dim excelWorkbook As Microsoft.Office.Interop.excel.Workbook = excelApp.Workbooks.Open(excelFilePath)
Dim excelWorksheet As Microsoft.Office.Interop.excel.Worksheet = DirectCast(excelWorkbook.Worksheets(“Before_Applying_Business_Rules”), Worksheet)
Dim wordDoc As Microsoft.Office.Interop.word.Document = DirectCast(wordApp.Documents.Open(objFilePath), Microsoft.Office.Interop.word.Document)
’ Perform mail merge
wordDoc.MailMerge.MainDocumentType = WdMailMergeMainDocType.wdFormLetters
wordDoc.MailMerge.OpenDataSource(Name:=excelWorkbook.FullName, AddToRecentFiles:=False, Revert:=False, Format:=WdOpenFormat.wdOpenFormatAuto, Connection:=“Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=” & excelWorkbook.FullName & “;Mode=Read;Extended Properties=”“HDR=YES;IMEX=1"”;", SQLStatement:=“SELECT * FROM [” & excelWorksheet.Name & “$]”)
wordDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
wordDoc.MailMerge.Execute()
’ Save the merged document as PDF
Dim mergedDoc As Document = wordApp.ActiveDocument
mergedDoc.ExportAsFixedFormat(pdfFilePath, WdExportFormat.wdExportFormatPDF)
’ Close the document and the applications
mergedDoc.Close(SaveChanges:=False)
wordApp.Quit()
excelWorkbook.Close(SaveChanges:=False)
excelApp.Quit()
’ Release the objects from memory
System.Runtime.InteropServices.Marshal.ReleaseComObject(mergedDoc)
System.Runtime.InteropServices.Marshal.ReleaseComObject(wordDoc)
System.Runtime.InteropServices.Marshal.ReleaseComObject(wordApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
I passed excel file path, word file path and output file path as parameters to invoke code.
Good Luck, everyone