Email again in excel

[Guest]

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

 

[Guest]

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/

 

[Guest]

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

Alvin


"Jim Thomlinson" skrev:

 

[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]

 

[Guest]

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]

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