PDA

View Full Version : Pls correct my code (sending mails)



syed_iqbal
01-19-2017, 01:00 AM
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
01-19-2017, 01:41 AM
one criterion, two criteria.
criteria's is wrong, please,correct it.

syed_iqbal
01-19-2017, 02:31 AM
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
01-19-2017, 07:34 AM
what snb referring to is :
criterion is singular
criteria is plural

snb
01-19-2017, 07:44 AM
@JKwan

I fear your hope is in vain.

Paul_Hossler
01-19-2017, 08:36 AM
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