Creating a user defined function

Status
Not open for further replies.
R

reubendayal

Senior Member
Outlook version
Outlook 365 64 bit
Email Account
Office 365 Exchange
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
 
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
 
Diane Poremsky said:
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
Click to expand...

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 said:
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
Click to expand...
Hi Diane,
May I gently follow up on the above error?
thank you.
 
Status
Not open for further replies.
< Reference Custom Fields with VBA | Export Outlook calendar appointments by filters and date range >
Similar threads
Thread starter Title Forum Replies Date
Witzker Outlook bug when creating a user defined contact form? Using Outlook 1
F Creating Meeting Request Custom form and distribute it to domain user HELP!!! Using Outlook 0
F Creating Meeting Request Custom form and distribute it to domain user HELP!!! Using Outlook 0
T Outlook 2010 Creating a email 'Group' in OL 2010 Using Outlook 2
G Creating Macro to scrape emails from calendar invite body Outlook VBA and Custom Forms 6
S Custom Contact card - need help creating one Outlook VBA and Custom Forms 1
D Outlook 2016 Creating an outlook Macro to select and approve Outlook VBA and Custom Forms 0
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
G Event when creating task from mailitem Outlook VBA and Custom Forms 2
T Outlook creating unwanted tasks in Tasks and Todo from emails Using Outlook 1
Fozzie Bear Outlook 2016 Creating a shared local Contacts folder Using Outlook 2
M Creating an RSS Feed **FROM** Outlook 2013 Calendar. Using Outlook 5
O How to prevent CC from showing when creating a new mail? Using Outlook 1
N Creating a button or link to a form in the Organizational Forms Library Outlook VBA and Custom Forms 3
B Creating an email with the list of tasks Outlook VBA and Custom Forms 0
L Creating drafts when I thought I was sending Using Outlook 1
R Would creating a new profile cause Outlook to download all the old mails from the server? Using Outlook 1
A Creating Progress Bar or Status Bar Update Outlook VBA and Custom Forms 0
T Outlook creating a folder named: "Unwanted" Using Outlook 3
M Outlook 2007 Contacts Glitch: Creating a new email Using Outlook 1
Liza Creating a rule in outlook to filter messages Using Outlook 0
A Are categories still recommended for creating local distribution lists? Using Outlook 3
S Creating Email - Selecting Pre-Defined Text Using Outlook 2
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
M Creating Outlook Appointments from Excel Cells Outlook VBA and Custom Forms 1
N Creating New Profile Using Outlook 0
Y Creating custom appointment request form with multiple mail recipients Outlook VBA and Custom Forms 5
M creating email from contact file = 3 emails in To field Using Outlook 3
P Recover / Extract Rules from standalone PST file creating RWZ file Using Outlook 2
A Creating an outlook rule to forward an email with a specific message Using Outlook 1
I Creating meeting invite with disabled tentative button Outlook VBA and Custom Forms 5
E Creating email templates for organizational use Using Outlook 0
N Creating or changing the main new mail message template in Outlook 2010 Using Outlook 2
D Creating custom view with VBA Outlook VBA and Custom Forms 2
J Outlook creating unwanted rule on its own Using Outlook 1
R Creating a Room Mailbox with Exchange Online Outlook VBA and Custom Forms 0
A Creating a rule on “Deleted items” folder Using Outlook 1
CMG73 Creating templates with predefined subject and CC Using Outlook 1
G Creating Contact Sub Folders Using Outlook 2
Rupert Dragwater creating gmail account in Outlook 2013 Using Outlook 7
nathandavies Creating a Select Case for a directory of folders Outlook VBA and Custom Forms 1
2 creating custom stationery Using Outlook 2
A Help creating macro for conditional formatting settings Using Outlook 8
Fozzie Bear Creating Custom Meeting Form Outlook VBA and Custom Forms 6
U Creating a (This computer only) folder within an IMAP account directory Using Outlook 1
A Creating archive rule on the clients by script/ Outlook VBA and Custom Forms 3
J Creating a URL from a message body excerpt before forwarding Using Outlook 2
B Need Help Creating Email Based on Subject w Address in Body Outlook VBA and Custom Forms 1
A Creating rule to create week folder and transfer mail using alert Using Outlook 3
J Creating an "isolated" shared calendar in Outlook Outlook VBA and Custom Forms 1

Similar threads

Back
Top