Macro that will add multiple emails based on a range of cell values

[Tysone]

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 (e-mail address removed)
No Job Title (e-mail address removed)
Yes Job Title (e-mail address removed)
No Job Title (e-mail address removed)


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

Thanks for the help,

Tyson

 

[Ron de Bruin]

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("D1100").Cells.SpecialCells(xlCellTypeConstants)
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 = "(e-mail address removed)"
.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


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 (e-mail address removed)
No Job Title (e-mail address removed)
Yes Job Title (e-mail address removed)
No Job Title (e-mail address removed)


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

Thanks for the help,

Tyson

 

[Tysone]

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

Tyson