Send emails to different people each containing different content in the body based on cel

[Drake2300]

Hello all,

I am still very new to VBA, and the Mail function is confusing me to no end. What I'm trying to do is to send a list of emails to emails listed in column A (A1-A30) each containing different information in the body of the email based on whats in other cells (or sheets in the workbook)

so I want to send an email to the address listed in A1 containing, in the body of the email, content from cells B1-E1.

and repeat for subsequent cells.

The email program I will be using is outlook 2010, but is it possible to direct emails to pop up using whatever the default windows email client is for that computer?

Any help is really appreciated!

thanks a ton,
Drake

[Drake2300]

I found a sheet that works well for most of what I need! however, I barely understand any of the code, and theres one thing that I need it to do in addition to what its already doing:

I have a third sheet that will have the same list of names as the emails, with stats next to each name (same line) - I need each email that is selected to also send only the stats from that person name in the body in addition to whats written in the text box.

Thank you to jaslake for writing the original code! (http://www.excelforum.com/excel-prog...outlook-2.html)

emailing.xlsm

[jaslake]

Hi Drake2300

You'll need to redesign your Sheet Stats to add unique identifiers. You may well have two or more Students with the same first name. Do you use Student ID"S?

Please share YOUR file...not someone else's.

[Drake2300]

Hi Drake2300

You'll need to redesign your Sheet Stats to add unique identifiers. You may well have two or more Students with the same first name. Do you use Student ID"S?

Please share YOUR file...not someone else's.

In this case the Email will be unique for every person - emails cannot be duplicated in our system, the Student # on page one will also be unique.

Ive tinkered with the sheet code for the above sheet to suite my needs, but i'm still not able to get it to do what i want it to do, and I'm not sure what i'm doing wrong.

I've tried using Application.WorksheetFunction.VLookup(E_name, Sheet2.Range("C2:J36"), 2, False) from within the 'Body' section of the Email code:

.Body = ws.OLEObjects("TextBox2").Object.Text & "<BR>" & _
Application.WorksheetFunction.VLookup(ws.Cells(cel.Row, "C").Value, Sheet2.Range("C2:J36"), 2, False)

That didnt seem to work, but when I change the number 2 to a 1 I get an email address added to the body.

in the below sheet, I tried to reference individual cells, as I noticed the code cycles columns (at least I think it does) - but that doesn't seem to work either

in sheet 3 I've given a basic example of what I would like each email to look like when this is all said and done.

I think I'm missing something important with formatting here, can you suggest anything for me?emailing.xlsm

[jaslake]

Hi Drake2300

I'm not at all certain what you're attempting to do...try the attached.

[Drake2300]

You are a genious sir. This is exactly what I needed - i'm seeing some changes in the VBA - is this the only place you changed?

.htmlbody = ws.OLEObjects("TextBox2").Object.Text & "<BR><BR>" & _
RangetoHTML(mailRng)
' .Body = ws.OLEObjects("TextBox2").Object.Text & "<BR>" & _
' ws.Cells(cel.Row, "D").Text & "<BR>" & _
' ws.Cells(cel.Row, "E").Text & "<BR>" & _
' ws.Cells(cel.Row, "F").Text & "<BR>" & _
' ws.Cells(cel.Row, "G").Text & "<BR>" & _
' ws.Cells(cel.Row, "H").Text & "<BR>" & _
' ws.Cells(cel.Row, "I").Text & "<BR>" & _
' ws.Cells(cel.Row, "J").Text

does this mean I was on the right track? it's the conversion to HTML that I was missing?

I'm still learning, so I really appreciate this help, thank you! I will mark this as solved.

[jaslake]

Hi Drake2300

I also made some changes here to capture the Range of Cells to include in the Body of the Email

Please Login or Register  to view this content.

and here for Column Reference of Attachments (if any)...

Please Login or Register  to view this content.

And of course, Ron de Bruin's Function RangetoHTML(rng As Range)...

Please Login or Register  to view this content.

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.