Email again in excel
 

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

Email again in excel
 

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

Email again in excel
 

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

Email again in excel
 

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

Email again in excel
 

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