Solved

How does sending an email from within Excel work?

Posted on 2014-01-13
20
394 Views
Last Modified: 2014-01-15
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-
0
Comment
Question by:RWayneH
  • 11
  • 9
20 Comments
 
LVL 26

Expert Comment

by:pony10us
ID: 39776892
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
 

Author Comment

by:RWayneH
ID: 39777108
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39777117
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
 

Author Comment

by:RWayneH
ID: 39777241
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39777266
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
 

Author Comment

by:RWayneH
ID: 39777314
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39777701
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
 

Author Comment

by:RWayneH
ID: 39777706
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39777729
Do you have an example spreadsheet? I guess I don't quite follow.
0
 

Author Comment

by:RWayneH
ID: 39777807
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:pony10us
ID: 39777975
Okay, do you want a separate email to each individual or one email addressed to all?
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39778048
Updated version for your review/test
Simple-mail.xlsm
0
 

Author Comment

by:RWayneH
ID: 39779072
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
 

Author Comment

by:RWayneH
ID: 39779125
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
 
LVL 26

Accepted Solution

by:
pony10us earned 500 total points
ID: 39779526
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
 

Author Closing Comment

by:RWayneH
ID: 39780623
EXCELlent!!  Works Great!  Appreciate the help. -R-
0
 

Author Comment

by:RWayneH
ID: 39781735
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
 

Author Comment

by:RWayneH
ID: 39781738
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39782983
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
 

Author Comment

by:RWayneH
ID: 39783666
Work!! thanks. -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now