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

T

Tyson

I need a macro 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 [email protected]
No Job Title [email protected]
Yes Job Title [email protected]
No Job Title [email protected]

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

Thanks for the help,

Tyson
 
R

Ron de Bruin

See
http://www.rondebruin.nl/sendmail.htm

Choose a Outlook example and clik on the tip link
http://www.rondebruin.nl/mail/tips2.htm

--

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


I need a macro 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 [email protected]
No Job Title [email protected]
Yes Job Title [email protected]
No Job Title [email protected]

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

Thanks for the help,

Tyson
 
T

Tyson

This is somewhat helpful, but can someone give me a VBA answer based
on just my question?

Thanks,

Tsyon
 
T

Tyson

Yes, what you put in the link is VBA, but it isn't custom to just my
question is the problem. You have too much stuff going on in your
link... I just want code that I can cut and copy from this post and
put it into my file.

My whole point was I wanted an "answer based on just my question."

If someone could do that I would VERY thankful,

Tyson
 
R

Ron de Bruin

I just want code that I can cut and copy from this post and put it into my file.

I see that you not want to learn something.
In my first reply you have your answer (almost)


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(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 = "[email protected]"
.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


Yes, what you put in the link is VBA, but it isn't custom to just my
question is the problem. You have too much stuff going on in your
link... I just want code that I can cut and copy from this post and
put it into my file.

My whole point was I wanted an "answer based on just my question."

If someone could do that I would VERY thankful,

Tyson
 
Top