Email again in excel

A

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
 
A

Alvin Hansen

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

Alvin


"Jim Thomlinson" skrev:
 
J

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

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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top