Word Mail Merge->PDF->Auto Name
I am trying to think of a way to automate a process. Part of this is in word and part with Adobe. Basically, here is the scenario. Every year all the staff in the company (about 350-400 employees) get evaluated and then their compensation is recalculated and an employment contract sent to them for the next year. The contract is in PDF form which they digitally sign and return to the company. Right now the process is like this: A spreadsheet is created with all the employee specific information. The spreadsheet data is merged with the standard employment agreement based on their title. The merge creates a different document for each employee which must then be saved (named with the employee name), converted to a PDF, and then a digital signature is requested via Adobe. I would like to automate this whole procedure. I would like to mail merge, save each document as a Word Doc and then a PDF under the employee's name. Once I have all of that, I would like to get PDF to send out all the documents via email (which will be in the spreadsheet too). I am a programmer so code does not scare me in the least. I am just not sure of the approach to take. Any thoughts? Thanks in advance.
Table cell value from VBA
Hello, I have a question. When I get the value of a cell from a table in Word, it always brings it with an unknown character. If we activate "Show All" we see that there is a respective symbol for each cell, although each part of a document has its own symbol, like space, section break, page break, paragraph, etc. In fact, I get, for the example of this post, the value of a paragraph and it gives it to me clean, without any character. So, I don't understand what is that character that brings the value of the cell, do you know what is that character and do you know what other way there is to extract the value cleaner without that character? I show you in the screenshot the solution I gave to this problem (it is as a comment), but I would like to know if you have another way to solve this issue.
VBA code to bring the active document in front
I have a subroutine that produces and edits six different documents. My current problem is that the documents all stay in the background. But, I want them to be in front of all other windows using only VBA and without APIs. I've looked for code to do that and they either use APIs or create a new document. I tried this code: Sub Generate_Motion() Call Initiations Documents.Add Template:=pathInputDoc & "\Template.dotx" Dim wdApp As word.Application Dim word As word.Document Set wdApp = GetObject(ActiveDocument, "Word.Application") wdApp.Visible = True With ActiveDocument ... End Sub but it gives me an error. Is there way to bring an already-open document in front using only VBA code?SOLVED - Expand/Collapse specified heading(s) based on criteria (VBA code)
Hello all, I spent an entire evening trying to figure out the VBA code to collapse all headings in my document upon opening except for my "Notes" header (and only if I had a form control checkbox checked saying to do so). The only thing I found on the internet was the Range.Expand and Range.Collapse functions which did not work at all, no matter how much I played with it. I did manage to get the result I wanted using the SendKeys function with a collab of .Activate but it was really wonky. However, when I was playing around with it and searching through the arguments, I found .CollapsedState which could only follow after .Paragraphs(). I tested it and it worked! Polished Code: Private Sub Document_Open() On Error GoTo ErrHandler ThisDocument.ActiveWindow.View.CollapseAllHeadings Dim check_box As ContentControl Set check_box = ThisDocument.Range.ContentControls(Index:=1) If check_box.Checked = True Then Call ExpandNotes ErrHandler: End Sub Sub ExpandNotes() On Error GoTo ErrHandler1 Dim IsFound As Boolean 'ThisDocument.ActiveWindow.NewWindow.Activate 'OUTDATED METHOD 'ActiveWindow.Close 'OUTDATED METHOD IsFound = FindParagraph(ThisDocument.StoryRanges(wdMainTextStory), "Heading 1") ErrHandler1: End Sub Public Function FindParagraph(ByVal SearchRange As Word.Range, ByVal ParaStyle As String) As Long On Error GoTo ErrHandler2 Dim ParaIndex As Long For ParaIndex = 1 To SearchRange.Paragraphs.Count If ThisDocument.Paragraphs(ParaIndex).Range.Style = ParaStyle Then FindParagraph = ParaIndex If ThisDocument.Paragraphs(ParaIndex).Range.Text Like "*Notes*" Then ThisDocument.Activate ThisDocument.Paragraphs(ParaIndex).CollapsedState = False 'ThisDocument.Paragraphs(ParaIndex).Range.Select 'OUTDATED METHOD 'SendKeys "{RIGHT}", True 'OUTDATED METHOD 'SendKeys "~", True 'OUTDATED METHOD Exit Function End If End If Next ErrHandler2: 'Function built off of the original code (by freeflow): _ https://stackoverflow.com/questions/61209283/vba-word-find-a-paragraph-that-has-a-specific-style End Function It would be cool to use a date function for criteria at some point too, to expand headers that are within today's date. Notes: • The commented-out code followed by 'OUTDATED METHOD was the first working but wonky attempt. Lines can be removed. • The "check_box" variable is for the only checkbox I have in the document, which is a form control (not an ActiveX control). • You can change the "Heading 1" (ByVal as String for the FindParagraph function) under the "ExpandNotes" sub to the style name that is applicable to your headers. • You can change the "*Notes*" to whatever text your header is. You can also expand/collapse multiple headers if you include an Or statement(s) in that If. • The code is under the "ThisDocument" (Microsoft Word Objects) module for my workbook (not for "Normal"). • Edit/add/remove from the code to your needs.
VBA issue
I have a VBA code and instructions on how to add this into word. The code will take the comments from the word document and add them into a new excel sheet. Whenever I run the code I get the error message Compile error: Sub or function not defined and highlighted in yellow is: Private Sub Document_New() This is at the end of the code. I have no experience with VBA or codes and would just like to know if there is an easy way for this to be sorted, thanks! Edited, code aded in below: Sub ExportComments() 'Dim xlApp As Excel.Application 'Dim xlWB As Excel.Workbook Dim i As Integer, HeadingRow As Integer Dim objPara As Paragraph Dim objComment As Comment Dim strSection As String Dim strTemp Dim myRange As Range Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlWB = xlApp.Workbooks.Add 'create a new workbook With xlWB.Worksheets(1) ' Create Heading HeadingRow = 1 .Cells(HeadingRow, 1).Formula = "Comment" .Cells(HeadingRow, 2).Formula = "Page" .Cells(HeadingRow, 3).Formula = "Paragraph" .Cells(HeadingRow, 4).Formula = "Comment" .Cells(HeadingRow, 5).Formula = "Reviewer" .Cells(HeadingRow, 6).Formula = "Date" strSection = "preamble" 'all sections before "1." will be labeled as "preamble" strTemp = "preamble" If ActiveDocument.Comments.Count = 0 Then .Cells(2, 1).Value = "No comments found" MsgBox ("No comments") Exit Sub End If For i = 1 To ActiveDocument.Comments.Count Set myRange = ActiveDocument.Comments(i).Scope strSection = ParentLevel(myRange.Paragraphs(1)) ' find the section heading for this comment 'MsgBox strSection .Cells(i + HeadingRow, 1).Formula = ActiveDocument.Comments(i).Index .Cells(i + HeadingRow, 2).Formula = ActiveDocument.Comments(i).Reference.Information(wdActiveEndAdjustedPageNumber) .Cells(i + HeadingRow, 3).Value = strSection .Cells(i + HeadingRow, 4).Formula = ActiveDocument.Comments(i).Range .Cells(i + HeadingRow, 5).Formula = ActiveDocument.Comments(i).Initial .Cells(i + HeadingRow, 6).Formula = Format(ActiveDocument.Comments(i).Date, "dd/MM/yyyy") .Cells(i + HeadingRow, 7).Formula = ActiveDocument.Comments(i).Range.ListFormat.ListString Next i .Cells(i + HeadingRow, 1).Value = "DONE" End With Set xlWB = Nothing Set xlApp = Nothing End Sub Function ParentLevel(Para As Word.Paragraph) As String ' Finds the first outlined numbered paragraph above the given paragraph object Dim ParaAbove As Word.Paragraph Set ParaAbove = Para sStyle = Para.Range.ParagraphStyle sStyle = Left(sStyle, 4) If sStyle = "Head" Then GoTo Skip End If Do While ParaAbove.OutlineLevel = Para.OutlineLevel Set ParaAbove = ParaAbove.Previous Loop Skip: strTitle = ParaAbove.Range.Text strTitle = Left(strTitle, Len(strTitle) - 1) ParentLevel = ParaAbove.Range.ListFormat.ListString & " " & strTitle End Function Private Sub Document_New() End Sub
How to disable "RemoveDocumentInformation"?
I use ActiveDocument.RemoveDocumentInformation (wdRDIDocumentProperties) in VBA to remove personal data. Once activated it kills also any personal data in future in the document. So if I want to get rid of this function I know only how to deactivate it manually but I do not find any VBA command to deactivate it. Can you help my?Word Challenge: How to divide a Word Document in several in an easy way
Hello, On my YouTube channel where I present Office tutorials (In Spanish), they asked me how to easily divide Word documents into several. I searched the internet and only found solutions where the document was converted to PDF for splitting or where the split had to be done with fixed page size. I have found a way to do it and I am preparing a video to show how to do it. But before, I have released the following challenge where I ask to look for ways to do this split. I hope you like it and participate: https://youtu.be/AVh5d0EfHAsForm-DocVariable-Building Block Quagmire
I think this one is low-hanging fruit, but my brain has gone full circle, multiple times and I know the solution is easy, but it is elusive. Here's the setup: I have a Userform that collects information thatn is passed onto a document via DocVariables. So, person fills it out and a template is filled in that can be copy/pasted into an email, ORI put a button in the file that would copy/paste the email (as a section) into a newly created Outlook Mail object, which would make the faciilitator's job that much easier, by literally putting one click away from generating an email and sending it from Outlook in a perfectly and wonderfully controlled environment. Everything works swimmingly - I have the content copying over wonderfully into the body, set some static "Subject" Text, etc. But, when trying to pass the .To variable, I get locked up by the DocVariable Field codes sneaking into the To: field. Here's what happens. I get the variable in from the UserForm as "ParticipantEmail" Variable ParticipantEmail is stored in a Building Block called "DCVPartEmail" I use the BuildingBlock to pass the Variable to the .To field in Outmail as DCVPartEmailBB. But then, in the "To:" line in the outlook email, I get EVERYTHING from the DocVariable - *{ DocVariable ParticipantEmail \*MergeFormat} *<TheInputEmailAddress@Email.com>* Obviously, I only want the "TheInputEmailAddress@email.com" to show up in the To: Field. That's all I want to happen - I don't even need the Particpant Email to show up in the actual Word Doc, although I can hide it to pass it, if needed. But all I want is for the Participant email to go from the UserForm to the To: field in an OutMail creation, as just the email. No extras! Check out Module 3 and page 10 for more context Thanks in advance!
