extract subject line from open email

[aeromedic101]

I have written a UserForm that generates the text in the body of an email. One requirement is that the user's selection on the form is compared to the subject line of the email... but i cannot get the macro to extract the value from the subject. Please help!

As an example (this is for a specific application in our office) :

• An email arrives
• The user clicks reply.. and a new email opens up (subject line populated of course)
• The user activates the appropriate macro which then initiates a UserForm (select information from the body of open email is sent to the UserForm)
• The user selects the appropriate fields on the UserForm
• One field needs to be compared to the subject line in the email to which the user is replying (the subject line is in a standardised format)
• If different, the user is prompted for additional information.
• Once complete, the body of the email is generated and the email sent.

But no matter what i try, i cannot extract the info from the subject line.
Outlook 2003, Windows XP

Thanks everybody!

[matthewspatrick]

Please post your code

[aeromedic101]

Thanks for your interest.

At present i have no code for this part of the application as nothing seems to work!

I am manually entering the subject term and then the code takes over but i would really like an automated way to extract the subject line.

I am starting to wonder whether its even possible?!

[matthewspatrick]

Even so, please post the cose that is working for the other bits, and what you have tried to use for the "subject grabbing".

[aeromedic101]

Thanks.
For this code i have just used:
Set Item = ActiveExplorer.Selection(1)
to get a copy of the Subject line. Dirty but it works (on my comp anyhow)

Cheers



Dim varDiagnosis As Variant ' the event term
Dim varDiagnosisSend As String ' as sent
Dim x As String
Function CleanData(varDiagnosis)
' trim the diagnosis and remove punctuation at end of string
' also checks that varDiagnosis contains valid data
Dim last_var As String ' looks for punctuation in diagnosis and remove
Dim first_var As String ' looks for punctuation at begining of string

varDiagnosis = Trim(varDiagnosis)
Do
last_var = Right(varDiagnosis, 1) ' right side of string first
If last_var Like "[A-Za-z]" = False And varDiagnosis <> "" Then
varDiagnosis = Left(varDiagnosis, Len(varDiagnosis) - 1)
varDiagnosis = Trim(varDiagnosis)
End If
Loop While last_var Like "[A-Za-z]" = False And varDiagnosis <> ""

Do
first_var = Left(varDiagnosis, 1) ' left side of string now
If first_var Like "[A-Za-z]" = False And varDiagnosis <> "" Then
varDiagnosis = Right(varDiagnosis, Len(varDiagnosis) - 1)
varDiagnosis = Trim(varDiagnosis)
End If
Loop While first_var Like "[A-Za-z]" = False And varDiagnosis <> ""
varDiagnosis = StrConv(varDiagnosis, vbProperCase)
End Function

Sub Triage_Macro()
'
'
' there are two ways to select the text in the arriving email:
' 1) Place the cursor in front of the diagnosis
' 2) Select (highlight) the actual term
' Both needed
' This script sorts out which is being used
'
Set varDiagnosis = Selection ' Assume data has been selected
CleanData varDiagnosis ' Send for cleaning.

If Len(varDiagnosis) <= 1 Then
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Set varDiagnosis = Selection
CleanData varDiagnosis

If varDiagnosis = "" Then ' if varDiagnosis is still empty, stop programme
MsgBox "Please highlight", 0, " ERROR!"
End ' end script!
End If
End If

Set Item = ActiveExplorer.Selection(1) ' gets the name of the email from email browser
Title = Item ' Title will become UserForm Caption
Title = StrConv(Title, vbUpperCase)
Title = Replace(Title, "TRIAGE", "") ' remove triage
Title = Replace(Title, "FW:", "") ' remove FW:
Title = Replace(Title, "RE:", "") ' remove RE:
Title = Replace(Title, "/", "", 1, 1) ' remove first two "/"
Title = Replace(Title, " ", "")
Title = Trim(Title)
varDiagnosisSend = varDiagnosis ' if you keep as varDiagnosis, creates fatal loop
Load TriageForm1 ' MUST load the form first!
TriageForm1.Caption = Title ' name UserForm
TriageForm1.ComboBox1.AddItem "Ca" ' populate comboboxes
TriageForm1.ComboBox1.AddItem "MI"
TriageForm1.ComboBox1.AddItem "FatMI"
TriageForm1.ComboBox1.AddItem "Non-FatSt"
TriageForm1.ComboBox1.AddItem "FatSt"
TriageForm1.ComboBox1.AddItem "Other CV"
TriageForm1.ComboBox1.AddItem "Non-CV Car"
TriageForm1.ComboBox1.AddItem "Non-CV Non Ca"
TriageForm1.ComboBox2.AddItem "Primary "
TriageForm1.ComboBox2.AddItem "Other "
Call TriageForm1.FillVars(varDiagnosisSend) ' Send diagnosis over to the form

TriageForm1.Show ' show form to user
End Sub