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.
