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

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 = "")
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". The URL is fixed and only "Company_Name" 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. I know this is quite a tricky operation so please let me know if you need additional information.

Thanks a lot in advance for your help!

Note: This post has previously been posted here but since I had no reply, I'm asking here. I hope this is OK.

Hi,

With a question like this, you will hardly ever get a response unless you:
a. Upload a sample workbook
b. Describe in detail and/or provide samples of what your final result should be and what your inputs are.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Lewis