Adding document fields to subject line of email

russkris · #1 09-24-2017, 05:56 PM

Hi Guys,

I have a small form and added an email button to assist clients to submit it easier and to structure the subject lines

Code:

Private Sub CommandButton1_Click()
Dim OL              As Object
Dim EmailItem       As Object
Dim Doc             As Document
 
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
 
With EmailItem
    .Subject = "SUBJECT LINE"
    .Body = "BODY MESSAGE" & vbCrLf & _
    "SECOND LINE BODY MESSAGE" & vbCrLf & _
    "THIRD LINE BODY MESSAGE"
    .To = "TEST@EMAIL.COM"
    .Importance = olImportanceNormal
    .Attachments.Add Doc.FullName
    .Display
    
End With
 
Application.ScreenUpdating = True
 
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
    
End Sub

I am new to VBA, so I really don't know what I am doing and used google to help.

What I want to do is use information users enter. So I have a uch of content controls for example "date1, commodity, location" etc.

Is there a way I can use the information entered into the fields to form a subject line?

macropod · #2 09-24-2017, 07:29 PM

Your code implies the use of a userform. In that case, simply reference the names of the various textboxes, etc. holding the relevant values. For example, instead of:
.Subject = "SUBJECT LINE"
you might have:
.Subject = Me.TextBox1.Value

However, I note you also refer to content controls. If the Subject is in one of them, you might apply the 'Subject' title to the content control and use:

Code:

.Subject = ActiveDocument.SelectContentControlsByTitle("Subject")(1).Range.Text

russkris · #3 09-24-2017, 08:12 PM

Hi macropod,
Thank you for taking the time to reply..

As i said, i totally googled and pieced together, something.. lol. but no I am not using a userform.

This is what I added(after much more googling.)

Code:

Dim subj1 As String
    Dim subj2 As String
    Dim subj3 As String
    Dim oTbl As Table
    Dim oRg As Range
    Set oTbl = ActiveDocument.Tables(1) ' first table in document
    
    Set oRg = oTbl.Cell(Row:=4, Column:=2).Range ' first part of subject
    oRg.MoveEnd Unit:=wdCharacter, Count:=-1 ' exclude the marker
    subj1 = oRg.Text
    
    Set oRg = oTbl.Cell(Row:=2, Column:=2).Range ' second part of subject
    oRg.MoveEnd Unit:=wdCharacter, Count:=-1 ' exclude the marker
    subj2 = oRg.Text
    
    Set oRg = oTbl.Cell(Row:=5, Column:=2).Range ' thrid part of subject
    oRg.MoveEnd Unit:=wdCharacter, Count:=-1 ' exclude the marker
    subj3 = oRg.Text

    .Subject = subj1 & " - " & subj2 & " - " & subj3

Which seems to work... ok.. However, as they are content controls, if the user doesn't enter a value the standard text of "Click here to enter text" goes into the subject line.

russkris · #4 09-24-2017, 08:18 PM

Also, Your way works too.

Code:

With EmailItem
    .Subject = ActiveDocument.SelectContentControlsByTitle("commodity")(1).Range.Text
    .To = "TEST@EMAIL.COM"
    .Importance = olImportanceNormal
    .Attachments.Add Doc.FullName
    .Display

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Including Text Form Field info in the subject line of an email	Gadget	Word	0	09-08-2016 10:55 AM
Outlook - Add text to subject line when sending a reply email	rparker85	Outlook	3	12-12-2011 10:26 AM
Batch adding text to email subject lines in Outlook	paulkaye	Outlook	0	12-05-2011 06:38 AM
Adding fields to a calendar event beyond subject and location	willauqs	Outlook	0	07-08-2011 01:10 PM
getting additional characters in email subject line	namishtiwari	Outlook	1	04-21-2009 11:21 PM