# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Send email from VBA

## Norman Jones

Hi swesterm,

See Ron De Bruin's extensive email coding examples at:

http://www.rondebruin.nl/sendmail.htm

---
Regards,
Norman



"swesterm" <swesterm@discussions.microsoft.com> wrote in message
news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
>I don't want to send a workbook.  My coding is all set.  I have the macro
> run, and if a certain value is reached, then I want to send an email out
> to 2
> people.  Simple as that.  But I can't figure out the coding.  Will someone
> PLEASE answer me using my examples listed below?
>
> I want the subject of the email to say = "Value has been reached"
> I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
> I don't want any attachments OR body text.
> Just send it to certain people AND a subject heading.
>
> Can someone please help WITHOUT directing me to someone else's website?
>
> Thx

----------


## swesterm

While I appreciate the help, is there someone out there who can answer this
WITHOUT forwarding me to someone's webpage?  I am sure this is just a
fewlines of code.

Activeworkbook.sendmail would work if i wanted to attach the workbook - BUT
I don't.  I just want to send an email with a Subject line and THAT IS IT>

Thx!

"Norman Jones" wrote:

> Hi swesterm,
>
> See Ron De Bruin's extensive email coding examples at:
>
>    http://www.rondebruin.nl/sendmail.htm
>
> ---
> Regards,
> Norman
>
>
>
> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
> news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
> >I don't want to send a workbook.  My coding is all set.  I have the macro
> > run, and if a certain value is reached, then I want to send an email out
> > to 2
> > people.  Simple as that.  But I can't figure out the coding.  Will someone
> > PLEASE answer me using my examples listed below?
> >
> > I want the subject of the email to say = "Value has been reached"
> > I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
> > I don't want any attachments OR body text.
> > Just send it to certain people AND a subject heading.
> >
> > Can someone please help WITHOUT directing me to someone else's website?
> >
> > Thx
>
>
>

----------


## Ron de Bruin

You don't tell us your mail program ?

And what's the problem to click on a link to read free information ????


--
Regards Ron de Bruin
http://www.rondebruin.nl


"swesterm" <swesterm@discussions.microsoft.com> wrote in message news:175A9F4D-46F6-4DE2-9F31-4194855DE16D@microsoft.com...
> While I appreciate the help, is there someone out there who can answer this
> WITHOUT forwarding me to someone's webpage?  I am sure this is just a
> fewlines of code.
>
> Activeworkbook.sendmail would work if i wanted to attach the workbook - BUT
> I don't.  I just want to send an email with a Subject line and THAT IS IT>
>
> Thx!
>
> "Norman Jones" wrote:
>
>> Hi swesterm,
>>
>> See Ron De Bruin's extensive email coding examples at:
>>
>>    http://www.rondebruin.nl/sendmail.htm
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
>> news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
>> >I don't want to send a workbook.  My coding is all set.  I have the macro
>> > run, and if a certain value is reached, then I want to send an email out
>> > to 2
>> > people.  Simple as that.  But I can't figure out the coding.  Will someone
>> > PLEASE answer me using my examples listed below?
>> >
>> > I want the subject of the email to say = "Value has been reached"
>> > I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>> > I don't want any attachments OR body text.
>> > Just send it to certain people AND a subject heading.
>> >
>> > Can someone please help WITHOUT directing me to someone else's website?
>> >
>> > Thx
>>
>>
>>

----------


## STEVE BELL

No need for any of us to reinvent the wheel when Ron has already done all
the hard work.

Besides I think you might appreciate the exercise....

(No offense meant)...

--
steveB

