VBA to ignore blank lines in email body

J

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
130
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Is anyone able to offer some assistance with this please? I've had to give up because it's beyond my capability :(
 
Upvote 0
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>"
 
Upvote 0
John_w said:
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>"
Click to expand...

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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
201
GarnesGambit
G
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
411
josros60
J
J
  • Solved
Email body not showing using html.
Replies
3
Views
589
Logit
Logit
B
  • Solved
Change existing code to attach range of cells to email
Replies
2
Views
214
bh24524
B
G
  • Question
Excel VBA to reply to existing email chain and add workbook as attachment
Replies
4
Views
188
gopalgriffith
G

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,958
Latest member
Hat4Life

Share this page

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
Back
Top