find/replace WITHIN vba code without opening editor


  #1  
Old 03-05-15, 10:45 AM
B
Member
Thread Starter
Join Date: Jul 2006
Location: Central MN
Posts: 539
Upvotes: 0
Received 0 Upvotes on 0 Posts
find/replace WITHIN vba code without opening editor

I need to have people be able to find and replace an email address within some vba code. I do not want them to have to open the editor in-order to find it.
I have a userform I created with a text box for them to enter the email address. I want them to enter the address and click a command button. That click will then search for a line that is .To = "[current address]" and replace it with .TO = "[New address]"
 
  #2  
Old 03-05-15, 01:16 PM
P
Temporarily Suspended
Join Date: Jul 2008
Location: NY
Posts: 10,265
Upvotes: 0
Received 0 Upvotes on 0 Posts
$path = "C:\testFile.txt"
$word = "searchword"
$replacement = "ReplacementText"
$text = get-content $path
$newText = $text -replace $word,$replacement
$newText > $path

That's a powershell script that I found on:
string replace file content with powershell - Stack Overflow
 
  #3  
Old 03-06-15, 05:53 PM
Z
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 3,375
Received 121 Upvotes on 112 Posts
I don’t think that code will work. It is replacing one constant (“searchword”) with another constant (“ReplacementText”). But “current address”, what you want to search for and replace, is variable. In other words, with that code you have to first replace “searchword” with “current address” – which is the thing you don’t know.

My knowledge of VBA is not good but I don’t believe you would replace the email “TO” line by actually searching for the TO line. Isn’t there an OutLook object for the email that has already been created at the time you want to replace the “TO” line? That object would already have a “.To” property.

You would just assign the .To property with the new TO line from your textbox. I don’t have Outlook so I can’t create an Outlook object and test. But I did set up a sheet with a button(ToLineChangeButton ) and a text box (NewToLineTextBox), and I created a simple class with two properties (To and Subject) like the real OutLook email class. When you click the ToLineChangeButton button whatever is in the NewToLineTextBox is moved to the email TO line in the ChangeEmailFields subroutine.

I added a line of code to change the Subject line to append the word “Approved” to the subject just to show there would be other things you could do at the time the user enters a new TO line.

Might give you a few ideas (can't format better here, they throw out spaces - and forget indent lol). But if you actually do want to search through many emails looking for a specific TO line - that would be much different code than shown here.


Private Sub ToLineChangeButton_click()

'user clicked on change button so call routiine to change email 'TO' line and other fields

ChangeEmailFields
End Sub


Sub ChangeEmailFields()

Dim objMail As EmailClass

' would use already existing OutLook email object rather than
' creating a new one as is done in next instruction

Set objMail = New EmailClass

'get text from NewToLineTextBox and move into email TO line

objMail.ToLine = NewToLineTextBox.Text

'append word APPROVED to subject to indicate user OKed msg

objMail.Subject = objMail.Subject & "-APPROVED"

'add/mod other Email object fields here e.g. - FROM Line etc.

MsgBox (objMail.Subject & objMail.ToLine) ' display for debug

End Sub ' end ChangeEmailFields
here is the class I created for the above code:

' this is the class named "EmailClass" and used to create an email object. The Subject property is set
' set to "Canned subject" for demonstration purposes


Private myToLine As String
Private mySubjectLine As String

Private Sub Class_Initialize()
MsgBox "Class was initialized"
mySubjectLine = "Canned subject"
End Sub


Public Property Get ToLine() As String
ToLine = myToLine
End Property

Public Property Let ToLine(newToLine As String)
myToLine = newToLine
End Property

Public Property Get Subject() As String
Subject = mySubjectLine
End Property

Public Property Let Subject(newSubject As String)
mySubjectLine = newSubject
End Property

'end EmailClass


 
  #4  
Old 03-07-15, 06:57 AM
P
Temporarily Suspended
Join Date: Jul 2008
Location: NY
Posts: 10,265
Upvotes: 0
Received 0 Upvotes on 0 Posts
Does the fact that he is using VBA, point to Outlook, since the OP didn't mention it? Does it matter what the target file is? There are other scripts on that same site, in every possible scripting language. However, I imagine that the same problem of replacing an unknown variable would follow no matter what language is used. Since the @ in the address is not a variable, would it help if you grepped for the line with the @ in it first?
 
  #5  
Old 03-07-15, 09:51 AM
Z
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 3,375
Received 121 Upvotes on 112 Posts
It appears bc is using Outlook from another post. I don’t think it would matter anyway because I believe email is represented everywhere today as objects with members, properties, and methods. Outlook follows that model and Microsoft calls the email object in their Outlook model a “MailItem”.

So you wouldn’t be searching through a stream of text to match a particular “TO” line. If you did in fact have the email as entire stream of text you could look for the @ sign, but only coupled with something else so you didn’t wind up at the wrong @. That’s what I was implying when I said you don’t know what to replace the “searchword” with in the above code. Thus I was implying that you could only search for the “@” or something which is constant, as you point out, not the current address, and hope that would guarantee you hit on the :TO: line.

But I’m going off on a tangent here because the email doesn’t exist in a contiguous text stream searchable form anyway so we really can’t do that type of search– you have to look through MailItem properties to match on something.

So I’m thinking maybe bc actually really doesn’t need to do a search if he is already working with a given MailItem – just replace the Recipients property of the MailItem with a new TO string from his text box input.

In my little example above it may be a little misleading because I don’t have Outlook, and thus cannot create a MailItem object. So for my object I just made up a property called ToLine. The real MailItem object has a property called “Recipients” which is a Collection as is used like this;


‘ DIYMail is a MailItem object

DIYMail.Recipients.Add("xxx@zzz.zz");
 
 

Thread Tools
Search this Thread
 
Ask a Question
Question Title:
Description:
Your question will be posted in: