Assistance with Word VBA

nanders8 · #1 05-03-2017, 06:27 AM

I have a word document setup as a form. The users have to fill out the form then hit the Submit Email button at the bottom of the form. When they press this button, it will generate an email to a specific email address and attach the form to the email. I am wanting the subject to take and concatenate two fields from the form together. Everything is sort of working except for a couple of things.

When I submit the form the subject line reads as

GSS #390 for FORMTEXT NICHOLAS ANDERSON FORMTEXT 605-321-1181

Is there a way that I can remove the FORMAT text from the subject?

Here is the code that I am using:

Code:

Private Sub CommandButton1_Click()
Dim olkApp As Object
Dim strSubject As String
Dim strTo As String
Dim strBody As String
Dim strAtt As String

    strSubject = "GSS #390 for" & ActiveDocument.Bookmarks("User_Name").Range.Text & ActiveDocument.Bookmarks("Cell_Phone_Number").Range.Text
    strTo = "nanders8@good-sam.com"
      strAtt = ActiveDocument.FullName
    
    Set olkApp = CreateObject("outlook.application")
    With olkApp.createitem(0)
        .to = strTo
        .Subject = strSubject
        .body = strBody
        .attachments.Add strAtt
        '.send
        .Display
    End With
    Set olkApp = Nothing
End Sub

Any assistance would be greatly appreciated.

gmayor · #2 05-03-2017, 08:25 PM

Use the formfield result for the content.

Code:

strSubject = "GSS #390 for " & ActiveDocument.FormFields("User_Name").Result & Chr(32) & ActiveDocument.FormFields("Cell_Phone_Number").Result

nanders8 · #3 05-04-2017, 06:07 AM

Thanks for your help.

One more question....we are saving the document outside of the email system, is there a way to have the document titled the same as the subject?

gmayor · #4 05-04-2017, 10:39 PM

You can add the subject string to your macro that saves the document e.g.

Code:

Sub SaveForm()
Dim strPath As String
Dim strName As String
strPath = "C:\Path\"    'Where to save the document
    On Error GoTo err_Handler
    If ActiveDocument.FormFields("User_Name").Result = "" Or _
       ActiveDocument.FormFields("Cell_Phone_Number").Result = "" Then
        MsgBox "Complete name and cell number fields"
        GoTo lbl_Exit
    End If
    strName = "GSS #390 for " & ActiveDocument.FormFields("User_Name").Result & Chr(32) & ActiveDocument.FormFields("Cell_Phone_Number").Result
    strName = strName & ".docx"
    ActiveDocument.SaveAs2 strPath & strName
lbl_Exit:
    Exit Sub
err_Handler:
    If Err.Number = 5941 Then
        MsgBox "One or other of the form fields used to name the documnent is missing!"
        Err.Clear
        GoTo lbl_Exit
    End If
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search assistance	mysterytramp	Word	2	04-06-2016 06:28 AM
Assistance with VBA Code to identify and highlight dates in a word doc	shaunlthomas	Word VBA	1	08-13-2015 05:25 PM
Assistance with Word 2010 If formula	ciresuark	Word	1	03-09-2015 12:57 PM
Graph Assistance	medavidcook	Excel	9	01-31-2014 02:17 AM
Formula assistance please	blazzercat	Excel	2	07-08-2009 08:45 AM