E-mail if Cell B33 = Yes

J

justin.arnold2

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
 
D

Don Guillett

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

If ucase(Worksheets("Sheet1").Range("B33")) = "YES" Then
 
J

justin.arnold2

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email on value change (Formula) 6
Send Email 4
Sending e-mails from specific email address 2
Mail Multiple Sheets via PDF Q 5
Converting an Excel file to PDF Q 1
Send Email VBA 3
EMAIL 1
?????? 2

Top