Remove "AYN" from email to respond
"swesterm" <swesterm@discussions.microsoft.com> wrote in message
news:175A9F4D-46F6-4DE2-9F31-4194855DE16D@microsoft.com...
> While I appreciate the help, is there someone out there who can answer
> this
> WITHOUT forwarding me to someone's webpage?  I am sure this is just a
> fewlines of code.
>
> Activeworkbook.sendmail would work if i wanted to attach the workbook -
> BUT
> I don't.  I just want to send an email with a Subject line and THAT IS IT>
>
> Thx!
>
> "Norman Jones" wrote:
>
>> Hi swesterm,
>>
>> See Ron De Bruin's extensive email coding examples at:
>>
>>    http://www.rondebruin.nl/sendmail.htm
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
>> news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
>> >I don't want to send a workbook.  My coding is all set.  I have the
>> >macro
>> > run, and if a certain value is reached, then I want to send an email
>> > out
>> > to 2
>> > people.  Simple as that.  But I can't figure out the coding.  Will
>> > someone
>> > PLEASE answer me using my examples listed below?
>> >
>> > I want the subject of the email to say = "Value has been reached"
>> > I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>> > I don't want any attachments OR body text.
>> > Just send it to certain people AND a subject heading.
>> >
>> > Can someone please help WITHOUT directing me to someone else's website?
>> >
>> > Thx
>>
>>
>>

----------


## Anne Troy

If you want code written FOR you, about the best place I know of is
www.vbaexpress.com
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"swesterm" <swesterm@discussions.microsoft.com> wrote in message
news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
> I don't want to send a workbook.  My coding is all set.  I have the macro
> run, and if a certain value is reached, then I want to send an email out
to 2
> people.  Simple as that.  But I can't figure out the coding.  Will someone
> PLEASE answer me using my examples listed below?
>
> I want the subject of the email to say = "Value has been reached"
> I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
> I don't want any attachments OR body text.
> Just send it to certain people AND a subject heading.
>
> Can someone please help WITHOUT directing me to someone else's website?
>
> Thx

----------


## Richard Buttrey

On Wed, 6 Jul 2005 12:11:04 -0700, "swesterm"
<swesterm@discussions.microsoft.com> wrote:

>I don't want to send a workbook.  My coding is all set.  I have the macro
>run, and if a certain value is reached, then I want to send an email out to 2
>people.  Simple as that.  But I can't figure out the coding.  Will someone
>PLEASE answer me using my examples listed below?
>
>I want the subject of the email to say = "Value has been reached"
>I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>I don't want any attachments OR body text.
>Just send it to certain people AND a subject heading.
>
>Can someone please help WITHOUT directing me to someone else's website?
>
>Thx

Try the following.

You might find Outlook halts whilst it tries to send the email,
waiting for you to confirm it's OK. Microsoft decided in their
infinite wisdom, to prevent the automatic sending of emails without
confirmation from the user and a 5 second delay. Can't fault the
logic, which was to  frustrate virus writers spamming emails to
distribution lists, but the law of unintended consequences is that
those of us who genuinely want to email multi users, are frustrated. I
have a regular monthly task which distributes about 350 emails with
different attachments. I use a piece of software called "ClickYes"
from express-soft.com which automatically clicks the "Yes" button for
me so that I can leave the whole thing running over a lunch-time

Sub OutputeMail()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim Txtto As String
Dim Txtsubject As String

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Txtto = "me@yahoo.com;" & "you@yahoo.com"
Txtsubject = "My Subject"

With olMail
.To = Txtto
.Subject = Txtsubject
End With

olMail.Send

End Sub

HTH,

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

----------


## swesterm

I don't want to send a workbook.  My coding is all set.  I have the macro
run, and if a certain value is reached, then I want to send an email out to 2
people.  Simple as that.  But I can't figure out the coding.  Will someone
PLEASE answer me using my examples listed below?

I want the subject of the email to say = "Value has been reached"
I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
I don't want any attachments OR body text.
Just send it to certain people AND a subject heading.

Can someone please help WITHOUT directing me to someone else's website?

Thx

----------


## Ron de Bruin

Have you look on the site ?

Maybe this

http://www.rondebruin.nl/mail/folder3/smallmessage.htm

Click also on the tip link for a example to send to more people



--
Regards Ron de Bruin
http://www.rondebruin.nl


"swesterm" <swesterm@discussions.microsoft.com> wrote in message news:9982D964-1307-44AE-8072-559E371312DC@microsoft.com...
> Relax there buddy.
>
> I didn't know I needed that.  I'm just a beginner.
>
> I'm using Microsoft Outlook.
>
> I thought it would be quite easy.
>
> Any way to do it really quick?

