VBA to ignore blank lines in email body

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
125
Hi all,
I've got an excel VBA running to send an email based on the text inside a range of cells, this works fine. What I would like to do is change it slightly so if the cell is blank is skips it rather then leaving a blank row in me email.
As a side note, I can choose my font as Arial but not as Franklin Gothic Book - how would I do that? font-family:Franklin Gothic Book doesn't work

VBA Code:
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
    
        eBody = "<BODY style=font-size:10.0pt;font-family:Arial>" & Cells(i, 8) & "<br>" & Cells(i, 9) & "<br>" & Cells(i, 10) & "<br>" & Cells(i, 11) & "<br>" & Cells(i, 12) & "<br>" & Cells(i, 13) & "<br>" & Cells(i, 14) & "<br>" & Cells(i, 15) & "<br>" & Cells(i, 16) & "<br>" & Cells(i, 17) & "</font>"
        toList = Cells(i, 3)
        ccList = Cells(i, 4)
        eSubject = Cells(i, 7)
        
        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ccList
        .BCC = ""
        .Subject = eSubject
        .Display
        End With
        With OutMail
        
        .HTMLBody = eBody & vbNewLine & .HTMLBody
        .Send
        End With

Thanks in advance for the help
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
125
Is anyone able to offer some assistance with this please? I've had to give up because it's beyond my capability :(
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Replace your eBody = line with this, which skips blank cells and sets the font on the paragraph tag rather than the body.
VBA Code:
    Dim c As Long
    eBody = "<p style='font-family:Franklin Gothic Book;font-size:10.0pt;'>"
    For c = 8 To 17
        If Not IsEmpty(Cells(i, c).Value) Then eBody = eBody & Cells(i, c).Value & "<br>"
    Next
    eBody = eBody & "</p>"
 

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
125
Replace your eBody = line with this, which skips blank cells and sets the font on the paragraph tag rather than the body.
VBA Code:
    Dim c As Long
    eBody = "<p style='font-family:Franklin Gothic Book;font-size:10.0pt;'>"
    For c = 8 To 17
        If Not IsEmpty(Cells(i, c).Value) Then eBody = eBody & Cells(i, c).Value & "<br>"
    Next
    eBody = eBody & "</p>"

Hi John,
Thank you for helping with this! Sorry it's taken me so long to reply. This works perfectly for what I need, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,662
Messages
5,637,637
Members
416,977
Latest member
kdoederlein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top