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

davodka

New Member
Joined
Feb 25, 2013
Messages
13
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

davodka

New Member
Joined
Feb 25, 2013
Messages
13
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
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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

New Member
Joined
Feb 25, 2013
Messages
13
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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

New Member
Joined
Feb 25, 2013
Messages
13
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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

New Member
Joined
Feb 25, 2013
Messages
13
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">August 2013 Monthly Report</td><td style=";">Send</td><td style=";">CC</td><td style=";"> - August 2013 Monthly Report</td><td style=";">Please see attached August 2013 Monthly Report for </td><td style=";">and let us know if you have any questions.</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Toys R Us</td><td style="text-decoration: underline;color: #0000FF;;">michael.ni@mc.com</td><td style="text-decoration: underline;color: #0000FF;;">michael.ni@mc.com</td><td style="text-align: right;text-decoration: underline;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Sees Candy</td><td style="text-decoration: underline;color: #0000FF;;">michael.ni@mc.com</td><td style="text-decoration: underline;color: #0000FF;;">mi@syr.edu</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Motorcycles plus</td><td style="text-decoration: underline;color: #0000FF;;">test1@hotmail.com</td><td style="text-decoration: underline;color: #0000FF;;">test1@hotmail.com</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />


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

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,796
Messages
5,470,832
Members
406,728
Latest member
BCM7

This Week's Hot Topics

Top