Link to home
Avatar of Bradley Bishop
Bradley Bishop

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Bradley Bishop
Bradley Bishop

ASKER

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
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).
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?
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?).
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?
Add this line after the display line (or replace it, because why display it?)

MyItem.SaveAs "C:\Users\502402087\Desktop\outlooktest.msg", OlSaveAsType.olTemplate
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