----------


## Norman Jones

Hi Swesterm

Try:

http://www.rentacoder.com/RentACoder/default.asp

I don't think that you have the remotest concept of the quality, range and
professionalism of Ron de Bruin's email code.

I am sure that if you ask nicely, Ron will not only visit you and type in
the code, but he will send the emails for you!

---
Regards,
Norman



"swesterm" <swesterm@discussions.microsoft.com> wrote in message
news:9982D964-1307-44AE-8072-559E371312DC@microsoft.com...
> Relax there buddy.
>
> I didn't know I needed that.  I'm just a beginner.
>
> I'm using Microsoft Outlook.
>
> I thought it would be quite easy.
>
> Any way to do it really quick?

----------


## STEVE BELL

We've been trying to make it easy for you.  Nobody is trying to get on your
case.

Ron has done it all and you can get all you need quickly...

http://www.rondebruin.nl/sendmail.htm

We all started as beginners and we know the frustration of getting started.

The ng is a great place for you to ask for help and get it.
Sometimes it is easier  and far more effective (for you) for us to
direct you to a great source so you can get the best help possible.

keep on Exceling...

steveB

Remove "AYN" from email to respond
"swesterm" <swesterm@discussions.microsoft.com> wrote in message
news:9982D964-1307-44AE-8072-559E371312DC@microsoft.com...
> Relax there buddy.
>
> I didn't know I needed that.  I'm just a beginner.
>
> I'm using Microsoft Outlook.
>
> I thought it would be quite easy.
>
> Any way to do it really quick?

----------


## swesterm

Relax there buddy.

I didn't know I needed that.  I'm just a beginner.

I'm using Microsoft Outlook.

I thought it would be quite easy.

Any way to do it really quick?

----------


## ktinsley@gmail.com

Try http://www.wizbids.com
WizBids.com is the most efficient and affordable way for business to
connect with talented freelance professionals from around the world.
>From artists and graphic designers to programmers and engineers, 1000's
of freelancer service providers use WizBids.com daily to find their
next outsourced project.  By outsourcing through WizBids.com,
businesses typically get projects done in half the time at half the
cost.

----------


## Gareth

You may have already come across this Richard - but just in case you
haven't - there's a product called Redemption that is supposed to get
around this problem. It sounds like it should seriously speed up your
email sending.

http://www.dimastr.com/redemption/

Read more about this at www.outlookcode.com/d/sec.htm

I've never tried it though - it's $200...

The best way I have managed to get around the problem was on a PC
(dedicated to running one piece of software and required to email
regular reports rather than a normal office PC). We installed Outlook 97
- no prompts: no problems!

cya


Richard Buttrey wrote:
> On Wed, 6 Jul 2005 12:11:04 -0700, "swesterm"
> <swesterm@discussions.microsoft.com> wrote:
>
>
>>I don't want to send a workbook.  My coding is all set.  I have the macro
>>run, and if a certain value is reached, then I want to send an email out to 2
>>people.  Simple as that.  But I can't figure out the coding.  Will someone
>>PLEASE answer me using my examples listed below?
>>
>>I want the subject of the email to say = "Value has been reached"
>>I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>>I don't want any attachments OR body text.
>>Just send it to certain people AND a subject heading.
>>
>>Can someone please help WITHOUT directing me to someone else's website?
>>
>>Thx
>
>
> Try the following.
>
> You might find Outlook halts whilst it tries to send the email,
> waiting for you to confirm it's OK. Microsoft decided in their
> infinite wisdom, to prevent the automatic sending of emails without
> confirmation from the user and a 5 second delay. Can't fault the
> logic, which was to  frustrate virus writers spamming emails to
> distribution lists, but the law of unintended consequences is that
> those of us who genuinely want to email multi users, are frustrated. I
> have a regular monthly task which distributes about 350 emails with
> different attachments. I use a piece of software called "ClickYes"
> from express-soft.com which automatically clicks the "Yes" button for
> me so that I can leave the whole thing running over a lunch-time
>
> Sub OutputeMail()
>     Dim olApp As Outlook.Application
>     Dim olMail As MailItem
>     Dim Txtto As String
>     Dim Txtsubject As String
>
>     Set olApp = New Outlook.Application
>     Set olMail = olApp.CreateItem(olMailItem)
>     Txtto = "me@yahoo.com;" & "you@yahoo.com"
>     Txtsubject = "My Subject"
>
>     With olMail
>         .To = Txtto
>         .Subject = Txtsubject
>     End With
>
>     olMail.Send
>
> End Sub
>
> HTH,
>
> Regards
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

