Pls correct my code (sending mails)

[syed_iqbal]

Hi,


pls go through code in the attached file. there are three criteria's in the code.

Criteria:
1) if employee status is "Resigned" ("Status" column mentioned in "Master" sheet) then mail should not go to that person.
2) if employee status is "Working" and there is no individual attachment for him, then mail should go with remaining all attachments (attachment file path's are mentioned in 'column F' in "Emp Information" Sheet).
3) if employee status is "Working" and there is individual attachment, then mail should go with remaining all attachments (attachment file path mentioned in each individual cell in 'Column F' in "Emp Information" sheet) including individual attachment (attachment file path mentioned in each individual cell in 'Column E' in "Emp Information" sheet).

Examples:

1)Employee's A1,A5,A9,A10,A15 are resigned. So they should not get email.
2) Employees's A3,A12 are Working and there is no file to attach for them (Column E in "Emp information" sheet). then Mail should go with remaining all attachments in column F in "Emp information" Sheet ("Covering letter.txt","1.txt")
3) for employees A2,A4,A6,A7,A8,A9,A11,A13,A14 , mail should go with all attachments (individual & remaining files)

I hope criteria's are clear.

i can't understand where the mistake was occurred in my code. Pls help me

Thank you in advance

regards
iqbal

[snb]

one criterion, two criteria.
criteria's is wrong, please,correct it.

[syed_iqbal]

Hi snb,

I clearly explained all 3 criteria's in my original post. pls go through it. if any confusion in my post, pls reply me.


regards
iqbal

[JKwan]

what snb referring to is :
criterion is singular
criteria is plural

[snb]

@JKwan

I fear your hope is in vain.

[Paul_Hossler]

Skipping the non-productive word-smithing ...

I thought the criteria were well specified and easy to follow


1. I wouldn't use On Error Resume Next unless really necessary, and even then only for a very limited time. It hides errors and you can't see what should be fixed

2. You had

If Sheets("Master").Cells(i, 3).Value = "resigned" Then

but the worksheet had "Resigned" so that statement would never be true


3. Instead of the Do While / Loop which uses counters, etc. I suggest a For Each / Next to just walk down the rows in Master

4. To avoid a lot of nested If / Else / End If what I find makes the code more readable, and therefore more easily debugged, is to use something like

            If .Row = 1 Then GoTo NextEmp        ' skip first row
            If UCase(.Cells(1, 3).Value) = "RESIGNED" Then GoTo NextEmp     '   skip if not WORKING

inside the loop



This is not tested and I haven't tried to send any mail, but it might be a good starting point

Option Explicit

'Criteria:
'1) if employee status is "Resigned" ("Status" column mentioned in "Master" sheet) then mail should not go to that person.
'2) if employee status is "Working" and there is no individual attachment for him, then mail should go with remaining all
'       attachments (attachment file path's are mentioned in 'column F' in "Emp Information" Sheet).
'3) if employee status is "Working" and there is individual attachment, then mail should go with remaining all
'       attachments (attachment file path mentioned in each individual cell in 'Column F' in "Emp Information" sheet) including individual attachment (attachment file path mentioned in each individual cell in 'Column E' in "Emp Information" sheet).
'Examples:
' 1)Employee's A1,A5,A9,A10,A15 are resigned. So they should not get email.
' 2) Employees's A3,A12 are Working and there is no file to attach for them (Column E in "Emp information" sheet). then Mail should go with remaining all attachments in column F in "Emp information" Sheet ("Covering letter.txt","1.txt")
' 3) for employees A2,A4,A6,A7,A8,A9,A11,A13,A14 , mail should go with all attachments (individual & remaining files)

Sub send_email_with_attachments()
    Dim rNames As Range, rEmp As Range
    Dim wsInfo As Worksheet
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    
    Set rNames = Worksheets("Master").Cells(1, 1)
    Set wsInfo = Worksheets("Emp INformation")
    
    Set olApp = New Outlook.Application
    
    For Each rEmp In rNames.Rows
        With rEmp
            If .Row = 1 Then GoTo NextEmp        ' skip first row
            If UCase(.Cells(1, 3).Value) = "RESIGNED" Then GoTo NextEmp     '   skip if not WORKING
            
            Set olMail = New olApp.CreateItem(olMailItem)     ' Added the New, it's not in the attachment
            olMail.To = Cells(.Row, 2).Value        '   .Row = the row number for each rEmp as you loop down
            olMail.Subject = Cells(.Row, 3).Value
            olMail.Body = Cells(.Row, 4).Value
            
            If Len(wsInfo.Cells(.Row, 5).Value) > 0 Then    ' indiv attachment
                olMail.Attachments.Add wsInfo.Cells(.Row, 5).Value
            End If
    
            If Len(wsInfo.Cells(.Row, 6).Value) > 0 Then    ' remaining attachment
                olMail.Attachments.Add wsInfo.Cells(.Row, 6).Value
            End If
    
            olMail.Display
        
            Set olMail = Nothing
        End With
    
NextEmp:
    Next
End Sub