on error resume next on vba

[earthworm]

Below is the source code

Sub Step5()

If Worksheets("Trigger").Range("E1").Value <> 0 Then

' To Send email along with Attachments from Folder

Dim emailApplication As Object
Dim emailItem As Object

Dim x As Integer
For x = 1 To 3

Dim nme As String
nme = Worksheets("Location").Range("A1")

Dim tomail As String
tomail = Worksheets("Brain").Range("N" & x + 1)

Dim tocc As String
tocc = "test@test.com"

Dim valuedate As String, StrPath As String, ath2 As String
valuedate = (Format(Date, "dd-mmm-yyyy"))
StrPath = (Worksheets("Location").Range("A1")) & "Outward Swift Message" & valuedate & "\"
ath = tomail & "-" & valuedate & ".pdf"
ath2 = ath

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

emailItem.Body = "The message for the email."

With emailItem
        .To = tomail
        .CC = tocc
        '.Bcc = ""
        .subject = "SWIFT Message for Branch " & tomail
        .HTMLBody = "Dear Branch,<br><br>Attached are the SWIFT message of your respective branch for your reference and record.<br><br>Kindly feel free to contact incase of further assistance <br><br>Regards<br>Roomi<br><Style='font-family:calibri;font-size:15'><br><span style=""color:#3366FF""><b>United Bank Limited<br>Remittance Unit</span style=""color:#3366FF""><\b>"
        .Attachments.Add StrPath & ath2
        
'Display email before send

  emailItem.Display
 
End With

 ' Send the Email
 ' emailItem.Send

Set emailItem = Nothing
Set emailApplication = Nothing

Next x

Else
End If

End Sub

Incase of any error in email address being sent to TO or CC , VBA gives an error and stopes . Some email are sent and rest are stuck .

I want to apply if error resume next so that incase of any error identified in email address go to ( run macro again for another x) . in the end i also want to find

what were the email address that had an errors on it . please guide.

 

[Alex Blakenburg]

What line is highlighted when the VBA gives an error and stops and what is the error message ?
Is it a different line that gets highlighted depending on whether the TO or CC is is causing the error ?
If CC is a fixed email address then we can focus on the To address.

Where do you want the do you want the email addresses that had an error to be reported ? If it is only 1 or 2 a message box is ok but other wise we need to output list somewhere or put a flag a column on to the line being processed as being in error eg you are using Column N is Column O free for use ?

 

[earthworm]

What line is highlighted when the VBA gives an error and stops and what is the error message ?
Is it a different line that gets highlighted depending on whether the TO or CC is is causing the error ?
If CC is a fixed email address then we can focus on the To address.

Where do you want the do you want the email addresses that had an error to be reported ? If it is only 1 or 2 a message box is ok but other wise we need to output list somewhere or put a flag a column on to the line being processed as being in error eg you are using Column N is Column O free for use ?

VBA Highlights that line when outlook is trying to send an email but couldn't sent due to incorrect email address.

Incase of any error whether in "to" or "cc" receipts while sending from outlook , Skip that piece / loop and move to the the next x loop .
Only need the list to whom message is not sent in another sheet that will be ok.

 

[Alex Blakenburg]

I have modified your code to trap and report on the errors. See if it does what you need it to do:
Warning: I have commented out Display email and uncommented Send Email.

Sub Step5()

If Worksheets("Trigger").Range("E1").Value <> 0 Then

    ' To Send email along with Attachments from Folder
    
    Dim emailApplication As Object
    Dim emailItem As Object
    
    Dim emailLRow As Long
    Dim arrEmail As Variant, errorCnt As Long
    Dim errorSht As Worksheet, errorName As String
    

    
    'emailLRow = Worksheets("Brain").Range("N" & Rows.Count).End(xlUp).Row      ' Get last row of email address list
    emailLRow = 4                                                               ' For testing set last row to row 4
    ReDim arrEmail(1 To emailLRow - 1, 1 To 3)
    errorName = "Send Errors"
    
    Dim x As Integer
    For x = 2 To emailLRow                                                      ' Changed to use row numbers ie start at 2 and go to last row
    
        Dim nme As String
        nme = Worksheets("Location").Range("A1")
        
        Dim tomail As String
        tomail = Worksheets("Brain").Range("N" & x)
        
        Dim tocc As String
        tocc = "test@test.com"
        
        Dim valuedate As String, StrPath As String, ath2 As String, ath As String
        valuedate = (Format(Date, "dd-mmm-yyyy"))
        StrPath = (Worksheets("Location").Range("A1")) & "Outward Swift Message" & valuedate & "\"
        ath = tomail & "-" & valuedate & ".pdf"
        ath2 = ath
        
        Set emailApplication = CreateObject("Outlook.Application")
        Set emailItem = emailApplication.CreateItem(0)
        
        emailItem.Body = "The message for the email."
        
        With emailItem
                .To = tomail
                .CC = tocc
                '.Bcc = ""
                .Subject = "SWIFT Message for Branch " & tomail
                .HTMLBody = "Dear Branch,<br><br>Attached are the SWIFT message of your respective branch for your reference and record.<br><br>Kindly feel free to contact incase of further assistance <br><br>Regards<br>Roomi<br><Style='font-family:calibri;font-size:15'><br><span style=""color:#3366FF""><b>United Bank Limited<br>Remittance Unit</span style=""color:#3366FF""><\b>"
                .Attachments.Add StrPath & ath2
                
        'Display email before send
        
          'emailItem.Display
         
        End With
        
        On Error Resume Next
            ' Send the Email
            emailItem.Send
            
            If Err <> 0 Then
               errorCnt = errorCnt + 1
               arrEmail(errorCnt, 1) = x
               arrEmail(errorCnt, 2) = tomail
               arrEmail(errorCnt, 3) = tocc
            End If
         On Error GoTo 0
        
        Set emailItem = Nothing
        Set emailApplication = Nothing
    
    Next x
    
