Email help...need IF statement for more than one recipient
 

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 = "

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


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=555082

Email help...need IF statement for more than one recipient
 

I am confused

Look on my mail page and post back with a specific question


--
Regards Ron De Bruin
http://www.rondebruin.nl



"tanyhart" wrote in message
...

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 = "

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


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=555082

Email help...need IF statement for more than one recipient
 

Sorry to be confusing.

What I have is a report that shows projects that remain outstanding.
Associated with each project is a zone (or zones) that it is related to
(IE: Central, North, East, West). Each zone has 2 managers that need to
have a reminder sent to them. I have the spreadsheet set up so that the
user just needs to put a checkmark under the zone the project is for.

What I would like to do is be able to generate a reminder email that
will be send to the managers of the zones dependent upon if there is a
checkmark in the zone box.

I know how to have an email genereated, but what I don't know how to
have it address it to the managers in the zones. So for example, if
Zone Central had a checkmark, the email would automatically put the 2
email address in the TO:. If more than one zone is checked, I would
like it to address to the 4 managers responsible and so on.

Any thoughts?


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=555082

Email help...need IF statement for more than one recipient
 

Sorry to be confusing.

What I have is a report that shows projects that remain outstanding.
Associated with each project is a zone (or zones) that it is related to
(IE: Central, North, East, West). Each zone has 2 managers that need to
have a reminder sent to them. I have the spreadsheet set up so that the
user just needs to put a checkmark under the zone the project is for.

What I would like to do is be able to generate a reminder email that
will be send to the managers of the zones dependent upon if there is a
checkmark in the zone box.

I know how to have an email genereated, but what I don't know how to
have it address it to the managers in the zones. So for example, if
Zone Central had a checkmark, the email would automatically put the 2
email address in the TO:. If more than one zone is checked, I would
like it to address to the 4 managers responsible and so on.

Any thoughts?


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=555082