Extract email body contents into .txt file (1 Viewer)

Derek

Registered User.
Local time
Today, 16:24
Joined
May 4, 2010
Messages
234
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:
Code:
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

I’m here to help
Staff member
Local time
Today, 16:24
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried using one more Replace() line to handle the quotes?
 

Derek

Registered User.
Local time
Today, 16:24
Joined
May 4, 2010
Messages
234
Where shall I add another replace ? I just need to replace double quotes with '!!' sign for email part only .
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:24
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:24
Joined
May 4, 2010
Messages
234
Thanks . Can you please send me a line of code ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:24
Joined
Oct 29, 2018
Messages
21,358
Thanks . Can you please send me a line of code ?
See if you could start with this.
 

Derek

Registered User.
Local time
Today, 16:24
Joined
May 4, 2010
Messages
234
Yes It worked fine . Added another replace command to replace chr(34 ) with "!!". Thanks so much :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:24
Joined
Oct 29, 2018
Messages
21,358
Yes It worked fine . Added another replace command to replace chr(34 ) with "!!". Thanks so much :)
Congratulations! Good luck with your project.
 

Micron

AWF VIP
Local time
Today, 19:24
Joined
Oct 20, 2018
Messages
3,476
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

Lifelong Learner
Local time
Today, 16:24
Joined
Mar 14, 2017
Messages
8,738
Just a guess, but I don't think OP wants to risk replacing ALL the quotation marks in the entire string.
 

Micron

AWF VIP
Local time
Today, 19:24
Joined
Oct 20, 2018
Messages
3,476
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)
 

Micron

AWF VIP
Local time
Today, 19:24
Joined
Oct 20, 2018
Messages
3,476
Who knows? It's another case where we banter about stuff with no feedback from the OP. :unsure:
Sometimes it's understandable but sometimes I think it goes on too long...
 

Isaac

Lifelong Learner
Local time
Today, 16:24
Joined
Mar 14, 2017
Messages
8,738
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.
 

Users who are viewing this thread

Top Bottom