T
tanyhart
I need some help with a macro I am creating. I have the following code
to create an email
Code:
--------------------
Sub Reminder()
Dim myOutlook As Object
Dim myMailItems As Object
Dim strbody As String
Dim recipient As String
With ThisWorkbook.ActiveSheet
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMainItem)
strbody = "This is a reminder"
End With
'Central
If ActiveSheet.Range("E6:E2000") = "a" Then recipient = "(e-mail address removed)"
With OutMail
.To = recipient
.CC = ""
.BCC = ""
.Subject = "Reminder"
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
--------------------
If you look at the attachment, I will have various sheets with
Estimators initials (KM is just one sample). What I would like to do
is create a macro that will run on the active sheet (so whomever is
looking at their own report can run the email on their data only), and
send emails to those with days remaining <5. As well, I would like it
to send to the specialists who are associated with the zones that have
checkmarks. I also have the specialists listed on a separate sheet
with their email addresses.
I was thinking about using an IF statement, but not sure how I would do
that. Any help would be much appreciated.
Thanks
to create an email
Code:
--------------------
Sub Reminder()
Dim myOutlook As Object
Dim myMailItems As Object
Dim strbody As String
Dim recipient As String
With ThisWorkbook.ActiveSheet
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMainItem)
strbody = "This is a reminder"
End With
'Central
If ActiveSheet.Range("E6:E2000") = "a" Then recipient = "(e-mail address removed)"
With OutMail
.To = recipient
.CC = ""
.BCC = ""
.Subject = "Reminder"
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
--------------------
If you look at the attachment, I will have various sheets with
Estimators initials (KM is just one sample). What I would like to do
is create a macro that will run on the active sheet (so whomever is
looking at their own report can run the email on their data only), and
send emails to those with days remaining <5. As well, I would like it
to send to the specialists who are associated with the zones that have
checkmarks. I also have the specialists listed on a separate sheet
with their email addresses.
I was thinking about using an IF statement, but not sure how I would do
that. Any help would be much appreciated.
Thanks