----------


## Richard Buttrey

On Thu, 07 Jul 2005 19:53:19 -0500, Gareth <msng@garhooREMOVEME.com>
wrote:

>You may have already come across this Richard - but just in case you
>haven't - there's a product called Redemption that is supposed to get
>around this problem. It sounds like it should seriously speed up your
>email sending.
>
>http://www.dimastr.com/redemption/
>
>Read more about this at www.outlookcode.com/d/sec.htm
>
>I've never tried it though - it's $200...

Gareth,  Thanks for that suggestion. I'll follow it up. Sounds a touch
expensive but no doubt my company will cough up if it speeds
productivity.

Regards.


>
>The best way I have managed to get around the problem was on a PC
>(dedicated to running one piece of software and required to email
>regular reports rather than a normal office PC). We installed Outlook 97
>- no prompts: no problems!
>
>cya
>
>
>Richard Buttrey wrote:
>> On Wed, 6 Jul 2005 12:11:04 -0700, "swesterm"
>> <swesterm@discussions.microsoft.com> wrote:
>>
>>
>>>I don't want to send a workbook.  My coding is all set.  I have the macro
>>>run, and if a certain value is reached, then I want to send an email out to 2
>>>people.  Simple as that.  But I can't figure out the coding.  Will someone
>>>PLEASE answer me using my examples listed below?
>>>
>>>I want the subject of the email to say = "Value has been reached"
>>>I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>>>I don't want any attachments OR body text.
>>>Just send it to certain people AND a subject heading.
>>>
>>>Can someone please help WITHOUT directing me to someone else's website?
>>>
>>>Thx
>>
>>
>> Try the following.
>>
>> You might find Outlook halts whilst it tries to send the email,
>> waiting for you to confirm it's OK. Microsoft decided in their
>> infinite wisdom, to prevent the automatic sending of emails without
>> confirmation from the user and a 5 second delay. Can't fault the
>> logic, which was to  frustrate virus writers spamming emails to
>> distribution lists, but the law of unintended consequences is that
>> those of us who genuinely want to email multi users, are frustrated. I
>> have a regular monthly task which distributes about 350 emails with
>> different attachments. I use a piece of software called "ClickYes"
>> from express-soft.com which automatically clicks the "Yes" button for
>> me so that I can leave the whole thing running over a lunch-time
>>
>> Sub OutputeMail()
>>     Dim olApp As Outlook.Application
>>     Dim olMail As MailItem
>>     Dim Txtto As String
>>     Dim Txtsubject As String
>>
>>     Set olApp = New Outlook.Application
>>     Set olMail = olApp.CreateItem(olMailItem)
>>     Txtto = "me@yahoo.com;" & "you@yahoo.com"
>>     Txtsubject = "My Subject"
>>
>>     With olMail
>>         .To = Txtto
>>         .Subject = Txtsubject
>>     End With
>>
>>     olMail.Send
>>
>> End Sub
>>
>> HTH,
>>
>> Regards
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

----------


## Gary Keramidas

ron:

i tried your sendmail vba code, Mail one Sheet With SendMail, example 1, and
i get an error on the sendmail line: method "sendmail" of object '_workbook
failed.

i use outlook express and i do have the sheet renamed, in case that makes
any difference.

do you know why i am getting that error?


thanks

--


