Sqlstatement:="SELECT * FROM `Data$`" ' Set this as requiredĪlso, instead of checking the Excel file for the Employee name to create the file name, you could do this after you merge the document. MailMerge.OpenDataSource Name:=cDir + ThisFileName, _
Const WTempName = "letter.docx" 'This is the 07/10 Word Templates name,
Mail merge from excel to labels code#
You don't need to open up word each time, so put all of the code setting the datasource of the mail merge and creating the word doc outside of your main loop. You need to use r-1 because Word is going to use the record number in its dataset, and since the data starts in row 2, and the counter r is related to the row, you need r-1. To fix this, change the lines as follows: With. Instead, you want to execute the merge one letter at a time. It looks to me that when you are executing the mail merge, it should create a file with ALL of the letters, so when you open it, it would appear that the first letter is the one that is getting saved, but if you scroll down the word file that you have saved, you may find each letter on a new page.
Mail merge from excel to labels pdf#
To save the file in pdf format use cDir & NewFileName, _ ObjMMMD.Close savechanges:=wdDoNotSaveChangesĬells(r, 7).Value = "Letter Generated Already" With objMMMD.MailMerge 'With ActiveDocument.MailMerge MailMerge.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT * FROM `Data$`" ' Set this as required ' Set to True if you want to see the Word Doc flash past during construction Set objWord = CreateObject("Word.Application") Set objWord = GetObject(, "Word.Application") NewFileName = "Offer Letter - " & EmployeeName & ".docx" 'This is the New 07/10 Word Documents File Name, Change as req'd"ĬDir = ActiveWorkbook.path + "\" 'Change if appropriate If Cells(r, 7).Value = "Letter Generated Already" Then GoTo nextrowĮmployeeName = Sheets("Data").Cells(r, 2).ValueĬonst WTempName = "letter.docx" 'This is the 07/10 Word Templates name, Change as req'd Lastrow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row 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.Īlso 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? Sub MergeMe() 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. The problem is the output in all the merged files the output is same as the first row. 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. I have run mail-merge automatically and save individual files as per the Employee name. 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. 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.
0 kommentar(er)
