Hi Jatinder,, PFA
Hope it will help you.....not 100% but u take an idea about the same.. Thanks & regards, www.MrExcel4U.com <http://mrexcel4u.blogspot.com/> On Mon, Feb 17, 2014 at 3:09 PM, Jitender Saini <jeetusain...@gmail.com>wrote: > Hi Friends, > > Need your help to modify this macro as i m new to VBA. > > I have a macro in which we can mail through excel sheet (having a specific > text in last column and row). It's working perfect as i want but now i want > to modify this according to my another need. > > This macro send the mail to customer if last column having "Yes". But i > want that it will the mail to customer only if last row having "Yes" in > last column (means last cell of the column and row). > > In another way, the data is row wise, every time a new data is inserted in > row with status, if last row have "Yes" status in last cell of the last > column it will send the mail , if having "No" then no mail will send. and > When next data will enter in row then will check it again for status "Yes" > and "No", and if found "Yes" send the mail. > > I hope i clarify this clearly. Please Help > > [code] > > Option Explicit > > Sub Send_Mail() > Dim OutApp As Object > Dim OutMail As Object > Dim LR As Long, LC As Long > Dim ws As Worksheet > Dim rng As Range, cel As Range > Dim strBody As String > Dim x As String > > Set OutApp = CreateObject("Outlook.Application") > Set ws = Sheets("PartsData") > Application.ScreenUpdating = False > With ws > LC = .Cells.Find(What:="*", SearchOrder:=xlByColumns, _ > SearchDirection:=xlPrevious).Column > LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), > SearchOrder:=xlByRows, _ > SearchDirection:=xlPrevious).Row > .Range(.Cells(1, 2), .Cells(LR, LC)).AutoFilter field:=LC - 1, > Criteria1:="Yes" > Set rng = .AutoFilter.Range > x = rng.Columns(30).SpecialCells(xlCellTypeVisible).Count - 1 > If x >= 1 Then > For Each cel In .Range(.Cells(LC, 30), .Cells(LR, > 30)).SpecialCells(xlCellTypeLastCell) > > 'For Each cel In .Range(.Cells(2, 30), .Cells(LR, > 30)).SpecialCells(xlCellTypeVisible) > strBody = "Dear Valuable Customer," & vbCrLf & vbCrLf _ > & "Greetings from XYZ!!!" & vbCrLf & vbCrLf _ > & "We thank you for giving us an opportunity to serve > you." & vbCrLf & vbCrLf _ > & "We have noted your concern and your request will > be resolved within 7 working days." & vbCrLf & vbCrLf _ > & "Assuring you of our best services always." & > vbCrLf & vbCrLf _ > & "Your reference number for raised query & future > communication is :- " & ws.Cells(cel.Row, "C").Value & vbCrLf & vbCrLf & > vbCrLf _ > & "Best Wishes," & vbCrLf _ > & "Customer Care Team" & vbCrLf _ > & vbCrLf & "XYZ" _ > > > Set OutMail = OutApp.CreateItem(0) > > On Error Resume Next > With OutMail > .to = ws.Cells(cel.Row, "I").Value > .CC = "" > .BCC = "" > .Subject = "Auto Reply : Reference No. - " & > ws.Cells(cel.Row, "C").Value > .Body = strBody > .Display '.Send 'or use .Display > End With > On Error GoTo 0 > > Next cel > End If > .AutoFilterMode = False > End With > > Set OutMail = Nothing > Set OutApp = Nothing > Application.ScreenUpdating = True > End Sub > [/code] > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/groups/opt_out. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Send Email with attachment.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
