Send e-mail by Macro and let the range stop at the first empty (/blank) cell

[tdruiter]

Hello, I'm developing a new system for my organization. It's just a simple macro which will copy some information from Excel into a new e-mail. The range for copying is A68:E100. Therefore, sometimes there is no data in one of these cells, which means I want to stop the selection. My VBA as follows:

Sub De_Nieuwe_Macro_03_10_14()
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
    ActiveSheet.Range("A68:E100").Copy
    wEditor.Application.Selection.Paste
    With mail
        .Subject = Range("B66").Value
        .To = "Please fill in the right address"
        .Cc = myemail@address.com
        .Display
    End With
End Sub

And as you guys can see, everything is nearly arranged now. It's just that I don't want it to select empty cells and then the Macro is done. Hope anyone can help me out. Thanks in advance for your effort. Best regards, Tom

 

[ranman256]

Check the range then send it if needed...

If WorksheetFunction.CountA(Range("A1:D10")) = 0 Then
MsgBox "DO NOT SEND"
Else
MsgBox "SEND"
End If

 

[tdruiter]

Thanks a lot for your reply. I don't want Excel to send Messages but I want it to stop copying (into Outlook) until the first empty cells is reached. Awaiting your reply and thanks a lot for your effort.

 

[tdruiter]

Anyone known with this problem? Hope someone can help me out on this matter. It's still pending. Thanks in advance.