Create outlook email from excel email list with one criteria with subject matter and body of email

[davodka]

Hi Everyone,

I have emails in column C and the criteria in column D would be a "Yes". So I want to email everyone in column C when there's a Yes in column D in one big email. Also I'd like the subject to be in cell D1 and the body of the email in D2.

Doable?

Thanks,

Michael

 

[Michael M]

Have a look here for sending multiple E-mails

www.RonDeBruin.nl/sendmail

 

[davodka]

Hi,

I have been there and was able to pull off one with the criteria, but it's sending it to multiple emails whereas I only want to send it to one email. Please help.

And I used the, Mail a message to each person in a range, but I just want one email.

Have a look here for sending multiple E-mails

www.RonDeBruin.nl/sendmail

 

[Michael M]

I'd suggest then, that you post the code you're using
and provide us with

1. what you want to achieve
2. whare the list of recipients names are stored, etc.

 

[davodka]

Below is the code that I snagged from rondebruin.nl and the problem is that the emails are individual emails, whereas I want to string the emails address to to just one email. And if there is a way that I can identify which ones I want to CC in column C, that would even be more awesome!!


Sub Sendmail()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'Or use Display or send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Michael M]

So do you have a continous range of cells in column "B" that hold the E-Mail addresses ?
And
given the material in the body of the E-Mail would it be a little insensitive to include ALL e-Mail addresses in the one E-Mail ??

None of my business, but I think I'd be sticking to individual E-Mails

 

[davodka]

That's not my message. My message would read, the monthly financial reports for August for xxx client. That code I just took from that one website. I'm sending out monthly reports, not billing anyone.

So do you have a continous range of cells in column "B" that hold the E-Mail addresses ?
And
given the material in the body of the E-Mail would it be a little insensitive to include ALL e-Mail addresses in the one E-Mail ??

None of my business, but I think I'd be sticking to individual E-Mails

 

[Michael M]

Ok, no worries....what about my first question ?
If you range of E-Mail addresses is from say, "B2:B100", then you can use Application.Join.Transpose

 

[davodka]

Yes, I have a continous range in B. It's range B7 to B100. For subject matter, it's in row 4, the message body, it's in row 5. And if there's a "Yes", I want to send the email in the "to:" and if it says CC, then I want to "cc:" the person. Please help.

	Column A</SPAN>	Column B</SPAN>	Column C</SPAN>	Column D</SPAN>	Column E</SPAN>
Row 1</SPAN>	August 2013 Monthly Report</SPAN>
Row 2</SPAN>
Row 3</SPAN>
Row 4</SPAN>	subject line</SPAN>		Toys R US - August 2013 Monthly Report</SPAN>	See's Candy - August 2013 Monthly Report</SPAN>	Motorcycles Plus - August 2013 Monthly Report</SPAN>
Row 5 </SPAN>	Message body</SPAN>		Please see attached August 2013 Monthly Report for Toys R US and let us know if you have any questions.</SPAN>	Please see attached August 2013 Monthly Report for See's Candy and let us know if you have any questions.</SPAN>	Please see attached August 2013 Monthly Report for Motorcycles Plus and let us know if you have any questions.</SPAN>
Row 6</SPAN>	Client</SPAN>		Toys R US</SPAN>	See's Candy</SPAN>	Motorcycles Plus</SPAN>
Row 7</SPAN>	Michael Ni</SPAN>	michael.ni@mc.com</SPAN>	Yes</SPAN>	Yes</SPAN>	CC</SPAN>
Row 8</SPAN>	syr.edu</SPAN>	mi@syr.edu</SPAN>	CC</SPAN>		Yes</SPAN>
Row 9</SPAN>	personal</SPAN>	test1@hotmail.com</SPAN>	Yes</SPAN>	CC</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Michael M]

Hi,
Firstly, sorry I haven't responded sooner, but I've been away to a wedding.
Any way, I'm afraid I wouldn't do this in the way you have set it out.
I'd have one subject cell and one or 2 body cells and loop through the list of companies, a bit like this

Excel 2007
	A	B	C	D	E	F
1	August 2013 Monthly Report	Send	CC	- August 2013 Monthly Report	Please see attached August 2013 Monthly Report for	and let us know if you have any questions.
2	Toys R Us	michael.ni@mc.com	michael.ni@mc.com
3	Sees Candy	michael.ni@mc.com	mi@syr.edu
4	Motorcycles plus	test1@hotmail.com	test1@hotmail.com
Sheet1

That way you only need a single reference

With the code adjusted to suit, it will allow you to see each one as a check before sending, but if you are happy to send the lot in one go, simply change the line in red to SEND

Option Compare Text
Sub MM1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object, OutMail As Object, r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error GoTo cleanup
For r = 2 To lr
On Error Resume Next
With OutMail
.to = Range("B" & r).Value
.cc = Range("C" & r).Value
.Subject = Range("A" & r).Value & " " & Range("D1").Value
.Body = "Dear " & Range("A" & r).Value _
& vbNewLine & vbNewLine & Range("E1").Value & Range("A" & r).Value & " " & Range("F1").Value
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'Or use Display or send CHANGE THIS LINE TO SEND IF YOU  ARE HAPPY WITH THE DISPLAY]
End With
On Error GoTo 0
Next r
cleanup:
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub