when i use exit sub then VBA not going to next loop

Arun SD 1 Reputation point
2021-02-16T05:46:03.303+00:00

Sub Send_Email_With_Signature()

Dim objOutApp As Object, objOutMail As Object
Dim strBody As String, strSig As String
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("bAckend_file")
Dim i As Integer
Dim last_row As Integer

last_row = Application.CountA(sh.Range("A:A"))
For i = 2 To last_row

Set objOutApp = CreateObject("Outlook.Application")
Set objOutMail = objOutApp.CreateItem(0)

On Error Resume Next
With objOutMail

'SET THE EMAIL CONDITIONS

.to = sh.Range("A" & i).Value
.CC = sh.Range("B" & i).Value
.Subject = sh.Range("C" & i).Value

If Dir(sh.Range("E" & i).Value) <> "" Then
.attachments.Add sh.Range("E" & i).Value
sh.Range("F" & i).Value = "Email create/sent"
Else
sh.Range("F" & i).Value = "Missing invoice copy in the folder"

Exit Sub

End If

'ADD ATTACHMENTS
'.Attachments.Add ("C:\Users\FormatCells\Documents\MyTestDoc.txt")

'IF SENT FROM ANOTHER EMAIL ACCOUNT (MUST ALREADY BE SETUP)
'.SentOnBehalfOfName = "AnotherAccount@FormatCells.com"

'DO NOT REMOVE - THIS MUST BE VISIBLE FIRST TO GET THE DEFAULT SIGNATURE
.Display

'CHECK NAMES, ENSURES INTERNAL EMAIL ADDRESSES EXISTS IN ADDRESS BOOK
.Recipients.ResolveAll

'GET THE HTML CODE FROM THE SIGNATURE
strSig = .Htmlbody

'WHAT SHOULD THE EMAIL SAY, ON TOP OF THE SIGNATURE
'HTML TAGS CAN BE INCLUDED HERE

strBody = sh.Range("D" & i).Value

'COMBINE THE EMAIL WITH THE SIGNATURE
.Htmlbody = strBody & strSig

'IF YOU DO NOT HAVE HTML IN THE BODY, USE THIS INSTEAD
'.Body = strBody & strSig

'AUTOMATICALLY SEND EMAIL (IT WILL STILL BRIEFLY POPUP)
'.Send

End With

On Error GoTo 0
Set objOutMail = Nothing
Set objOutApp = Nothing

Next i

End Sub

0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 106.5K Reputation points
    2021-02-16T09:26:51.063+00:00

    Try something like this:

    . . .
    If Dir(sh.Range("E" & i).Value) = "" Then
       sh.Range("F" & i).Value = "Missing invoice copy in the folder"
    Else
       .attachments.Add sh.Range("E" & i).Value
       sh.Range("F" & i).Value = "Email create/sent"
    
       'ADD ATTACHMENTS
       '.Attachments.Add ("C:\Users\FormatCells\Documents\MyTestDoc.txt")
    
       ' SENT FROM ANOTHER EMAIL ACCOUNT (MUST ALREADY BE SETUP)
        . . .
    
    
       'AUTOMATICALLY SEND EMAIL (IT WILL STILL BRIEFLY POPUP)
       '.Send
    
    End If
    . . .
    
    0 comments No comments