![]() ![]() If Check = 0 Then NotFound = NotFound & vbCrLf & Cell Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) = Now() Range("RecipientEmail") = Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) Range("RecipientCompanyAddress") = Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) Range("RecipientCompany") = Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) Range("RecipientTitle") = Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) Range("RecipientName") = Source.Cells(TblCell.Row, Source.Range("MailMergeTable,]").Column) ![]() ![]() Set WdDoc = WdApp.Documents(Replace(Fname, FilePath, ""))įor Each TblCell In Source.ListObjects("MailMergeTable").ListColumns("Recipient Name").DataBodyRange Set WdApp = CreateObject("Word.Application") Set WdApp = GetObject(, "Word.Application") ![]() 'open dialog window to select word document:įname = Application.GetOpenFilename("Word files (*.do*),*.do*",, "Select Letter")įilePath = Left(Fname, InStrRev(Fname, "\")) If Len(Dir(ThisWorkbook.Path & "\" & DestFolder, vbDirectory)) = 0 Then MkDir ThisWorkbook.Path & "\" & DestFolder Set Source = Worksheets("Create Letters") Here is the code: Option Explicitĭim Fname As String, BMRange As Object, Cell As Range, DocNotOpen As Label, DocIsOpen As Label, FilePath As String, NewFileName As Stringĭim WdApp As Object, WdDoc As Object, Nm As String, ExitLine As Label, DestFolder As String, j As Integer, TblCell As Rangeĭim Source As Worksheet, NotFound As String, Check As Integer, ShowOnce As Boolean There are 2 version of the code for creating the letters, one is for the Worksheet_Change Event, that will create letters only for the selected rows, and the code below, which will create letters for all visible rows from table if there are no filters applied to table columns, a letter for each row will be created. Create Duplicate bookmarks for any fields you need.Īnd the most important thing: make sure that the bookmarks are visible, this way you will be able to see those square brackets that identifies a bookmark, to avoid deleting them when editing the document… From Word Options, Advanced Tab, check the “Show Bookmarks” checkbox, as in image below: The code will look in the list of defined names from column A, and will try to find a bookmark with the same name in the Word Document you indicated.Īdd a bookmark for each duplicate field, but make sure that the bookmark name is present in all duplicates: use duplicate bookmark names like this: RecipientName, RecipientName 1, RecipientName 2, or even RecipientName a, RecipientName b and so on the code will look for the main part: RecipientName and will send data to any bookmark that has a name starting with this, you can add any suffix you want. In Map Table, each cell from column B has a defined name, listed in column A. You can download the sample files from here:ĭownload: Create Individual or Mass Letters.xlsmĭownload: Sample Letter.docx How to Set Up the Map Between Excel Defined Names and Word Bookmarks: The key of this solution is a Map between Excel Defined Names and Word Document Bookmarks. The following will cover all these scenarios, the letters will be saved as PDF files.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |