Search & Replace in email body when sending an email with Excel

[DreamEyes]

Hi everyone,

I'd like to ask for your help with an issue I'm having with a project.

I have an Excel document with 2 tabs:

- Sheet 1 where the user can fill in their report and comment on their tasks.
- Sheet 2 that displays the information from Sheet 1 with a layout appropriate for an email.

Then, the following macro prepares and sends an email with Outlook:

Dim xRng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim wDoc As Object
Dim wRng As Object

Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then
Set OutApp = CreateObject("Outlook.Application")
If OutApp Is Nothing Then
MsgBox "Outlook is not accessible"
Exit Sub
End If
End If

'Get the contents
On Error GoTo Errorhandler

LastRow = Worksheets("Email").Columns(1).Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Set xRng = Worksheets("Email").Range("A1:J" & LastRow).SpecialCells(xlCellTypeVisible)

'Now create a mail
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = Range("EmailTo").Value
.CC = Range("EmailCc").Value
.Attachments.Add ActiveWorkbook.FullName
.Subject = "<subject here="">")
Set wDoc = OutMail.GetInspector.WordEditor
Set wRng = wDoc.Range

'Copy and paste the contents
xRng.Copy
wRng.Paste

.Display
.Send

My issue:

In the Sheet 2 (where the data are copied from and then pasted in the email body), there is a cell listing multiple company names (based on what the user said in their report) and these company names could be anything (no preset list).
I.e. "List of companies: Company 1, Company 2, Company 3"

I would like each company to be hyperlinked with a URL like "www.test.com/Company_Name<company>". The URL is fixed and only "Company_Name" <company>varies.

Unfortunately I cannot list all companies in separate cells.

My question is:

- After my macro has pasted the email content (from Sheet 2) into the email body, is there a way I could hyperlinked the companies directly in the email body with a search & replace command within the email body and replace each company name with a link? Basically replacing Company with Company

- Alternatively, is there a way I could instead display the company names in the Excel cell with some HTML codes that could be interpreted as linked words when being pasted into the email editor?


I hope that makes sense. Please let me know if you need additional information.

Thanks a lot in advance for your help!</company></company></subject>