How can I reply to an earlier person on a chain of correspondence?

Question

My colleagues often forward me emails to reply to the earlier person in the chain. Typically I hit forward and then set up my reply.

I am working on a VBA macro that will

1. Delete my colleague's message
   
2. Copy new email address into the "to" field" and then
   
3. Insert a pretyped message like
   

"Hello,
etc.
Regards"

I have put together step 1 with the help of another user.

Sub DeleteBeforeText_not_olFormatHTML()

Dim currMail As MailItem
Dim msgStr As String

Dim endStr As String
Dim endStrStart As Long
Dim endStrLen As Long

Set currMail = ActiveInspector.CurrentItem
endStr = "Dear"
endStrLen = Len(endStr)

If currMail.BodyFormat = olFormatHTML Then
    currMail.BodyFormat = olFormatRichText
End If

msgStr = currMail.Body
endStrStart = InStr(msgStr, endStr)

If endStrStart > 0 Then
currMail.Body = Right(msgStr, Len(msgStr) - (endStrStart - 1))
End If

End Sub

After this is run, the email will start with lines that look like this:

From: First Last [mailto:firstlast@email.com]
Sent: Tuesday, May 09, 2017 5:29 AM

I am trying to get "firstlast@email.com" into the to field in this example.

Answer 1

I'd use a Regular Expression like this one to detect the line to get the email from :

^[F][r][o][m][:].*[\[][m][a][i][l][t][o][:](\w+\@.*\..*)[\]].*$

You'll just have to tune a bit the part to delete the start of the body.

Full code :

Sub DeleteBeforeText_not_olFormatHTML()
    Dim currMail As MailItem
    Dim msgStr As String
    Dim endStr As String
    Dim endStrStart As Long
    Dim endStrLen As Long
    Dim regEx As New RegExp

    Set currMail = ActiveInspector.CurrentItem
    If currMail.BodyFormat = olFormatHTML Then
        currMail.BodyFormat = olFormatRichText
    End If

    msgStr = currMail.Body

    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "^[F][r][o][m][:].*[\[][m][a][i][l][t][o][:](\w+\@.*\..*)[\]].*$"
    End With

    If regEx.test(msgStr) Then
        endStr = CStr(regEx.Execute(msgStr)(0))
        Debug.Print endStr 
        endStrLen = Len(endStr)
        endStrStart = InStr(msgStr, endStr)

        If endStrStart > 0 Then
            currMail.Body = Right(msgStr, Len(msgStr) - (endStrStart - 1))
        End If
    Else
    End If
End Sub