VBA Code for formatting spacing in Outlook

[CarlStephens]

When using VBA code to create emails in Outlook, the email creates spaces between lines/rows which are not there on the spreadsheet. I have to use the Format Text tab in Outlook and select the Ad Space Before/After options to get the desired look, per the second screen shot. Anyone know of a fix for this?

Sub Outcome_Email()

Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim ws As Worksheet
Set ws = Sheets("RT1")
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

With xOutMail
.Display
.To = ws.Range("C2").Value
.CC = ws.Range("C3").Value
.BCC = ws.Range("C4").Value
.Subject = ws.Range("C5").Value
.body = Join(Application.Transpose(ws.Range("C14", ws.Cells(ws.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
On Error Resume Next
.Attachments.Add ws.Range("C7").Value
.Attachments.Add ws.Range("C8").Value
.Attachments.Add ws.Range("C9").Value
.Attachments.Add ws.Range("C10").Value
.Attachments.Add ws.Range("C11").Value
.Attachments.Add ws.Range("C12").Value
On Error GoTo 0


End With

On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[KOKOSEK]

You've got vbCrLf as parameter for JOIN, so you will get extra line between lines.

 

[CarlStephens]

You've got vbCrLf as parameter for JOIN, so you will get extra line between lines.

Thank you. So do I simply remove the vbCrLf?

 

[KOKOSEK]

Check. I do not know what you've got in cells C14:CXX...

 

[CarlStephens]

Check. I do not know what you've got in cells C14:CXX...

I found the fix, which is to replace the vbCrLf with vbVerticalTab and eh voila, I got the spacing removed. Thank you for your inspiration.

 

[KOKOSEK]

No problem. Happy to help.