Email again in excel

[Alvin Hansen]

Hi i use this
Sub Send_Range()
em = Range("gemte!$b$2").Value
' Select the range of cells on the active worksheet.

Sheets("email").Activate
Sheets("email").Range("A1:n71").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With Sheets("email").MailEnvelope
.Introduction = "Mail vedr. Booking Usa"
.Item.To = em
.Item.Subject = "Booking usa"
.Item.Send
End With
MsgBox ("Email er afsendt til: " & em)
End Sub

Now if a user when the dialog box comes up from outlook about sending the
mail say no to sending then i get an error, is there away in this code to
exit sub or something like that if the user say no to sending the mail.

Best regards alvin

[Jim Thomlinson]

Take a look at Ron's site. Everything you ever wanted to know about e-mailing
from Excel but were affraid to ask.

http://www.rondebruin.nl/

"Alvin Hansen" wrote:

> Hi i use this
> Sub Send_Range()
> em = Range("gemte!$b$2").Value
> ' Select the range of cells on the active worksheet.
>
> Sheets("email").Activate
> Sheets("email").Range("A1:n71").Select
>
> ' Show the envelope on the ActiveWorkbook.
> ActiveWorkbook.EnvelopeVisible = True
>
> ' Set the optional introduction field thats adds
> ' some header text to the email body. It also sets
> ' the To and Subject lines. Finally the message
> ' is sent.
> With Sheets("email").MailEnvelope
> .Introduction = "Mail vedr. Booking Usa"
> .Item.To = em
> .Item.Subject = "Booking usa"
> .Item.Send
> End With
> MsgBox ("Email er afsendt til: " & em)
> End Sub
>
> Now if a user when the dialog box comes up from outlook about sending the
> mail say no to sending then i get an error, is there away in this code to
> exit sub or something like that if the user say no to sending the mail.
>
> Best regards alvin
>

[Alvin Hansen]

Yes i know about ron's site
the code is from ron's site so...............

Alvin


"Jim Thomlinson" skrev:

> Take a look at Ron's site. Everything you ever wanted to know about e-mailing
> from Excel but were affraid to ask.
>
> http://www.rondebruin.nl/
>
> "Alvin Hansen" wrote:
>
> > Hi i use this
> > Sub Send_Range()
> > em = Range("gemte!$b$2").Value
> > ' Select the range of cells on the active worksheet.
> >
> > Sheets("email").Activate
> > Sheets("email").Range("A1:n71").Select
> >
> > ' Show the envelope on the ActiveWorkbook.
> > ActiveWorkbook.EnvelopeVisible = True
> >
> > ' Set the optional introduction field thats adds
> > ' some header text to the email body. It also sets
> > ' the To and Subject lines. Finally the message
> > ' is sent.
> > With Sheets("email").MailEnvelope
> > .Introduction = "Mail vedr. Booking Usa"
> > .Item.To = em
> > .Item.Subject = "Booking usa"
> > .Item.Send
> > End With
> > MsgBox ("Email er afsendt til: " & em)
> > End Sub
> >
> > Now if a user when the dialog box comes up from outlook about sending the
> > mail say no to sending then i get an error, is there away in this code to
> > exit sub or something like that if the user say no to sending the mail.
> >
> > Best regards alvin
> >

[Jake Marx]

Hi Alvin,

I'm assuming a runtime error is thrown when the user clicks no? If that's
the case, you can use error handling to manage it:

Sub Send_Range()
Dim em As Range

On Error GoTo ErrHandler

Set em = Range("sheet1!a1")
' Select the range of cells on the active worksheet.

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With em.Parent.MailEnvelope
.Introduction = "test"
.Item.To = em.Value
.Item.Subject = "test"
.Item.Send
End With
MsgBox ("Email sent to: " & em.Value)

ExitRoutine:
Exit Sub
ErrHandler:
MsgBox "Email not sent.", vbInformation
ActiveWorkbook.EnvelopeVisible = False
Resume ExitRoutine
End Sub


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Alvin Hansen wrote:
> Hi i use this
> Sub Send_Range()
> em = Range("gemte!$b$2").Value
> ' Select the range of cells on the active worksheet.
>
> Sheets("email").Activate
> Sheets("email").Range("A1:n71").Select
>
> ' Show the envelope on the ActiveWorkbook.
> ActiveWorkbook.EnvelopeVisible = True
>
> ' Set the optional introduction field thats adds
> ' some header text to the email body. It also sets
> ' the To and Subject lines. Finally the message
> ' is sent.
> With Sheets("email").MailEnvelope
> .Introduction = "Mail vedr. Booking Usa"
> .Item.To = em
> .Item.Subject = "Booking usa"
> .Item.Send
> End With
> MsgBox ("Email er afsendt til: " & em)
> End Sub
>
> Now if a user when the dialog box comes up from outlook about sending
> the mail say no to sending then i get an error, is there away in
> this code to exit sub or something like that if the user say no to
> sending the mail.
>
> Best regards alvin

[Alvin Hansen]

Its working
thank you for your help

regards alvin

"Jake Marx" skrev:

> Hi Alvin,
>
> I'm assuming a runtime error is thrown when the user clicks no? If that's
> the case, you can use error handling to manage it:
>
> Sub Send_Range()
> Dim em As Range
>
> On Error GoTo ErrHandler
>
> Set em = Range("sheet1!a1")
> ' Select the range of cells on the active worksheet.
>
> ' Show the envelope on the ActiveWorkbook.
> ActiveWorkbook.EnvelopeVisible = True
>
> ' Set the optional introduction field thats adds
> ' some header text to the email body. It also sets
> ' the To and Subject lines. Finally the message
> ' is sent.
> With em.Parent.MailEnvelope
> .Introduction = "test"
> .Item.To = em.Value
> .Item.Subject = "test"
> .Item.Send
> End With
> MsgBox ("Email sent to: " & em.Value)
>
> ExitRoutine:
> Exit Sub
> ErrHandler:
> MsgBox "Email not sent.", vbInformation
> ActiveWorkbook.EnvelopeVisible = False
> Resume ExitRoutine
> End Sub
>
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> Alvin Hansen wrote:
> > Hi i use this
> > Sub Send_Range()
> > em = Range("gemte!$b$2").Value
> > ' Select the range of cells on the active worksheet.
> >
> > Sheets("email").Activate
> > Sheets("email").Range("A1:n71").Select
> >
> > ' Show the envelope on the ActiveWorkbook.
> > ActiveWorkbook.EnvelopeVisible = True
> >
> > ' Set the optional introduction field thats adds
> > ' some header text to the email body. It also sets
> > ' the To and Subject lines. Finally the message
> > ' is sent.
> > With Sheets("email").MailEnvelope
> > .Introduction = "Mail vedr. Booking Usa"
> > .Item.To = em
> > .Item.Subject = "Booking usa"
> > .Item.Send
> > End With
> > MsgBox ("Email er afsendt til: " & em)
> > End Sub
> >
> > Now if a user when the dialog box comes up from outlook about sending
> > the mail say no to sending then i get an error, is there away in
> > this code to exit sub or something like that if the user say no to
> > sending the mail.
> >
> > Best regards alvin
>
>

[=LEN(NICK)]

I have tried the suggestions above and they work well with Outlook, is there anyway to send something through Lotus Notes?.