VBA for adding multiple content fields into Subject Header

[Booster]

Hi there,

I have successfully managed to link a submit button that issues an email with the subject field being filled based off of a field's value.

I am using this method:


With EmailItem
.Subject = Me.SelectContentControlsByTitle("Service Number")(1).Range.Text
.Body = "Thanks for submitting!" & vbCrLf & _
"BODY SECOND LINE" & vbCrLf & _
"BODY THIRD LINE"
.To = "boost.emailadress.com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olimprotanceLow
.Attachments.Add Doc.FullName
.Send


End With


how can i add a second or third element to the subject line? I ideally would want to have three more form content controls displaying their data.

Have tried a few alternative means of setting the subject line; for example the me.[content name].value and activedocument.formfields ; but can't seem too get any headway.

Any assistance is greatly appreciated.

[gmayor]

Don't mix content controls and formfields in a document. To include more than one content control content in a string you need something like


Dim strSubject As String
strSubject = Doc.SelectContentControlsByTitle("Service Number").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("The title of the second control").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("The title of the third control").Item(1).Range.Text

Then
.Subject = strSubject

[Booster]

Thanks for that - I adjusted my parameters accordingly - but have now been hit with an error stating "Object variable or With block variable not set"

Here is the current, entire schema of the excerpt (which throws up the error.


Private Sub CommandButton21_Click()Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document

Dim strSubject As String
strSubject = Doc.SelectContentControlsByTitle("Service Number").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Service").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Surname").Item(1).Range.Text

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

With EmailItem
.Subject = strSubject
.Body = “Form” & vbCrLf & _
"BODY SECOND LINE" & vbCrLf & _
"BODY THIRD LINE"
.To = "email address”
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olimprotanceLow
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItems = Nothing

End Sub

[gmayor]

You have used the variable Doc before you have set it. You also have some syntax errors. The following is what that code should look like


Private Sub CommandButton21_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document

Dim strSubject As String
Set Doc = ActiveDocument
strSubject = Doc.SelectContentControlsByTitle("Service Number").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Service").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Surname").Item(1).Range.Text

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0)
Doc.Save

With EmailItem
.Subject = strSubject
.body = "Form" & vbCrLf & _
"BODY SECOND LINE" & vbCrLf & _
"BODY THIRD LINE"
.To = "email address"
.Importance = 1
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

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

End Sub


however it could be improved with the addition of a bit of code you need to download from the link at the top of the macro to open Outlook properly. This one will retain the default signature for the account.


Private Sub CommandButton21_Click()

'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
'Graham Mayor - https://www.gmayor.com - Last updated - 19 Mar 2020

Dim OL As Object
Dim olInsp As Object
Dim EmailItem As Object
Dim Doc As Document
Dim wdDoc As Object
Dim oRng As Object

Dim strSubject As String
Set Doc = ActiveDocument
strSubject = Doc.SelectContentControlsByTitle("Service Number").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Service").Item(1).Range.Text
strSubject = strSubject & Chr(32) & Doc.SelectContentControlsByTitle("Surname").Item(1).Range.Text

Application.ScreenUpdating = False
Set OL = OutlookApp()
Set EmailItem = OL.CreateItem(0)
Doc.Save

With EmailItem
.Subject = strSubject
.To = "email address"
.Importance = 1
.BodyFormat = 2 'olFormatHTML
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.Collapse 1
oRng.Text = "Form" & vbCrLf & _
"BODY SECOND LINE" & vbCrLf & _
"BODY THIRD LINE"
.Display 'Required!
.Attachments.Add Doc.FullName
'.Send 'restore after testing
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Set EmailItem = Nothing
Set OL = Nothing

End Sub