E-mail Macro

[lmusn89]

Good afternoon,

I'm working on a Macro to email various POCs. Currently I've got one tab that I'm working off of, but ultimately I'd like to have multiple tabs and have the macro look for the data on each tab. Here's the code:

Sub Email()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Dim strbody As String
For Each cell In Range("G1:G76")
strbody = strbody & cell.Value & vbNewLine
Next
On Error GoTo cleanup
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
If LCase(Cells(cell.Row, "B").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "co@" & cell.Value & ".navy.mil"
.Cc = "xo@" & cell.Value & ".navy.mil"
.BCC = ""
.Subject = "DIVOs"
.Body = "Captain," & vbNewLine & vbNewLine & strbody
.Attachments.Add ("C:\Documents\Slates\Traditional Slate\Test.doc")
.Save
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Ultimately, I'd like for this segment of the code:

For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
If LCase(Cells(cell.Row, "B").Value) = "yes" Then

to look on whatever tab I designate with multiple columns of ships for which I can put "yes" next to.

and this sgement of code:

For Each cell In Range("G1:G76")
strbody = strbody & cell.Value & vbNewLine

to be designated on a tab I designate.


Any help is greatly appreciated!