0
Anula
Voturi
Bună,
I'm new to VBA. I wrote a code to send an email if there is a cell value change in column F, and the code works great. But I want to make sure the workbook is saved prior to sending out the email. The email text body must include the reference cell value, which is the value from column A of value changed row. Please do not use msg, it will not work with the workbook. The workbook has a form and it will load the input value to the designated cells. The code works fine with the form. But if someone goes into the sheet and manually update the value, the email will send out to users, which I don't want until the file is saved. Please help! Thanks in advance.
Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
Dim s1, s2, s3, s4, s5, s6 As Range
Set s1 = Range("F1310:F1334")
Set s2 = Range("F1426:F1450")
Set s3 = Range("F1339:F1363")
Set s4 = Range("F1455:F1479")
Set s5 = Range("F1368:F1392")
Set s6 = Range("F1397:F1421")
La data de eroare CV următoare
Dacă Target.Cells.Count > 1, apoi Ieșiți din sub
'find value changed in column F
If Intersect(Target, Union(s1, s2, s3, s4, s5, s6)) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value <> "" Then
Dim xOutApp ca obiect
Dim xOutMail ca obiect
Dim xMailBody ca șir
Dim xMailText As String
Setați xOutApp = CreateObject("Outlook.Application")
Setați xOutMail = xOutApp.CreateItem(0)
'index value from column A of the row that value changed to included in mail body
xMailText = Target.Offset(, -5).Value
xMailBody = „Bună ziua” & vbNewLine & vbNewLine & _
"Invoice Received for " & xMailText & vbNewLine & vbNewLine & _
"Thanks" & vbNewLine & vbNewLine & _
"Mr. J"
La data de eroare CV următoare
Cu xOutMail
.Pentru = ""
.CC = ""
.BCC = ""
.Subject = "Invoice Received"
.Body = xMailBody
.Trimite
Se termina cu
La eroare GoTo 0
Setați xOutMail = Nimic
Setați xOutApp = Nimic
Final, dacă
End Sub
I'm new to VBA. I wrote a code to send an email if there is a cell value change in column F, and the code works great. But I want to make sure the workbook is saved prior to sending out the email. The email text body must include the reference cell value, which is the value from column A of value changed row. Please do not use msg, it will not work with the workbook. The workbook has a form and it will load the input value to the designated cells. The code works fine with the form. But if someone goes into the sheet and manually update the value, the email will send out to users, which I don't want until the file is saved. Please help! Thanks in advance.
Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
Dim s1, s2, s3, s4, s5, s6 As Range
Set s1 = Range("F1310:F1334")
Set s2 = Range("F1426:F1450")
Set s3 = Range("F1339:F1363")
Set s4 = Range("F1455:F1479")
Set s5 = Range("F1368:F1392")
Set s6 = Range("F1397:F1421")
La data de eroare CV următoare
Dacă Target.Cells.Count > 1, apoi Ieșiți din sub
'find value changed in column F
If Intersect(Target, Union(s1, s2, s3, s4, s5, s6)) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value <> "" Then
Dim xOutApp ca obiect
Dim xOutMail ca obiect
Dim xMailBody ca șir
Dim xMailText As String
Setați xOutApp = CreateObject("Outlook.Application")
Setați xOutMail = xOutApp.CreateItem(0)
'index value from column A of the row that value changed to included in mail body
xMailText = Target.Offset(, -5).Value
xMailBody = „Bună ziua” & vbNewLine & vbNewLine & _
"Invoice Received for " & xMailText & vbNewLine & vbNewLine & _
"Thanks" & vbNewLine & vbNewLine & _
"Mr. J"
La data de eroare CV următoare
Cu xOutMail
.Pentru = ""
.CC = ""
.BCC = ""
.Subject = "Invoice Received"
.Body = xMailBody
.Trimite
Se termina cu
La eroare GoTo 0
Setați xOutMail = Nimic
Setați xOutApp = Nimic
Final, dacă
End Sub
Nu există răspunsuri făcute pentru acest post.