Else
End If

If errorCnt <> 0 Then
    If Evaluate("ISREF('" & errorName & "'!A1)") Then
        Application.DisplayAlerts = False
            Worksheets(errorName).Delete
        Application.DisplayAlerts = True
    End If
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = errorName
    Set errorSht = ActiveSheet
    With errorSht
        .Range("A1:C1") = Array("Row", "To", "CC")
        .Range("A1:C1").Resize(errorCnt) = arrEmail
        .Range("A1:C1").EntireColumn.AutoFit
    End With
End If

End Sub

 

[earthworm]

Thanks for the help . Honestly I haven't tested yet . Because I have to make a whole scenario again for which I have already made corrections in email. and this is just a small piece inside that huge macro .

For the learning can you please explain what does this below piece does. especially that Redim part . I am not expert in VBA . But at least I can try to understand what logic you have applied. What does is "As long" variable does ? and why did you use this ? Please also explain why did you use "As Worksheet" ? and through which what will macro do ?

Dim emailLRow As Long
    Dim arrEmail As Variant, errorCnt [B]As Long[/B]
    Dim errorSht [B]As Worksheet[/B], errorName As String
    

    
    'emailLRow = Worksheets("Brain").Range("N" & Rows.Count).End(xlUp).Row      ' Get last row of email address list
    emailLRow = 4                                                               ' For testing set last row to row 4
    ReDim arrEmail(1 To emailLRow - 1, 1 To 3)
    errorName = "Send Errors"
    
    Dim x As Integer
    For x = 2 To emailLRow                                                      ' Changed to use row numbers ie start at 2 and go to last row
    

If Err <> 0 Then
               errorCnt = errorCnt + 1
               arrEmail(errorCnt, 1) = x
               arrEmail(errorCnt, 2) = tomail
               arrEmail(errorCnt, 3) = tocc
            End If
         On Error GoTo 0

 

[Alex Blakenburg]

For the learning can you please explain what does this below piece does. especially that Redim part . I am not expert in VBA . But at least I can try to understand what logic you have applied. What does is "As long" variable does ? and why did you use this ? Please also explain why did you use "As Worksheet" ? and through which what will macro do ?

FYI - To use Bold and other rich text function you need to use the RICH button instead of the VBA button.

I needed to collect the errors including the TO value and the CC value and I opted to use an Array to store them in. I didn't know in advance how many there will be so the maximum number would be the full number of emails you are going to send out, so I have redimmed the array to the full number of email addresses.
Long is a bigger numeric data type than Integer and more efficient since the system converts Integer to Long anyway, so Long is preferred over Integer.
I didn't want to create a new sheet to hold the errors unless some errors were found, so initially I just dimmed a variable as a worksheet until I needed it and set it after the number of errors were found to be greater than 0 at which point I created a new sheet and named it Send Errors (it also deletes the previous version of this sheet if it already exists).

 

[earthworm]

FYI - To use Bold and other rich text function you need to use the RICH button instead of the VBA button.

I needed to collect the errors including the TO value and the CC value and I opted to use an Array to store them in. I didn't know in advance how many there will be so the maximum number would be the full number of emails you are going to send out, so I have redimmed the array to the full number of email addresses.
Long is a bigger numeric data type than Integer and more efficient since the system converts Integer to Long anyway, so Long is preferred over Integer.
I didn't want to create a new sheet to hold the errors unless some errors were found, so initially I just dimmed a variable as a worksheet until I needed it and set it after the number of errors were found to be greater than 0 at which point I created a new sheet and named it Send Errors (it also deletes the previous version of this sheet if it already exists).

Ohh.that make some sense. Redim. What does it do. Can you please explain with some example . In my view redim means to overwrite perviously assigned dim variable

 

[Alex Blakenburg]

You can't use a variable as a sizing parameter inside of a Dim statement eg emailLRow = 5, Dim arr(emailLRow) won't work.
So you set up the array unsized with Dim arr() then emailLRow = 5 and then Redim arr(emailLRow).

Without using the word Preserve, you are correct it will overwrite the previous array but at that point that in my code, the array has not yet been populated so there is nothing to overwrite. I am just trying to size the array dynamically before loading it to a reasonably maximum value without hard coding a number.

If you want to read up on it, see if this links helps. Limitation 1 in the article being that you can only resize the last dimension using the Preserve option is a significant issue since it is generally the row dimension (the 1st dimension) that you are wanting to resize.
Dynamic Array with ReDim Preserve VBA - wellsr.com