Outlook macro replace Hyperlinks

Hello All,

I am writing a small Outlook Macro via the Outlook 2013 VBA to open a .msg file and replace our old sharepoint links with our new share point links. The problem that i am having is that when i run this, It seems to do what i want it too except that it gets rid of all the formating in the email. Is there a way to fix this? It seems like the default is in Rich Text.

My VBA so far is:

Sub ReplaceHyperLinks()
 Dim MyItem As Outlook.MailItem
 Set MyItem = Application.CreateItemFromTemplate("C:\Users\502402087\Desktop\outlooktest.msg")
 
 Dim bodyRep As String
 Dim oldstr As String
 Dim newstr As String
 oldstr = "gensuitellccom-1.sharepoint.microsoftonline.com"
 newstr = "gensuite-portal1.sharepoint.com"
 
 
 bodyRep = MyItem.body
 bodyRep = Replace(bodyRep, oldstr, newstr)
 MyItem.body = bodyRep
 
 MyItem.Display
End Sub

Thanks,
Brad
LVL 2
Bradley BishopAssociate Product DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Sub ReplaceHyperLinks()
  Dim MyItem As Outlook.MailItem
 Set MyItem = Application.CreateItemFromTemplate("C:\Users\502402087\Desktop\outlooktest.msg")
    
  Dim bodyRep As String
  Dim oldstr As String
  Dim newstr As String
  oldstr = "gensuitellccom-1.sharepoint.microsoftonline.com"
  newstr = "gensuite-portal1.sharepoint.com"
  
  MyItem.HTMLBody = Replace(MyItem.HTMLBody, oldstr, newstr)

  MyItem.Display
 End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bradley BishopAssociate Product DeveloperAuthor Commented:
I tried that and its alot closer. but still gets rid of some of the formating. There is a table at the top of the page that goes away. I have attached before and after pictures. My original format is in Rich Text, if that matters
emailbefore.JPG
emailafter.JPG
KimputerCommented:
Is there a way for you to save the file as HTML? The reason is that I tried to fix it with RTF but can't, while HTML will work. The easiest solution is for you to check if the template saved as HTML still works, and then use the code.
The difference is that RTFBody is almost a binary format (in this Outlook instance), while the Body and HTMLbody it's pure text (and hence replacement is possible).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bradley BishopAssociate Product DeveloperAuthor Commented:
I understand. Is there a way to do convert it with in the vba? I did what you said and it worked perfectly but the issue is i will be running this on 1xxx of files so it would be a real pain to go in and resave each one.

Also what would i have to add for it to save the file once it replaces everything?
KimputerCommented:
I would need a lot of time to convert the RTF to strings (and even then it's still pretty much garbage, get the string together as one, replace, then split the string, then convert it back to bytes).
Definitely not done by this weekend. Provide the template too if possible (no sensitive data inside I hope?).
Bradley BishopAssociate Product DeveloperAuthor Commented:
Thats not neccessary because there is no specific "Template" for these.. they are all "random". So im not sure thats the answer. I will work with what i have and present it and see what the turn out is. Also can you comment on how to save the data in vba we have been working with?
KimputerCommented:
Add this line after the display line (or replace it, because why display it?)

MyItem.SaveAs "C:\Users\502402087\Desktop\outlooktest.msg", OlSaveAsType.olTemplate
Bradley BishopAssociate Product DeveloperAuthor Commented:
Yea i agree no need to display it. it seems to be saving it as plain text though. This is on one that i saved as html before i ran it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.