Userform Data to Populate Word Documents and Email Templates

[airwolf2017]

Afternoon All

Hoping I can get some help here.

I manage a team of call handlers, and want to get a process whereby they can use a userform in excel to then populate two word documents. And also based on some answers on the userform to then fire off some email templates which are partly pre-populated and partly completed from the contents of the userform.

I've kind of made a start, ideally there would be three buttons, one to clear the form and start again, one to open the documents up to then print/save etc and one to send emails. The email templates need to be addressed to different addresses depending on which counties have been selected.

Can anyone assist?

I'm not looking for someone to solve completely - but point me in the right direction.

Thank You

Ian

 

[Macropod]

Here's some code to get you started. It creates two new Word documents, from templates designed for the purpose, populates bookmarks in those documents with the contents of two Excel userform textboxes, then saves the documents in both docx and pdf format.

Private Sub CommandButton1_Click()
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, StrBkMk As String, StrTxt As String

'Create a new Document from the 1st template
Set wdDoc = wdApp.Documents.Add(Template:="C:\Users\" & Environ("Username") & "\Templates\Template1.dotx", Visible:=False)
'Populate the document
StrBkMk = "BookmarkName1": StrTxt = TextBox1.Text
Call UpdateBookmark(wdDoc, StrBkMk, StrTxt)
StrBkMk = "BookmarkName2": StrTxt = TextBox2.Text
Call UpdateBookmark(wdDoc, StrBkMk, StrTxt)
'Save & Close the new document
wdDoc.SaveAs Filename:="C:\Users\" & Environ("Username") & "\Reports\Report1.docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
' and/or:
wdDoc.SaveAs Filename:="C:\Users\" & Environ("Username") & "\Reports\Report1.pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
wdDoc.Close SaveChanges:=True

'Create a new Document from the 2nd template
Set wdDoc = wdApp.Documents.Add(Template:="C:\Users\" & Environ("Username") & "\Templates\Template2.dotx", Visible:=False)
'Populate the document
StrBkMk = "BookmarkName1": StrTxt = TextBox1.Text
Call UpdateBookmark(wdDoc, StrBkMk, StrTxt)
StrBkMk = "BookmarkName2": StrTxt = TextBox2.Text
Call UpdateBookmark(wdDoc, StrBkMk, StrTxt)
'Save & Close the new document
wdDoc.SaveAs Filename:="C:\Users\" & Environ("Username") & "\Reports\Report2.docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
' and/or:
wdDoc.SaveAs Filename:="C:\Users\" & Environ("Username") & "\Reports\Report2.pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
wdDoc.Close SaveChanges:=True

'Exit Word & Cleanup
wdApp.Quit: Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

Sub UpdateBookmark(wdDoc As Word.Document, StrBkMk As String, StrTxt As String)
Dim BkMkRng As Word.Range
With wdDoc
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
  End If
End With
Set BkMkRng = Nothing
End Sub