Preventing E-mail messages with blank "To" line

Posted by Lewis (1) on December 20, 2001 1:57 AM

Hi,

I am using the following to send E-mails.

Dim myFile As String, s As String
ThisWorkbook.ActiveSheet.Copy
myFile = ActiveWorkbook.Name
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
With ThisWorkbook.Worksheets("Sheet6").Cells
For Each c In .Range("A18:A20").Cells
s = c.Value
Workbooks(myFile).SendMail Recipients:=s, _
Subject:="Here is a NEW Problem Report", _
ReturnReceipt:=False
Next c
End With
Workbooks(myFile).Close savechanges:=False

What I am trying to do is make it ignore empty cells and not generate a message with a blank "To".
Basically I want to be able to set up an area into which the user can place their list of recipients without having to re-set the range in the macro every time they add or subtract a recipient. I have tried variious ways but always get the message with the blank "To".

Any suggestions?

Lewis

Posted by Robb on December 20, 2001 3:31 AM

Lewis

Try testing the s for empty strings and, if found, skip the mail send.

This is part of the code, with suggested inserts:

For Each c In .Range("A18:A20").Cells
s = c.Value
if s = "" then GoTo Skip
Workbooks(myFile).SendMail Recipients:=s, _
Subject:="Here is a NEW Problem Report", _
ReturnReceipt:=False
Skip:
Next c

Any help?

Regards

Robb

Posted by Lewis (1) on December 20, 2001 4:55 AM

Thanks just what I needed also feedback on sending individual sheets

Thanks works fine now.
With ref our early exchanges and one of the other sites (I forget which). Although it is theoretically possible to send an individual sheet as the body of an E-mail with Excel 2000, no one has been able to do it using a macro.