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
 

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,179
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,179
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,179
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,179
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
 

Forum statistics

Threads
1,081,973
Messages
5,362,508
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top