MS Word 2013 Form, create a Save as and Submit Button with macro

Question

I have a simple form (from a template) in Word 2013 and have a 'Submit' button that will:
1. Gather information from fields in the document, name the document, and saveAs.
2. Email the form to pre-set email addresses.

Multiple people will open a document from the template

Private Sub CommandButton21_Click()
Dim OL              As Object
Dim EmailItem       As Object
Dim Doc             As Document
Dim strTagNum As String, strNTID As String, strDate As String

strTagNum = ActiveDocument.SelectContentControlsByTitle("TagNum")(1).Range.Text
strNTID = ActiveDocument.SelectContentControlsByTitle("NTID")(1).Range.Text
strDate = ActiveDocument.SelectContentControlsByTitle("Date")(1).Range.Text
Dim strFilename As String
strFilename = strTagNum & "_" & strNTID & "_" & Format(strDate, "ddmmyyyy") & ".docx"
StrPath = "V:\OPS\Central\Shared\ARM\ALERT"

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument

Doc.SaveAs2 strFilename


With EmailItem
.Subject = "CGF ARM - ALERT ADD/DROP/CHANGE"
.Body = "Please Review this Alert for Continuous Improvement"
.To = "[email protected]"
.Importance = olImportanceNormal
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

MsgBox "Alert Record Submitted"

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing


End Sub

(Credit to Neil Malek at knacktraining.com)
and this topic
Microsoft Word 2013 macro save file name from form content

My questions are:
1. How do I specify the filepath for the saveAs? I have the code in the Macro but the document is being saved in the individual users' Documents folder.

How to I tell the Macro to pull the username from the form and send it to "username"@example.com?

The rest of the macro works, when the 'Submit' button is pushed, the document is renamed, saved, and sent as an email to pre-set email addresses.

I've updated this from the original question, I was able to answer some of my prevous questions through trial and error.

Thank you for your time.

So you don't want to save as template? then what type file you want to SaveAs? — 0m3r
The template will be central, with many users opening a Document1.docx from it. I'd like the Document1.docx to be saved as in a specific location with a filename taken from the form. — ThunderJ
You need to Save it including the path: Doc.SaveAs2 StrPath & "\" & strFilename. Better to check if folder StrPath exists beforehand. Also consider a few UNC paths instead of V: like \\FileServerNAS\Documents\ or \\YourWorkDomain.com\DFSroot\Site\Documents\ . Alternatively, add CustomDocumentProperties to store this path. — PatricK
That worked for including the path, thanks! I'd love to use a UNC path but for this application the company isn't set up that way. — ThunderJ

ThunderJ ThunderJ · Accepted Answer · 2017-02-18T02:59:32

I was able to dig some more and solve the last part of my issue, thanks to this helpful link:

excel: reference cell value to get email recipient for selected row?

I ended up referencing the field value appended with & "example.com" and then .CC the string, worked like a charm!

Here's the code in total, I'm a very shallow end vba user so I'm sure there are cleaner ways, but this seems to work for what I need.

Private Sub CommandButton21_Click()
Dim OL              As Object
Dim EmailItem       As Object
Dim Doc             As Document
Dim strTagNum As String, strNTID As String, strDate As String

strTagNum = ActiveDocument.SelectContentControlsByTitle("TagNum")(1).Range.Text
strNTID = ActiveDocument.SelectContentControlsByTitle("NTID")(1).Range.Text
strDate = ActiveDocument.SelectContentControlsByTitle("Date")(1).Range.Text
Dim strFilename As String
strFilename = strTagNum & "_" & strNTID & "_" & Format(strDate, "ddmmyyyy") & ".docx"
Email_Send_To = strNTID & "@example.com"

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument

Doc.SaveAs2 StrPath & "V:\Central\Shared\ARM\ALERT\SubmittedForms\" & strFilename


With EmailItem
.Subject = "Continuous Improvement"
.Body = "Please Review this Alert for Continuous Improvement"
.To = "[email protected]; [email protected]; [email protected]"
.CC = Email_Send_To
.Importance = olImportanceNormal
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

MsgBox "Alert Record Submitted"

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing


End Sub

Thanks to everyone who took a look and for the helpful code and comments!

MS Word 2013 Form, create a Save as and Submit Button with macro

1 Answers