Try this approach. Instead of notifying you of the error in VBA, this process will just continue sending emails, but you won't know that their is a bad email if the internal check doesn't catch the error. I'm assuming the email error you received is generated through VBA, but if it is generated by Outlook, we may be out of luck.
Option Explicit
Sub emailattachpdffile()
Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim lr As Integer
Dim Path As String
Application.ScreenUpdating = False
lr = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
On Error Resume Next
For i = 2 To Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
If Not IsEmail(Sheet1.Cells(i, "B").Value) Then
Sheet1.Cells(i, "D").Value = "Bad Email"
GoTo skip
End If
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
If (IsEmpty(Cells(i, "D").Value) Or Cells(i, "D").Value = "N") _
And Sheet1.Cells(i, "C").Value <= Date Then 'Check for date
With OutMail
.To = Sheet1.Cells(i, "B").Value
.CC = ""
.Subject = "Enter Subject here" '& Sheet1.Cells(i, "B")
.HTMLBody = "Dear " & Cells(i, "A") & "," & Chr(11) & Chr(11) & "Please find the full Final Settlement." & Chr(11) & Chr(11) & "Thanks and Regards" & Chr(11) & Chr(11) & .HTMLBody
'.Attachments.Add
.Display 'DELETE THIS LINE IF USING SEND
'.send
End With
Sheet1.Cells(i, "E").Value = Date
Sheet1.Cells(i, "D").Value = "Y"
Set OutMail = Nothing
Set OutApp = Nothing
End If
skip:
Next i
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
Exit Sub
erHandle:
MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
On Error GoTo -1
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
Function IsEmail(ByVal s As String) As Boolean
Dim x As Long, AtSign As Long, Parts() As String
Dim NotLocale As String, NotDomain As String
NotLocale = "*[!A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]*"
NotDomain = "*[!A-Za-z0-9._-]*"
Parts = Split(s, "@")
If UBound(Parts) <> 1 Then Exit Function
If Parts(0) Like NotLocale Then Exit Function
If Parts(1) Like NotDomain Then Exit Function
IsEmail = True
End Function
Bookmarks