单元格值更改时，VBA发送电子邮件 [英] VBA Send Email When Cell Value Changes
本文介绍了单元格值更改时，VBA发送电子邮件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
需要找到一种方法来抵消电子邮件正文中的单元格值.对于无论哪个单元格值触发电子邮件(意味着已经达到我们的目标)，我都想将相应的行值返回到左侧的12列.您会在我的代码中看到我使用了Target.Offset(0，-12)，但这返回了错误.我希望这是有道理的.
Private Sub Worksheet_Calculate()
Dim Target As Range
With Me
Set Target = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))
End With
If (Range("N45") = Range("F45")) Or (Range("N46") = Range("F46")) Or (Range("N47") = Range("F47")) Or (Range("N48") = Range("F48")) Or (Range("N50") = Range("F50")) Or (Range("N51") = Range("F51")) Or (Range("N52") = Range("F53")) Or (Range("N55") = Range("F55")) Or (Range("N56") = Range("F56")) Or (Range("N57") = Range("F57")) Or (Range("N58") = Range("F58")) Or (Range("N59") = Range("F59")) Or (Range("N61") = Range("F61")) Or (Range("N62") = Range("F62")) Or (Range("N63") = Range("F63")) Or (Range("N65") = Range("F65")) Or (Range("N66") = Range("F66")) Or (Range("N67") = Range("F67")) Or (Range("N68") = Range("F68")) Or (Range("N70") = Range("F70")) Or (Range("N71") = Range("F71")) Or (Range("N73") = Range("F73")) Or (Range("N74") = Range("F74")) Or (Range("N75") = Range("F75")) Or (Range("N76") = Range("F76")) Or (Range("N77") = Range("F77")) Or (Range("N79") = Range("F79")) Or (Range("N80") = Range("F80")) Or (Range("N81") = Range("F81")) Or (Range("N83") = Range("F83")) And _
(Range("N84") = Range("F84")) Or (Range("N85") = Range("F85")) Or (Range("N87") = Range("F87")) Or (Range("N88") = Range("F88")) Or (Range("N89") = Range("F89")) Or (Range("N91") = Range("F91")) Or (Range("N92") = Range("F92")) Or (Range("N93") = Range("F93")) Or (Range("N95") = Range("F95")) Or (Range("N96") = Range("F96")) Or (Range("N97") = Range("F97")) Or (Range("N99") = Range("F99")) Or (Range("N100") = Range("F100")) Or (Range("N101") = Range("F101")) Or (Range("N103") = Range("F103")) Or (Range("N104") = Range("F104")) Or (Range("N105") = Range("F105")) Or (Range("N106") = Range("F106")) Or (Range("N108") = Range("F108")) Or (Range("N109") = Range("F109")) Or (Range("N110") = Range("F110")) Or (Range("N111") = Range("F111")) Or (Range("N113") = Range("F113")) Or (Range("N114") = Range("F114")) Or (Range("N115") = Range("F115")) Or (Range("N116") = Range("F116")) Or (Range("N117") = Range("F117")) Or (Range("N118") = Range("F118")) Or (Range("N121") = Range("F121")) And _
(Range("N122") = Range("F122")) Or (Range("N123") = Range("F123")) Or (Range("N124") = Range("F124")) Or (Range("N125") = Range("F125")) Or (Range("N127") = Range("F127")) Or (Range("N128") = Range("F128")) Or (Range("N132") = Range("F132")) Or (Range("F134") = Range("N134")) Or (Range("N136") = Range("F136")) Or (Range("N138") = Range("F138")) Or (Range("N140") = Range("F140")) Or (Range("N142") = Range("F142")) And _
(Range("N145") = Range("F145")) Or (Range("N146") = Range("F146")) Or (Range("N147") = Range("F147")) Or (Range("N148") = Range("F148")) Or (Range("N149") = Range("F149")) Or (Range("N150") = Range("F150")) Or (Range("N153") = Range("F153")) Or (Range("N154") = Range("F154")) Or (Range("N156") = Range("F156")) Or (Range("N157") = Range("F157")) Or (Range("N159") = Range("F159")) Or (Range("N160") = Range("F160")) Or (Range("N161") = Range("F161")) Or (Range("N162") = Range("F162")) Or (Range("N163") = Range("F163")) Or (Range("N164") = Range("F164")) Or (Range("N166") = Range("F166")) Or (Range("N167") = Range("F167")) Or (Range("N168") = Range("F168")) Or (Range("N169") = Range("F169")) Or (Range("N170") = Range("F170")) Or (Range("N171") = Range("F171")) Or (Range("N173") = Range("F173")) Or (Range("N174") = Range("F174")) Or (Range("N175") = Range("F175")) Or (Range("N176") = Range("F176")) Or (Range("N177") = Range("F177")) Or (Range("N178") = Range("F178")) And _
(Range("N180") = Range("F180")) Or (Range("N182") = Range("F182")) Or (Range("N184") = Range("F184")) Or (Range("N185") = Range("F185")) Or (Range("N186") = Range("F186")) Or (Range("N187") = Range("F187")) Or (Range("N188") = Range("F188")) Or (Range("N189") = Range("F189")) Or (Range("N191") = Range("F191")) Or (Range("N192") = Range("F192")) Or (Range("N193") = Range("F193")) Or (Range("N195") = Range("F195")) Or (Range("N196") = Range("F196")) Or (Range("N197") = Range("F197")) Or (Range("N198") = Range("F198")) Or (Range("N199") = Range("F199")) Or (Range("N200") = Range("F200")) Or (Range("N201") = Range("F201")) Or (Range("N202") = Range("F202")) Or (Range("N203") = Range("F203")) Or (Range("N205") = Range("F205")) Or (Range("N206") = Range("F206")) Or (Range("N207") = Range("F207")) Or (Range("N208") = Range("F208")) Or (Range("N209") = Range("F209")) Or (Range("N210") = Range("F210")) Or (Range("N211") = Range("F211")) Or (Range("N212") = Range("F212")) And _
(Range("N213") = Range("F213")) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
Target.Offset(0, -12) & " has reached its target"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "Target Reached"
.Body = xMailBody
.Send 'or use .Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
在这里，我使用了Worksheet_Change事件，并且Offset工作正常.
Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Target, Range("D1:D99"))
If xRg Is Nothing Then Exit Sub
If (Range("D7") > Range("E7")) Or (Range("D8") > Range("E8")) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
xRg.Offset(0, -3) & " has reached its target"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "Target Reached"
.Body = xMailBody
.Send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
未定义Mail_small_Text_Outlook中的
解决方案
Target.Offset(0, -12).您不能在一个子例程中设置一个变量，然后在另一个子例程中使用它，除非该变量已全局定义(在所有子例程和函数之外)，或者已作为参数传递给该子例程.
请尝试:
Sub Mail_small_Text_Outlook(Target as Range)
然后调用该子例程，如:
Call Mail_small_Text_Outlook(Target)
因此，您要将变量Target传递给Mail_small_Text_Outlook子例程.
或者将Target设置为全局变量，以便VBAProject中的子例程和函数可以使用它(在这种情况下，我不会这样做).您将添加到代码的顶部(在worksheet_calculate例程之上):
Public Target as Range
然后在worksheet_calculate例程中删除对Target的声明:
'Dim Target As Range
但是，如果这样做，请更改变量的名称，因为还有其他内置的子例程，例如Worksheet_Change()，这些子例程具有Target作为内置变量，并且作用域将变得很奇怪. /p>
Need to find a way to offset the cell value in the email body. For whichever cell value triggers the email (meaning our target has been reached) I want to return the corresponding row value 12 columns to the left. You'll see in my code I've used Target.Offset(0, -12) but this is returning an error. I hope that makes sense.
Private Sub Worksheet_Calculate()
Dim Target As Range
With Me
Set Target = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))
End With
If (Range("N45") = Range("F45")) Or (Range("N46") = Range("F46")) Or (Range("N47") = Range("F47")) Or (Range("N48") = Range("F48")) Or (Range("N50") = Range("F50")) Or (Range("N51") = Range("F51")) Or (Range("N52") = Range("F53")) Or (Range("N55") = Range("F55")) Or (Range("N56") = Range("F56")) Or (Range("N57") = Range("F57")) Or (Range("N58") = Range("F58")) Or (Range("N59") = Range("F59")) Or (Range("N61") = Range("F61")) Or (Range("N62") = Range("F62")) Or (Range("N63") = Range("F63")) Or (Range("N65") = Range("F65")) Or (Range("N66") = Range("F66")) Or (Range("N67") = Range("F67")) Or (Range("N68") = Range("F68")) Or (Range("N70") = Range("F70")) Or (Range("N71") = Range("F71")) Or (Range("N73") = Range("F73")) Or (Range("N74") = Range("F74")) Or (Range("N75") = Range("F75")) Or (Range("N76") = Range("F76")) Or (Range("N77") = Range("F77")) Or (Range("N79") = Range("F79")) Or (Range("N80") = Range("F80")) Or (Range("N81") = Range("F81")) Or (Range("N83") = Range("F83")) And _
(Range("N84") = Range("F84")) Or (Range("N85") = Range("F85")) Or (Range("N87") = Range("F87")) Or (Range("N88") = Range("F88")) Or (Range("N89") = Range("F89")) Or (Range("N91") = Range("F91")) Or (Range("N92") = Range("F92")) Or (Range("N93") = Range("F93")) Or (Range("N95") = Range("F95")) Or (Range("N96") = Range("F96")) Or (Range("N97") = Range("F97")) Or (Range("N99") = Range("F99")) Or (Range("N100") = Range("F100")) Or (Range("N101") = Range("F101")) Or (Range("N103") = Range("F103")) Or (Range("N104") = Range("F104")) Or (Range("N105") = Range("F105")) Or (Range("N106") = Range("F106")) Or (Range("N108") = Range("F108")) Or (Range("N109") = Range("F109")) Or (Range("N110") = Range("F110")) Or (Range("N111") = Range("F111")) Or (Range("N113") = Range("F113")) Or (Range("N114") = Range("F114")) Or (Range("N115") = Range("F115")) Or (Range("N116") = Range("F116")) Or (Range("N117") = Range("F117")) Or (Range("N118") = Range("F118")) Or (Range("N121") = Range("F121")) And _
(Range("N122") = Range("F122")) Or (Range("N123") = Range("F123")) Or (Range("N124") = Range("F124")) Or (Range("N125") = Range("F125")) Or (Range("N127") = Range("F127")) Or (Range("N128") = Range("F128")) Or (Range("N132") = Range("F132")) Or (Range("F134") = Range("N134")) Or (Range("N136") = Range("F136")) Or (Range("N138") = Range("F138")) Or (Range("N140") = Range("F140")) Or (Range("N142") = Range("F142")) And _
(Range("N145") = Range("F145")) Or (Range("N146") = Range("F146")) Or (Range("N147") = Range("F147")) Or (Range("N148") = Range("F148")) Or (Range("N149") = Range("F149")) Or (Range("N150") = Range("F150")) Or (Range("N153") = Range("F153")) Or (Range("N154") = Range("F154")) Or (Range("N156") = Range("F156")) Or (Range("N157") = Range("F157")) Or (Range("N159") = Range("F159")) Or (Range("N160") = Range("F160")) Or (Range("N161") = Range("F161")) Or (Range("N162") = Range("F162")) Or (Range("N163") = Range("F163")) Or (Range("N164") = Range("F164")) Or (Range("N166") = Range("F166")) Or (Range("N167") = Range("F167")) Or (Range("N168") = Range("F168")) Or (Range("N169") = Range("F169")) Or (Range("N170") = Range("F170")) Or (Range("N171") = Range("F171")) Or (Range("N173") = Range("F173")) Or (Range("N174") = Range("F174")) Or (Range("N175") = Range("F175")) Or (Range("N176") = Range("F176")) Or (Range("N177") = Range("F177")) Or (Range("N178") = Range("F178")) And _
(Range("N180") = Range("F180")) Or (Range("N182") = Range("F182")) Or (Range("N184") = Range("F184")) Or (Range("N185") = Range("F185")) Or (Range("N186") = Range("F186")) Or (Range("N187") = Range("F187")) Or (Range("N188") = Range("F188")) Or (Range("N189") = Range("F189")) Or (Range("N191") = Range("F191")) Or (Range("N192") = Range("F192")) Or (Range("N193") = Range("F193")) Or (Range("N195") = Range("F195")) Or (Range("N196") = Range("F196")) Or (Range("N197") = Range("F197")) Or (Range("N198") = Range("F198")) Or (Range("N199") = Range("F199")) Or (Range("N200") = Range("F200")) Or (Range("N201") = Range("F201")) Or (Range("N202") = Range("F202")) Or (Range("N203") = Range("F203")) Or (Range("N205") = Range("F205")) Or (Range("N206") = Range("F206")) Or (Range("N207") = Range("F207")) Or (Range("N208") = Range("F208")) Or (Range("N209") = Range("F209")) Or (Range("N210") = Range("F210")) Or (Range("N211") = Range("F211")) Or (Range("N212") = Range("F212")) And _
(Range("N213") = Range("F213")) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
Target.Offset(0, -12) & " has reached its target"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "Target Reached"
.Body = xMailBody
.Send 'or use .Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Here I have used the Worksheet_Change event and the Offset works fine.
Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Target, Range("D1:D99"))
If xRg Is Nothing Then Exit Sub
If (Range("D7") > Range("E7")) Or (Range("D8") > Range("E8")) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
xRg.Offset(0, -3) & " has reached its target"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "Target Reached"
.Body = xMailBody
.Send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
解决方案
Target.Offset(0, -12) in your Mail_small_Text_Outlook isn't defined. You can't set a variable in one subroutine and then use it in another unless that variable has been defined globally (outside of all subroutines and functions) or it is passed as a parameter to the subroutine.
Try instead:
Sub Mail_small_Text_Outlook(Target as Range)
Then calling that subroutine like:
Call Mail_small_Text_Outlook(Target)
With that you are passing the variable Target to the Mail_small_Text_Outlook subroutine.
Alternatively to set Target as a global variable so it is available to subroutines and functions in your VBAProject (I wouldn't in this case for reasons). You add to the top of your code (above your worksheet_calculate routine):
Public Target as Range
Then remove your declaration of Target in your worksheet_calculate routine:
'Dim Target As Range
Although, if you do this, then change the name of the variable since there are other built in subroutines like Worksheet_Change() that have Target as a built in variable and the scope is going to get all weird then.
这篇关于单元格值更改时，VBA发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
