VBA: Remove line spacing if there is no content in cell for Email Draft

[svlouis]

We are drafting an email using excel vba codes. In this the HTML body includes 4 lines data. If any of line is blank (no data) we dont require the field and line spacing. (we need to remove the particular line)

Sample data in source file in excel format

Company ID: 4545454
Company Name: ABCDE Ltd
Company Type: Private
Company Industry: Financial

In case if company Type is not listed in the source data, we need to remove the company type field with out line spacing. As of now the result showing one blank space if there is no data

Current result using our macro.

Company ID: 4545454
Company Name: ABCDE Ltd
(space)
Company Industry: Financial


We are looking for the following result (we need to remove the company type field with out line spacing)

Company ID: 4545454
Company Name: ABCDE Ltd
Company Industry: Financial


Thank in Advance,
Sajan

 

[Fluff]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

Could you please post the code that you are already using?

 

[svlouis]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

Sub Vetting()
Dim OutApp As Object
Dim OutMail As object
Dim aEmail as Object

For i = 2 To cells(rows.count,4).end(xlup).row
too = range("D" & i).value
ccc = range("E" & i).value
subj = range("F" & i).value

If range("L"& i).value = "" then
body1 = ""
Else
body1 = range("L"& i).value
end if

If range("M"& i).value = "" then
body2 = ""
Else
body2 = range("M"& i).value
end if

If range("N"& i).value = "" then
body3 = ""
Else
body3 = range("N"& i).value
End if

If range("O"& i).value = "" then
body4 = ""
Else
body4 = range("O"& i).value
End if

strbody = body1& "<br>" &body2& "<br>" &body3& "<br>" &body4

Note :I have given the break code but it's not showing after post.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = AOutApp.CreateItem(0)

With OutMail
.To = too
.Cc = ccc
.Subject = subj
.htmlBod = strbody
.save
End with

set outmail = nothing
set outapp = nothing
next i
end sub

I am not in a position to copy paste the full macro codes. I'm facing the issues in the above mentioned codes.

 

[daverunt]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

Use tags around the code when you post without the " "
In this case you probably need to use the html tags to show < br >

"

"  

code

"[/HTML ]"

 

[Fluff]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

Try

Sub Vetting()
Dim OutApp As Object
Dim OutMail As Object
Dim aEmail As Object

For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
too = Range("D" & i).Value
ccc = Range("E" & i).Value
subj = Range("F" & i).Value

If Range("L" & i).Value <> "" Then strbody = Range("L" & i).Value & "<∇Br/∇>"

If Range("M" & i).Value <> "" Then strbody = strbody & Range("M" & i).Value & "<∇Br/∇>"

If Range("N" & i).Value <> "" Then strbody = strbody & Range("N" & i).Value & "<∇Br/∇>"

If Range("O" & i).Value = "" Then strbody = strbody & Range("O" & i).Value & "<∇Br/∇>"



Set OutApp = CreateObject("Outlook.Application")
Set OutMail = AOutApp.CreateItem(0)

With OutMail
.To = too
.CC = ccc
.Subject = subj
.htmlBod = strbody
.Save
End With

Set OutMail = Nothing
Set OutApp = Nothing
Next i
End Sub

You'll need to get rid of the nabla symbols ∇

 

[svlouis]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

Thanks for your reply. I tried to use the maco which you provided. But I am not able to identify how to insert Nabla symbol. Can you please guide me?

 

[Fluff]

Re: Excel VBA Code for Remove line spacing if there is no content in cell for Email Draft

You don't add them you get rid of them

You'll need to get rid of the nabla symbols ∇

I only added them to ensure the HTML coding wasn't removed