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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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