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