Creating a user defined function

reubendayal

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Exchange Server 2013
Hi All,

I need help with converting this code in to a user defined funtion.

I am using an word document as a template to create an email. And then changing text in the email using bookmarks. But then some of the changed text I want to reformat. and in this instance only make the changed text's font colour red. And while the below works great if I repeat it each time, it make the code very long. I would like to convert this to a function so it can be used several times. I just do not know how to do it though.

Code:
                    objSel.Find.ClearFormatting
                    objSel.Find.Replacement.ClearFormatting
                    objSel.Find.Replacement.Font.Color = wdColorRed
                    With objSel.Find
                        .Text = "XXXX" ' intended text to be formatted'
                        .Replacement.Text = "XXXX" ' replaced and formatted text'
                        .Forward = True
                        .Wrap = wdFindAsk
                        .Format = True
                        .MatchCase = False
                        .MatchWholeWord = False
                        .MatchWildcards = False
                        .MatchSoundsLike = False
                        .MatchAllWordForms = False
                    End With
                    With objSel
                        If .Find.Forward = True Then
                            .Collapse Direction:=wdCollapseStart
                        Else
                            .Collapse Direction:=wdCollapseEnd
                        End If
                        .Find.Execute Replace:=wdReplaceOne
                        If .Find.Forward = True Then
                            .Collapse Direction:=wdCollapseEnd
                        Else
                            .Collapse Direction:=wdCollapseStart
                        End If
                        .Find.Execute
                    End With
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Short version - just change sub to function
Sub MyMacro()
is
Function MyMacro()

If you need to pass values, you'll use something like this, depending on what the value is you are passing.
Function MyMacro(byVal variablename as string)

For example this is passing a string name which represents a folder.
Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder

Or, you can call a macro from another macro.



Or use one macro with Select Case or arrays. This will work well if you are always looking for specific words:
.Text = "XXXX" ' intended text to be formatted'
.Replacement.Text = "XXXX" ' replaced and formatted text


Dim arrWords As String
Dim arrFind As Variant

' Set up the array
arrWords = Array("findword1", "findword2", "findword3", "findword4", "findword5", "findword6", "findword7", "findword8", "findword9")

' Go through the array and look for a match, then do something
For i = LBound(arrFind ) To UBound(arrFind )
strFind = arrWords(i)
Next i

.Text = strFind ' intended text to be formatted'
.Replacement.Text = strFind ' replaced and formatted text
 

reubendayal

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Exchange Server 2013
Short version - just change sub to function
Sub MyMacro()
is
Function MyMacro()

If you need to pass values, you'll use something like this, depending on what the value is you are passing.
Function MyMacro(byVal variablename as string)

For example this is passing a string name which represents a folder.
Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder

Or, you can call a macro from another macro.



Or use one macro with Select Case or arrays. This will work well if you are always looking for specific words:
.Text = "XXXX" ' intended text to be formatted'
.Replacement.Text = "XXXX" ' replaced and formatted text


Dim arrWords As String
Dim arrFind As Variant

' Set up the array
arrWords = Array("findword1", "findword2", "findword3", "findword4", "findword5", "findword6", "findword7", "findword8", "findword9")

' Go through the array and look for a match, then do something
For i = LBound(arrFind ) To UBound(arrFind )
strFind = arrWords(i)
Next i

.Text = strFind ' intended text to be formatted'
.Replacement.Text = strFind ' replaced and formatted text
Hi Diane,

sorry about the delayed reply. I never received any updates to this post and kept checking the page until a short while back when your reply appeared all of a sudden. :)

Thank you for listing out the several ways to go about this problem. I am trying to pass the string value from one macro to this function to process it. So something on the lines of as you state
"For example this is passing a string name which represents a folder.
Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder

Or, you can call a macro from another macro. "

So I have named the function in my macro as Function Sel(ByVal variablename As String) and as this text is in a selection and am I using the getInspector method from word, I have also copied over this piece of the code from earlier in my main macro so the function can understand where to apply the change. Here's how my Function code looks now:

Code:
Function Sel(ByVal variablename As String) 'Function MyMacro(byVal variablename as string)
    'after the message is open and addressed to the contact, use reference word to put fields into bookmarks
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
           
   
    On Error Resume Next

    'Set objItem = Application.ActiveInspector.CurrentItem
    If oMsg.Class = OlMail Then
        Set objInsp = oMsg.GetInspector
        Set objDoc = objInsp.WordEditor
        Set objWord = objDoc.Application
        Set objSel = objWord.Selection


    objSel.Find.ClearFormatting
    objSel.Find.Replacement.ClearFormatting
    objSel.Find.Replacement.Font.Color = wdColorRed
    With objSel.Find
        .Text = Sel
        .Replacement.Text = Sel
        .Forward = True
        .Wrap = wdFindAsk
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    With objSel
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseStart
        Else
            .Collapse Direction:=wdCollapseEnd
        End If
        .Find.Execute Replace:=wdReplaceOne
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseEnd
        Else
            .Collapse Direction:=wdCollapseStart
        End If
        .Find.Execute
    End With
End If
End Function
And from the main macro I am trying to pass the text string by this statement -
Code:
Sel ("XXXX")
Unfortunately, nothing happens.

So I am not sure what am I missing.

Regards,
Reuben
 

reubendayal

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Exchange Server 2013
Hi Diane,

sorry about the delayed reply. I never received any updates to this post and kept checking the page until a short while back when your reply appeared all of a sudden. :)

Thank you for listing out the several ways to go about this problem. I am trying to pass the string value from one macro to this function to process it. So something on the lines of as you state
"For example this is passing a string name which represents a folder.
Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder

Or, you can call a macro from another macro. "

So I have named the function in my macro as Function Sel(ByVal variablename As String) and as this text is in a selection and am I using the getInspector method from word, I have also copied over this piece of the code from earlier in my main macro so the function can understand where to apply the change. Here's how my Function code looks now:

Code:
Function Sel(ByVal variablename As String) 'Function MyMacro(byVal variablename as string)
    'after the message is open and addressed to the contact, use reference word to put fields into bookmarks
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
          
  
    On Error Resume Next

    'Set objItem = Application.ActiveInspector.CurrentItem
    If oMsg.Class = OlMail Then
        Set objInsp = oMsg.GetInspector
        Set objDoc = objInsp.WordEditor
        Set objWord = objDoc.Application
        Set objSel = objWord.Selection


    objSel.Find.ClearFormatting
    objSel.Find.Replacement.ClearFormatting
    objSel.Find.Replacement.Font.Color = wdColorRed
    With objSel.Find
        .Text = Sel
        .Replacement.Text = Sel
        .Forward = True
        .Wrap = wdFindAsk
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    With objSel
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseStart
        Else
            .Collapse Direction:=wdCollapseEnd
        End If
        .Find.Execute Replace:=wdReplaceOne
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseEnd
        Else
            .Collapse Direction:=wdCollapseStart
        End If
        .Find.Execute
    End With
End If
End Function
And from the main macro I am trying to pass the text string by this statement -
Code:
Sel ("XXXX")
Unfortunately, nothing happens.

So I am not sure what am I missing.

Regards,
Reuben
Hi Diane,
May I gently follow up on the above error?
thank you.
 
Top