Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-27-2018, 12:05 AM
TomDW TomDW is offline Macro to remove underscores, except in email address Windows 10 Macro to remove underscores, except in email address Office 2016
Novice
Macro to remove underscores, except in email address
 
Join Date: Sep 2018
Posts: 3
TomDW is on a distinguished road
Default

Hi Macropod,

Unfortunately the e-mail addresses are not formatted as hyperlinks.
The Word document is the result of merging fields, and apparently this doesn't automatically format the e-mail addresses...
Reply With Quote
  #2  
Old 09-27-2018, 01:08 AM
macropod's Avatar
macropod macropod is offline Macro to remove underscores, except in email address Windows 7 64bit Macro to remove underscores, except in email address Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by TomDW View Post
The Word document is the result of merging fields, and apparently this doesn't automatically format the e-mail addresses...
It can (see the Mailmerge Tips and Tricks 'Sticky' thread - https://www.msofficeforums.com/mail-...ps-tricks.html), but that's another story. Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim Hlnk As Hyperlink
With ActiveDocument
  With .Range
    'Activate email addresses as hyperinks
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Format = False
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "<[0-9A-?.\-_]{1,}\@[0-9A-?\-_]{1,}.[a-z]{2,3}>"
      .Replacement.Text = ""
      .Execute
    End With
    Do While .Find.Found
      i = i + 1
      .Hyperlinks.Add Anchor:=.Duplicate, Address:="mailto:" & .Text, TextToDisplay:=.Text
      .Start = .Hyperlinks(1).Range.End
      .Find.Execute
    Loop
    'Replace underscores with spaces
    With .Find
      .Text = "_"
      .Replacement.Text = " "
      .Wrap = wdFindContinue
      .MatchWildcards = False
      .Execute Replace:=wdReplaceAll
    End With
  End With
  'Repair email address underscores
  For Each Hlnk In .Hyperlinks
    With Hlnk
      If InStr(.Address, "mailto:") > 0 Then .TextToDisplay = Split(.Address, "mailto:")(1)
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-27-2018, 01:15 AM
TomDW TomDW is offline Macro to remove underscores, except in email address Windows 10 Macro to remove underscores, except in email address Office 2016
Novice
Macro to remove underscores, except in email address
 
Join Date: Sep 2018
Posts: 3
TomDW is on a distinguished road
Default

Hi everyone, thanks for the replies.
Gmayor's code seems to do the trick...
If I'm having problems with this solution later on, I can always try the other suggestions.
Thanks!
Reply With Quote
Reply

Tags
macro replace email



Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Email Address of Previous Email when Replying or Forwarding bondingfortoday Outlook 0 03-05-2016 04:29 PM
Add link to email address that hides the actual address or makes it inaccessible to online bots richiebabes Word 1 09-03-2014 03:22 PM
Macro to send from non-default email address k.n. Mail Merge 5 12-03-2013 03:22 AM
Mail Merge Many URLs in one email by common email address instantaphex Mail Merge 3 04-29-2013 05:46 PM
Remove a Group Email address from Outlook meppwc Outlook 0 10-17-2012 07:09 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft