Extract email body contents into .txt file

[Derek]

Hi guys,

I got the script to extract data from email body to .txt file working . But there is email field in the body which contains email address and it's getting stored into .txt file in the format HYPERLINK "mailto:A11x@hotmail.co.uk"A11x@hotmail.co.uk

Is there any way I can change double quotes to '!!' sign so instead of above it should be showing as below in text file

HYPERLINK !!mailto:A11x@hotmail.co.uk!!A11x@hotmail.co.uk

Please see below the script:

For I = 1 To SubFolder.Items.Count
    
        messageArray = ""
        strRowData = ""
    
        Set myItem = SubFolder.Items(1)
        
        msgtext = Trim(myItem.Body)
          
       'search for specific text
    
        delimtedMessage = Replace(Trim(msgtext), "Please confirm property location", "###")
        delimtedMessage = Replace(Trim(delimtedMessage), "Are you currently on any plan?", "###")
        delimtedMessage = Replace(Trim(delimtedMessage), "Have you received our letter about your options?", "###")
        delimtedMessage = Replace(delimtedMessage, "Name of Borrower", "###")
        delimtedMessage = Replace(delimtedMessage, "Account Number(s)", "###")
        delimtedMessage = Replace(Trim(delimtedMessage), "Property Address", "###")
        delimtedMessage = Replace(delimtedMessage, "Contact Telephone Number", "###")
        delimtedMessage = Replace(delimtedMessage, "Email", "###")
            
        messageArray = Split(delimtedMessage, "###")
    

        For j = 1 To 8
    
            strRowData = Replace(Replace(Replace(Trim(strRowData & Trim(messageArray(j)) & "|"), vbCr, ""), vbLf, " "), vbTab, "")
    
        Next j
        
        strRowData = Replace(strRowData, " " & vbCrLf, vbCrLf)
          
        sFilePath = m & "Form" & I & "-" & Format(Now, "ddmmyyhhmmss") & ".txt"
        
        Set objFile = objFS.CreateTextFile(sFilePath, False)
      
        With objFile
             .WriteLine strRowData
        End With
         
        myItem.Move myDestFolder
  
    Next I

 

[theDBguy]

Hi. Have you tried using one more Replace() line to handle the quotes?

 

[Derek]

Where shall I add another replace ? I just need to replace double quotes with '!!' sign for email part only .

 

[theDBguy]

Where shall I add another replace ? I just need to replace double quotes with '!!' sign for email part only .

In that case, you might have to use regular expression. You can use Replace() on the first quote, but it gets tricky for the second one.

 

[Derek]

Thanks . Can you please send me a line of code ?

 

[theDBguy]

Thanks . Can you please send me a line of code ?

See if you could start with this.

Extract Email Address

This topic is really about Regular Expressions. If you do not know, Regular Expressions are text strings used as a search pattern for matchi...

thedbguy.blogspot.com

 

[Derek]

Yes It worked fine . Added another replace command to replace chr(34 ) with "!!". Thanks so much

 

[theDBguy]

Yes It worked fine . Added another replace command to replace chr(34 ) with "!!". Thanks so much

Congratulations! Good luck with your project.

 

[Deleted member 147267]

I'm curious and a bit confused. Why would it take more than one replace function to remove 2 sets of the same characters? The replace function has a counter parameter...
Replace(string1, find, replacement, start, count, compare)

 

[Isaac]

Just a guess, but I don't think OP wants to risk replacing ALL the quotation marks in the entire string.

 

[Deleted member 147267]

I only saw one pair
HYPERLINK "mailto:A11x@hotmail.co.uk"A11x@hotmail.co.uk

both of which were replaced

HYPERLINK !!mailto:A11x@hotmail.co.uk!!A11x@hotmail.co.uk

Anyway, that's what the count parameter is for, assuming the count of the string to be replaced is in successive order; i.e. using count of 2, you cannot replace the 1st and 3rd (or 4th and 6th, etc). They have to be consecutive.

This ought to begin at position 1 and replace 2 occurrences of "" with !!
replace("""mailto:A11x@hotmail.co.uk""A11x@hotmail.co.uk","""","!!",1,2)

 

[Isaac]

I only saw one pair

My mistake, then. I thought there might be any number of quotations in their original text.

 

[Deleted member 147267]

Who knows? It's another case where we banter about stuff with no feedback from the OP.
Sometimes it's understandable but sometimes I think it goes on too long...

 

[Isaac]

Yeah. I had a little function written, IF the position of the first HYPERLINK " needed to be found, but I didn't even bother posting it, since they seem happy with dbguy's Regex suggestion. I haven't used Regex hardly at all, but that's admittedly mostly because I haven't learned Regex and I try not to use code that I don't fully understand and could modify.