Gary


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:O40U%23MmgFHA.3608@TK2MSFTNGP12.phx.gbl...
> You don't tell us your mail program ?
>
> And what's the problem to click on a link to read free information ????
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
> news:175A9F4D-46F6-4DE2-9F31-4194855DE16D@microsoft.com...
>> While I appreciate the help, is there someone out there who can answer
>> this
>> WITHOUT forwarding me to someone's webpage?  I am sure this is just a
>> fewlines of code.
>>
>> Activeworkbook.sendmail would work if i wanted to attach the workbook -
>> BUT
>> I don't.  I just want to send an email with a Subject line and THAT IS
>> IT>
>>
>> Thx!
>>
>> "Norman Jones" wrote:
>>
>>> Hi swesterm,
>>>
>>> See Ron De Bruin's extensive email coding examples at:
>>>
>>>    http://www.rondebruin.nl/sendmail.htm
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>>
>>> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
>>> news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
>>> >I don't want to send a workbook.  My coding is all set.  I have the
>>> >macro
>>> > run, and if a certain value is reached, then I want to send an email
>>> > out
>>> > to 2
>>> > people.  Simple as that.  But I can't figure out the coding.  Will
>>> > someone
>>> > PLEASE answer me using my examples listed below?
>>> >
>>> > I want the subject of the email to say = "Value has been reached"
>>> > I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>>> > I don't want any attachments OR body text.
>>> > Just send it to certain people AND a subject heading.
>>> >
>>> > Can someone please help WITHOUT directing me to someone else's
>>> > website?
>>> >
>>> > Thx
>>>
>>>
>>>
>
>

----------


## Ron de Bruin

Hi Gary

See this first
http://www.rondebruin.nl/mail/problems.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gary Keramidas" <GKeramidas@comcast.net> wrote in message news:Ohp$v2MhFHA.1444@TK2MSFTNGP10.phx.gbl...
> ron:
>
> i tried your sendmail vba code, Mail one Sheet With SendMail, example 1, and i get an error on the sendmail line: method
> "sendmail" of object '_workbook failed.
>
> i use outlook express and i do have the sheet renamed, in case that makes any difference.
>
> do you know why i am getting that error?
>
>
> thanks
>
> --
>
>
> Gary
>
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:O40U%23MmgFHA.3608@TK2MSFTNGP12.phx.gbl...
>> You don't tell us your mail program ?
>>
>> And what's the problem to click on a link to read free information ????
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "swesterm" <swesterm@discussions.microsoft.com> wrote in message news:175A9F4D-46F6-4DE2-9F31-4194855DE16D@microsoft.com...
>>> While I appreciate the help, is there someone out there who can answer this
>>> WITHOUT forwarding me to someone's webpage?  I am sure this is just a
>>> fewlines of code.
>>>
>>> Activeworkbook.sendmail would work if i wanted to attach the workbook - BUT
>>> I don't.  I just want to send an email with a Subject line and THAT IS IT>
>>>
>>> Thx!
>>>
>>> "Norman Jones" wrote:
>>>
>>>> Hi swesterm,
>>>>
>>>> See Ron De Bruin's extensive email coding examples at:
>>>>
>>>>    http://www.rondebruin.nl/sendmail.htm
>>>>
>>>> ---
>>>> Regards,
>>>> Norman
>>>>
>>>>
>>>>
>>>> "swesterm" <swesterm@discussions.microsoft.com> wrote in message
>>>> news:9375B352-EA05-4994-BCB7-F586FB9C369A@microsoft.com...
>>>> >I don't want to send a workbook.  My coding is all set.  I have the macro
>>>> > run, and if a certain value is reached, then I want to send an email out
>>>> > to 2
>>>> > people.  Simple as that.  But I can't figure out the coding.  Will someone
>>>> > PLEASE answer me using my examples listed below?
>>>> >
>>>> > I want the subject of the email to say = "Value has been reached"
>>>> > I want to send the email to  = "me@yahoo.com" AND "you@yahoo.com"
>>>> > I don't want any attachments OR body text.
>>>> > Just send it to certain people AND a subject heading.
>>>> >
>>>> > Can someone please help WITHOUT directing me to someone else's website?
>>>> >
>>>> > Thx
>>>>
>>>>
>>>>
>>
>>
>
>

----------


## lazerwire

You can also find a good description on how to send an e-mail from Excel on this page:
Excel VBA: Send E-mail from Excel  :Smilie:

----------

