Sending Email from Excel

[HWL]

Excel 2007,

I get how to send sheets and workbooks and such, but what if I wanted to simply send some text generated within VBA (hard coded perhaps)? Do I need access to the user email client to send through? I am looking to automate so the user doesn't have to press SEND on their email client or even see the email being generated. Is this impossible without server access? Thanks

 

[phantom1975]

How about using a hyperlink within a cell?

=HYPERLINK("mailto:youknowwho@gmail.com?bcc=itisjustme@gmail.com&subject=THIS IS A TEST OF THE EMEGENCY BROADCAST SYSTEM&body=This is only a test","EMAIL ME")

This site can help with the syntax:

Mailto Syntax Tutorial - How to Create an Email Link - How to use Mailto Protocol, Mailto tag, and Mailto link tutorial - AddressMunger.com

 

[HWL]

How about using a hyperlink within a cell?

=HYPERLINK("mailto:youknowwho@gmail.com?bcc=itisjustme@gmail.com&subject=THIS IS A TEST OF THE EMEGENCY BROADCAST SYSTEM&body=This is only a test","EMAIL ME")

This site can help with the syntax:

Mailto Syntax Tutorial - How to Create an Email Link - How to use Mailto Protocol, Mailto tag, and Mailto link tutorial - AddressMunger.com

Thanks phantom1975, this is a really cool solution. It still requires the user to actually send the mail, so it doesn't solve my immediate problem. However, I think of other applications for this. Is there a way to attatch the present sheet with this hyperlink method?

 

[phantom1975]

I'll take a stab at answering your question, but I'm not the best person to do so. I believe the major issue you will have is that an email client of some sort will be required to send the email. Simply put -- Excel doesn't send email. What it can do is pass values on to an email client. For example, code could be written to pass whatever values you like to Microsoft Outlook and I am sure that there is a way to have it sent behind the scenes. The problem that you will likely have is that the code to do this for Outlook may be different for each type of email client. Furthermore, there may be "blockades" built into the email clients that prevent this. Otherwise it would be relatively easy to write a macro to hack into someone's email and send spam.

You might want to consider the benefits of forcing the user to hit the SEND button. What if they made a mistake and wished to correct it before sending the email? Does your code allow them to preview what is being sent before they approve it? How would you feel if an email was sent out with your name on it and you didn't know what the content was?

I sent you the hyperlink formula to use within a cell. To do it in code, you would use the Workbook.FollowHyperlink command. For example:

Thisworkbook.FollowHyperlink "mailto:surprise@gmail.com?bcc=me@gmail.com&subject=Howdy partner&body=test"