VBA code to remove carriage returns from outlook table

[eranjo]

Hi all, I am new here.

Monthly, I get an email from my manager with cases in a table format. The columnns includes the case number, client name, date received, case type, remarks. The remarks cells is string data type and has a lot of carriage returns. What I usually do is do a find "^l" replace with "^v" but I would prefer to have a VBA code to automatically replaces all the carriage returns with a " ".

I found this code but there is an error "Method or data member not found" ini the For loop section wdCell.Range.Replace vbCr, "", wdReplaceAll. The word 'Replace' is the error.

Sub RemoveCarriageReturnsFromTable()
Dim olMail As MailItem
Dim wdDoc As Word.Document
Dim wdTable As Word.Table
Dim wdCell As Word.Cell

'Get the selected email
Set olMail = ActiveExplorer.Selection(1)

'Get the Word document that represents the email body
Set wdDoc = olMail.GetInspector.WordEditor

'Get the first table in the document
Set wdTable = wdDoc.Tables(1)

'Loop through each cell in the table
For Each wdCell In wdTable.Range.Cells
'Remove the carriage returns in the cell
wdCell.Range.Replace vbCr, "", wdReplaceAll
Next wdCell
End Sub

If you have better code would really help Thanks a lot

 

[Micron]

I'm quite familiar with vba Replace function and it doesn't look like that. Perhaps you are using the worksheet version in vba code (because I think it's different) so maybe that's the problem. The vba version is Replace(string to search, string to find, replacement value, [start, [count, compare]]])
So maybe
wdCell.Range.Value = Replace(wdCell.Range.Value,Chr(10),"")

I can't recall if you can use vb constants in the function but I don't think so. I also think the character you want to remove is 10 but it could be 13 or both. Easy enough to figure that out if need be.
EDIT - Range without an address does not look right to me.

 

[eranjo]

Hi Micron

Thanks for your help. Sorry took a while to respond as I had to read more articles then conducted several testing.

I tried using the syntax you have provided wdCell.Range.Value = Replace(wdCell.Range.Value,Chr(10),"") but it only replaced the carriage return at the end of each cell.

I have found the solution below

Get the selected email
Set olMail = ActiveExplorer.Selection(1)

ActiveExplorer.Selection(1).Display
ActiveInspector.CommandBars.ExecuteMso "EditMessage"

'Get the Word document that represents the email body
Set wdDoc = olMail.GetInspector.WordEditor

'Get the first table in the document
Set wdTable = wdDoc.Tables(1)

 

[eranjo]

I found the solution for my issue. First is to select the email to be editen then display and allow to display for edit. Then get the first table and loop through each cell to find the carriage return then replace with space " ". Lastly, save and close the mailitem object.

'Get the selected email

Set olMail = ActiveExplorer.Selection(1)



ActiveExplorer.Selection(1).Display

ActiveInspector.CommandBars.ExecuteMso "EditMessage"



'Get the Word document that represents the email body

Set wdDoc = olMail.GetInspector.WordEditor



'Get the first table in the document

Set wdTable = wdDoc.Tables(1)





'Loop through each cell in the table

For Each wdCell In wdTable.Range.cells

'Remove the carriage returns in the cell

If wdCell.ColumnIndex = 8 Then

With wdCell.Range.Find

.Text = "^l"

.Replacement.Text = " "

.Execute Replace:=wdReplaceOne

End With

olMail.Close olSave

End If



Next wdCell