Avatar of RWayneH
RWayneHFlag for United States of America asked on

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-
Microsoft ApplicationsOutlookMicrosoft Excel

Avatar of undefined
Last Comment
RWayneH

8/22/2022 - Mon
Steven Carnahan

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
ASKER
RWayneH

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-
Steven Carnahan

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
RWayneH

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-
Steven Carnahan

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
ASKER
RWayneH

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-
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steven Carnahan

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.
ASKER
RWayneH

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-
Steven Carnahan

Do you have an example spreadsheet? I guess I don't quite follow.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
RWayneH

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-
Steven Carnahan

Okay, do you want a separate email to each individual or one email addressed to all?
Steven Carnahan

Updated version for your review/test
Simple-mail.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
RWayneH

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-
ASKER
RWayneH

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-
ASKER CERTIFIED SOLUTION
Steven Carnahan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RWayneH

EXCELlent!!  Works Great!  Appreciate the help. -R-
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
RWayneH

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-
ASKER
RWayneH

If I name cell J1, it could add that to the front of the Subject and the date/time would be the suffix.  -R-
Steven Carnahan

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")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
RWayneH

Work!! thanks. -R-