vba - Send e-mail dependant on specific cell contents -
i novice vba programmer , have searched unable find solution matches need.
i have code ping out customer's ip address need e-mail notifications pings have timed out.
the ping results in column d , e-mails in column e of spreadsheet. i'd grateful help.
thanks in advance.
dim outlookapp dim objmail dim x long dim pingresults range lastrow = sheets("ping").cells(rows.count, 1).end(xlup).row x = 2 lastrow set pingresults = range("d2:d250") set outlookapp = createobject("outlook.application") set objmail = outlookapp.createitem(olmailitem) if pingresults.cells.value = "request timed out." objmail.to = cells(x, 5).value objmail .subject = cells(x, 1) & " " & "-" & " " & cells(x, 2) & " " & "-" & " " & cells(x, 3) .body = "run diagnostics. customer's broadband appears have issues" & vbcrlf & cells(x, 4) .display .save end sendkeys "%{s}", true elseif pingresults.cells.value = "" set outlookapp = nothing set objmail = nothing end if end sub
you after this:
option explicit sub main() dim pingresults range, cell range sheets("ping") .range("d1", .cells(.rows.count, "d").end(xlup)) .autofilter field:=1, criteria1:="request timed out." if application.worksheetfunction.subtotal(103, .cells) > 1 set pingresults = .resize(.rows.count - 1).offset(1).specialcells(xlcelltypevisible) end .autofiltermode = false end if not pingresults nothing createobject("outlook.application") each cell in pingresults .createitem(0) '<--| olmailitem item of outlook enumeration value "zero" .display .to = cell.offset(, 1).value .subject = cell.offset(, -3) & " " & "-" & " " & cell.offset(, -2) & " " & "-" & " " & cell.offset(, -1) .body = "run diagnostics. customer's broadband appears have issues" & vbcrlf & cell.value .save end sendkeys "%{s}", true next .quit end end if end sub
Comments
Post a Comment