Referencing certain cells to send emails out

[Klartigue]

Please see the attached document.

I need to send emails out to all the high bids, so that is for all the email addresses in column y (whose items numbers are highlighted in column D.

So for example, for item A12, I would like to compose a email to the contact in column Y, so that is mkissane@bloomberg.net.

In subject of the email, I would like to put YOUR ARE HIGH ON AN ITEM ON AVALON'S BID LIST.

Then in the context of the email, I would like to say..

"YOU ARE HIGH ON __ (get item A12 by referencing cell D12) at a price of (get the price by referencing cell J2) covered at __( get the cover by referencing cell J3).

THIS IS FOR __ settle (get settle by referecing cell I2) and coming from our __ account (get account by referencing cell H2).

BIDDER IS __ (get name by referencing cell O2).

Thanks,
Katherine"

I have never done an email where in the context you reference parts of an excel sheet so I am not sure how to go about this?

Any help would be awesome, thanks!

[Klartigue]

And I would like to do this for all the highlighted items. Also, notice that some have the same email address.. so mkissane@bloomberg.net has items A12, A13, and A22. I am not sure how to group these all in one email either.

[Bob Phillips]

No attachment.

[Klartigue]

It's now attached in the above post

[Bob Phillips]

Just build a loop around the email code


With Activesheet

For Each cell in .Range(.Range("D2"), .Range("D2").End(xlDown))

If cell.Interior.Colorindex = 6 Then

'email using using cell.Offset(0, 18).Value as the recipient
End If
Next cell
End With

[Klartigue]

I have this but am not sure how to reference things on excel in an email

Sub Emailsetup()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

For Each cell In .Range(.Range("D2"), .Range("D2").End(xlDown))

If cell.Interior.ColorIndex = 6 Then

Set oRecipient = .Recipients.Add.Offset(0, 18)
oRecipient.Type = 1

.Subject = "YOU ARE HIGH ON (how do i reference cell D2?"

.body = "You are hihg on .Offset(0,3)at a price of (cell J2) covered at (cell J3)." & vbNewLine & vbNewLine & _
"Bidder was (cell )02)." & vbNewLine & vbNewLine & _
"Please send a trade ticket when you can. " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com " & vbNewLine & _
"713-238-2088"

End If

End With

End Sub

[Bob Phillips]

Set oRecipient = .Recipients.Add cell.Offset(0, 18).Value