I posted this once already in microsoft.public.excel but I didn't get
an answer that really worked for me. So hopefully putting it in
programming it will produce better results. Anyway, sorry for the
double post...

================================

I need a macro (based on this data) that will basically look at one
column and if it says “Yes” put the email address in the email column
in the .bcc. All the email addresses need to be in one email by the
way. Here is what my data looks like starting in A4:

Yes Job Title
No Job Title
Yes Job Title
No Job Title


The list of emails is going to be variable too from month to month.

Thanks for the help,

Tyson

I reply with this example in your other thread

Example for Outlook

You can do this for a sheet named "Sheet1"
In col A yes/no and in Col D the mail addresses

Use Display instead of Send to test the code


Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String

On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Range("D1:D100").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, -3).Value) = "yes" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = strto
.Subject = "This is the Subject line"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tysone" wrote in message ...
I posted this once already in microsoft.public.excel but I didn't get
an answer that really worked for me. So hopefully putting it in
programming it will produce better results. Anyway, sorry for the
double post...

================================

I need a macro (based on this data) that will basically look at one
column and if it says “Yes” put the email address in the email column
in the .bcc. All the email addresses need to be in one email by the
way. Here is what my data looks like starting in A4:

Yes Job Title
No Job Title
Yes Job Title
No Job Title


The list of emails is going to be variable too from month to month.

Thanks for the help,

Tyson

Thank you Ron, this is exactly what I was looking for.

Tyson

On May 27, 8:02*am, "Ron de Bruin" wrote:
I reply with this example in your other thread

Example for Outlook

You can do this for a sheet named "Sheet1"
In col A yes/no and in Col D the mail addresses

Use Display instead of Send to test the code

Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007
* * Dim OutApp As Object
* * Dim OutMail As Object
* * Dim strbody As String
* * Dim cell As Range
* * Dim strto As String

* * On Error Resume Next
* * For Each cell In ThisWorkbook.Sheets("Sheet1") _
* * * * .Range("D1:D100").Cells.SpecialCells(xlCellTypeCon stants)
* * * * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, -3)..Value) = "yes" Then
* * * * * * strto = strto & cell.Value & ";"
* * * * End If
* * Next cell
* * On Error GoTo 0
* * If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

* * Set OutApp = CreateObject("Outlook.Application")
* * OutApp.Session.Logon
* * Set OutMail = OutApp.CreateItem(0)

* * strbody = "Hi there" & vbNewLine & vbNewLine & _
* * * * * * * "This is line 1" & vbNewLine & _
* * * * * * * "This is line 2" & vbNewLine & _
* * * * * * * "This is line 3" & vbNewLine & _
* * * * * * * "This is line 4"

* * On Error Resume Next
* * With OutMail
* * * * .To = "
* * * * .CC = ""
* * * * .BCC = strto
* * * * .Subject = "This is the Subject line"
* * * * .Body = strbody
* * * * .Send 'or use .Display
* * End With
* * On Error GoTo 0

* * Set OutMail = Nothing
* * Set OutApp = Nothing
End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Tysone" wrote in ...

I posted this once already in microsoft.public.excel but I didn't get
an answer that really worked for me. *So hopefully putting it in
programming it will produce better results. *Anyway, sorry for the
double post...

================================

I need a macro (based on this data) that will basically look at one
column and if it says “Yes” put the email address in the email column
in the .bcc. *All the email addresses need to be in one email by the
way. *Here is what my data looks like starting in A4:

Yes * *Job Title * * *
No * * *Job Title * * *
Yes * *Job Title * * *
No * * *Job Title * * *

The list of emails is going to be variable too from month to month.

Thanks for the help,

Tyson