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

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Kimputer

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).

Bradley Bishop

ASKER

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?

Kimputer

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 Bishop

ASKER

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?

Kimputer

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 Bishop

ASKER

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