To Debug.Print or not to Debug.Print (Outlook 2010 - Office365)

[dmgaddy]

So the question is, what am I missing in my code (below)? I need to copy some text out of the body of an email in Outlook 2010.
The email is from a temperature monitor and I want to write the temperature and humidity reported to a text file.
I can Debug.Print it to the immediate window but I cannot write it to the text file.


The body of the email is:


Temperature reached 73.76 Fahrenheit at 10/12/2016 9:49:54 AM Humidity reached 37 %RH at 10/12/2016 9:49:54 AM

The only changes in the email are the values following, "Temperature reached," "Humidity reached," and the "Date / Time pair" following each.


Using this code:


Sub GetValuesUsingRegEx()

Dim olMail As Outlook.MailItem
Dim Reg1 As RegExp
Dim Reg2 As RegExp
Dim M1 As MatchCollection
Dim M As Match
Dim s As String
Dim n As Integer

Set olMail = Application.ActiveExplorer().Selection(1)
' Debug.Print olMail.Body

Set Reg1 = New RegExp
Set Reg2 = New RegExp

' \s* = invisible spaces
' \d* = match digits
' \w* = match alphanumeric

n = FreeFile()
Open "C:\test.txt" For Output As #n

With Reg1
.Pattern = "Temperature reached\s*(\d*)\.+(\d*)"
.Global = True
End With
If Reg1.Test(olMail.Body) Then

Set M1 = Reg1.Execute(olMail.Body)
For Each M In M1
s = "M.SubMatches(0); "".""; M.SubMatches(1); ""º"""""
Debug.Print s
Debug.Print M.SubMatches(0); "."; M.SubMatches(1); "º"
Print #n, s ' write to file
Close #n
Next
End If
With Reg2
.Pattern = "Humidity reached\s*(\d*)"
.Global = True
End With
If Reg2.Test(olMail.Body) Then

Set M1 = Reg2.Execute(olMail.Body)
For Each M In M1
Debug.Print M.SubMatches(0); "%"
Next
End If


End Sub




I get returned:

This in the Immediate window:

M.SubMatches(0); "."; M.SubMatches(1); "º""
73.76º
37%


Can anyone tell me how to get the value s which should equal 73.76 to write to the text file rather than the code, M.SubMatches(0); "."; M.SubMatches(1); "º"" which is currently being written there?


DMG

[gmayor]

As the old joke goes, 'If I was going there, I wouldn't start from here' :)
I would use a Word Range search to find the strings e.g. as follows
Windows security will probably not allow you to write to the root of the C drive so save the text file to another location that will have write access.
The macro writes the values to the named text file (which it will create as required)


Option Explicit

Sub GetValues()
Dim olMsg As MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim oFound As Object
Dim sTemperature As String
Dim sHumidity As String
Dim n As Integer
On Error Resume Next
Set olMsg = ActiveExplorer.Selection.Item(1)
With olMsg
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
With oRng.Find
Do While .Execute(findText:=" Fahrenheit")
If .found Then
Set oFound = oRng
oFound.collapse 1
oFound.movestartuntil Chr(32), -1073741823
sTemperature = oFound.Text & Chr(176)
Exit Do
End If
Loop
End With
Set oRng = wdDoc.Range
With oRng.Find
Do While .Execute(findText:=" %RH")
If .found Then
Set oFound = oRng
oFound.collapse 1
oFound.movestartuntil Chr(32), -1073741823
sHumidity = oFound.Text & Chr(37)
Exit Do
End If
Loop
End With
End With
n = FreeFile()
Open "C:\Path\test.txt" For Append As #n
Print #n, sTemperature & ", " & sHumidity
Close #n
lbl_Exit:
Set wdDoc = Nothing
Set oRng = Nothing
Set oFound = Nothing
Set olInsp = Nothing
Set olMsg = Nothing
Exit Sub
End Sub