How can I make a macro ignore blank cells

whome

New Member
Joined
Mar 14, 2009
Messages
2
I found a macro that is used for opening an email to a list of e-mail addresses listed in an Excel worksheet, the problem I am having is the workbook is a club member list and not everyone has an e-mail address (hard to believe but true) the macro errors when it hits a blank cell, if I delete all of the blank cells the macro runs flawlessly, re-sorting the list is not practicle, I need the macro to ignore the blank cells and continue. Any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim OlApp As Object, M As Object, c As Range
If Target.Address <> "$A$3" Then Exit Sub
Cancel = True
' Creates an instance of Outlook
Set OlApp = CreateObject("Outlook.application")
'Creation of the message
Set M = OlApp.CreateItem(olMailItem)
With M
.Subject = "Subject"
.Body = "Body"
' Adding the recipients
For Each c In Range("B6", Range("B60000").End(xlUp))
.Recipients.Add c.Value
Next c
.display ' Use this line instead of the next one if you want to see / edit
' the Message
.Send
End With
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hello and welcome to MrExcel

Try

Rich (BB code):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim OlApp As Object, M As Object, c As Range
If Target.Address <> "$A$3" Then Exit Sub
Cancel = True
' Creates an instance of Outlook
Set OlApp = CreateObject("Outlook.application")
'Creation of the message
Set M = OlApp.CreateItem(olMailItem)
With M
    .Subject = "Subject"
    .Body = "Body"
    ' Adding the recipients
    For Each c In Range("B6", Range("B60000").End(xlUp))
        If c.Value <> "" Then .Recipients.Add c.Value
    Next c
    .display ' Use this line instead of the next one if you want to see / edit
    ' the Message
    .Send
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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