E-mail if Cell B33 = Yes
 

Here is a description of what I'm trying to accomplish:

I have a workbook that contains one sheet (sheet 1). This sheet has
formulas that retreive data from an external source via an Excel Add
In. When the sheet is opened the calculations are made. If the
calculations made do not meet specifications I would like to send an e-
mail alert via Outlook informing the correct users. In this particular
sheet cell B33 will display a No if the e-mail alert does not need to
be sent and Yes if the alert needs to be sent. I would like to create
one macro that does both the data verification (check for Yes or No)
and send the e-mail. I would like this macro to run automatically when
the workbook is opened. I then plan to set it up as a scheduled task
to run every 8 hours.

I think that my e-mail code is correct but I am struggling with to Yes/
No verification part. I also would like to know how to run the macro
automatically when the workbook is opened. Below you'll find the code
that I have for the one macro that I have created. Would anyone be
able to help me with this? I am a newbie to Excel/VBA so forgive me
for my ignorance.

Thanks

Justin


Here is my code so far.....

Sub modEmail()

If Worksheets("Sheet1").Range("B33") = Yes Then

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strto = "My E-mail Address"
strcc = ""
strbcc = ""
strsub = "My Subject"
strbody = "This is an automated message."

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End If
Close Workbook

Else
Exit Sub

End Sub

E-mail if Cell B33 = Yes
 

"Yes"
or to account for case yes, YES, etc

If ucase(Worksheets("Sheet1").Range("B33")) = "YES" Then
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
Here is a description of what I'm trying to accomplish:

I have a workbook that contains one sheet (sheet 1). This sheet has
formulas that retreive data from an external source via an Excel Add
In. When the sheet is opened the calculations are made. If the
calculations made do not meet specifications I would like to send an e-
mail alert via Outlook informing the correct users. In this particular
sheet cell B33 will display a No if the e-mail alert does not need to
be sent and Yes if the alert needs to be sent. I would like to create
one macro that does both the data verification (check for Yes or No)
and send the e-mail. I would like this macro to run automatically when
the workbook is opened. I then plan to set it up as a scheduled task
to run every 8 hours.

I think that my e-mail code is correct but I am struggling with to Yes/
No verification part. I also would like to know how to run the macro
automatically when the workbook is opened. Below you'll find the code
that I have for the one macro that I have created. Would anyone be
able to help me with this? I am a newbie to Excel/VBA so forgive me
for my ignorance.

Thanks

Justin


Here is my code so far.....

Sub modEmail()

If Worksheets("Sheet1").Range("B33") = Yes Then

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strto = "My E-mail Address"
strcc = ""
strbcc = ""
strsub = "My Subject"
strbody = "This is an automated message."

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End If
Close Workbook

Else
Exit Sub

End Sub

E-mail if Cell B33 = Yes
 

I changed my code to the following, does it look correct? Thanks for
your help.

Sub modEmail()

If Worksheets("Sheet1").Range("B33") = "Yes" Then

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strto = "My E-mail Address"
strcc = ""
strbcc = ""
strsub = "CSU Inventory Gain"
strbody = "This is an automated message."

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If
Close Workbook

Else
Exit Sub
Close Workbook

End Sub