run time error when sending mail from vba

[stevebrodie]

Hi All,

If this has been answered apologies, but i cannot find the exact answer. I have set up an email list for distribution of an excel workbook, but no matter what i try i always get an error 'Run-time error 1004' Method 'sendmail' of object'_workbook' Failed

[FONT=Arial]Sub mail()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]Dim cell As Range<o:p></o:p>[/FONT]
[FONT=Arial]Dim strto As String<o:p></o:p>[/FONT]
[FONT=Arial]For Each cell In ThisWorkbook.Sheets("DropDownData").Columns("H").Cells.SpecialCells(xlCellTypeConstants)<o:p></o:p>[/FONT]
[FONT=Arial]If cell.Value Like "*@*" Then<o:p></o:p>[/FONT]
[FONT=Arial]strto = strto & cell.Value & ";"<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]
[FONT=Arial]Next<o:p></o:p>[/FONT]
[FONT=Arial]'ActiveWorkbook.SaveAs "Morning Report for a" & Format(Date, "dd mmm")<o:p></o:p>[/FONT]
[FONT=Arial]ActiveWorkbook.SendMail Recipients:=strto<o:p></o:p>[/FONT]

Starting to pull my hair out, what little i have left

 

[AlphaFrog]

Try something like this...

Sub mail()
    Dim cell   As Range
    Dim vTo  As Variant
    For Each cell In ThisWorkbook.Sheets("DropDownData").Columns("H").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "*@*" Then
            vTo = vTo & cell.Value & ";"
        End If
    Next
    vTo = Split(Left(vTo, Len(vTo) - 1), ";")
    'ActiveWorkbook.SaveAs "Morning Report for a" & Format(Date, "dd mmm")
    ActiveWorkbook.SendMail Recipients:=vTo
End Sub