How does sending an email from within Excel work?

I was wondering if this is possible, but also how does Excel know what email template to use that is pre addressed and/or has a default text in Subject field and body of the email.  It does not need to have an attachment, and is more of an alert that a file is done and available for review.  The Subject field usually has todays date in it and I at times put a comment in the template body when an issue prevents the file from being complete.  Curious how others may have approached this in Excel, or is this something that should goto an OutLook area of EE?  -R-
RWayneHAsked:
Who is Participating?
 
pony10usConnect With a Mentor Commented:
I played with it a bit more and the results are here.

Basically I added the CC to read from Column A and the BCC to read from column C just as the To field reads from column H then added a couple of lines to the body.

It might take some thinking (and coding) to arrange for an option to send individually or collectively (the way it is now).

It currently is able to handle up to 24 email addresses in each field (To:, CC: and BCC:) You can increase/decrease by changing the range(s) in the lines:

' To:
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("H2:H25")

' cc:
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A2:A25")

 ' bcc:
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("C2:C25")

Are we getting close?  :)
20140114aSimple-mail.xlsm
0
 
pony10usCommented:
A few years ago I had a similar issue and came across some code that I think does what you are asking for.  Remember that you can't send from Excel unless Excel is open.

Here is the original test workbook that I got from techguy.org
Simple-mail.xlsm
0
 
RWayneHAuthor Commented:
For some reason I cannot get the To:  or  CC: fields to populate.  Did this come with any instructions?  Any other options?  Get the Subject and the Message body to populate.   -R-
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
pony10usCommented:
Well, I had to really dig back through my notes on this but here is the original link from 2007:

http://forums.techguy.org/business-applications/574148-e-mail-cell-data-excel.html
0
 
RWayneHAuthor Commented:
I still am not able to getting my list to email to populate in the Send message template...  I still have to copy/paste all To: and CC'd recptiants.  Is there anything else out there?  or can some look at why it is not working?  Not sure what Item A, B, and C are in column D, maybe that has something to do with it?  Articile was not much help.    -R-
0
 
pony10usCommented:
The macro is designed to read from the current cell.

In the example you have to have the focus (cursor) on cell A2

It will then open an email populated like this:

eecapture20140113
0
 
RWayneHAuthor Commented:
Ok that makes more sense and works for one name... still does not answer the Item A B and C question.  Now I need someone to be able to put all the names in column H in the To: field, or the ones from Item A, in the To:  the ones from Item B or another column in the CC'd field and the one from Item C in the BCC: field... or another column.  having to copy/paste reciptants is a pain...  I suppose I could make a distribution? but that does not solve the cc'd probably?  This is not a ever good example  -R-
0
 
pony10usCommented:
Okay, so with this additional information I agree that what I provided is not sufficient for you. However it was meant as a starting point.
0
 
RWayneHAuthor Commented:
True, a starting point....  Do you have enough information and a good idea of what I am looking for?  or do you need more info?  Please advise and thanks. -R-
0
 
pony10usCommented:
Do you have an example spreadsheet? I guess I don't quite follow.
0
 
RWayneHAuthor Commented:
the sample would be the one that you sent me... I need a means to be able to pull in all the emails that are in column H into the To: field and the email that are in another column into the CC'd.... I am not going to worry about the BCC'd.  I saw the Item A, B and C in the file that you sent... still not sure what that does, we maybe able to use that to separate where the email goes.  That or using another column to populate the CC'd email.. whatever.  All I did was import that sheet and the module into my workbook and started testing it.  -R-
0
 
pony10usCommented:
Okay, do you want a separate email to each individual or one email addressed to all?
0
 
pony10usCommented:
Updated version for your review/test
Simple-mail.xlsm
0
 
RWayneHAuthor Commented:
On your question of send individually?  Can we have a user input that allows either?  (a send individually checkbox on the s-sheet?)  I see that all emails come it the email form..  with the date and time...  that is good..  however one has an Item A and the others do not.  Does that do anything?  If I put a Item B in the email, does it CC the email instead to putting it in the To: field?

I do need the CC option.. however the BCC option will be rarely used.  Great start.   -R-
0
 
RWayneHAuthor Commented:
also in the line:  .Body = "The file: xxxx.xxx is ready for your review. "

How would I add a new line with a space between them?  I like the option to use Item A for the To: list and the Item B for the CC'd list.  manage one list.    -R-
0
 
RWayneHAuthor Commented:
EXCELlent!!  Works Great!  Appreciate the help. -R-
0
 
RWayneHAuthor Commented:
Sorry.... I am having issues adding to the Subject:  The Date and Time is great, but we need it more descriptive...  How would I add the report name to the beginning? -R-
0
 
RWayneHAuthor Commented:
If I name cell J1, it could add that to the front of the Subject and the date/time would be the suffix.  -R-
0
 
pony10usCommented:
If I follow what you want then try changing the .Subject.... line to this:

.Subject = Worksheets("Sheet1").Range("J1") & " " & Format(Now, "dd-mmm-yy h:mm:ss")
0
 
RWayneHAuthor Commented:
Work!! thanks. -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.