Hi,

I want to write a small macro in Excel that would send an email when something goes wrong. Can you please help me the VB codes?

Thanks alots!

There are two possibilities here. One possibility would be to use the Hyperlink object with a mailto:[email protected] to generate an e-mail. The other is to use the Route property to send the workbook. Not sure which would be best for you. Lookup Hyperlink and Route in Excel VBA Help.

Hi Scott,

Thanks for your feedback, but I didn't find any helpful information when I looked into Lookup Hyperlink and Route in Excel VBA Help. Can you show me the examples?

Thanks!

Gee I found the info when I looked. Though I see the HTML editor stripped out part of my instructions; you need to use the Mailto: tag before the e-mail address.

Hi Scott,

I am not sure if we are looking at the same Excel VBA Help, can you please copy and paste what you saw.

Below is what I have found in Excel VBA under Route:

Example
This example sends Book1.xls to three recipients, one after another.

Workbooks("BOOK1.XLS").HasRoutingSlip = True
With Workbooks("BOOK1.XLS").RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = Array("Adam Bendel", _
"Jean Selva", "Bernard Gabor")
.Subject = "Here is BOOK1.XLS"
.Message = "Here is the workbook. What do you think?"
End With
Workbooks("BOOK1.XLS").Route

Yes, That's what I found. That, as I described, is one possibility.

Are the codes correctly? Where can I place them (under workbook or any tabs) Do I have a create a textbox or label?

Workbooks("BOOK1.XLS").HasRoutingSlip = True
With Workbooks("BOOK1.XLS").RoutingSlip
.Mailto: [email protected]
.Recipients = Array("First Last")
.Subject = "Here is BOOK1.XLS"
.Message = "Here is the workbook. What do you think?"
End With
Workbooks("BOOK1.XLS").Route

The code would be part of a VBA module that could be triggered from a button or key combination.

Scott,

I tried to trigger from a buttom, but I got an error, can you please check the error?

Private Sub CommandButton1_Click()

ActiveWorkbook.HasRoutingSlip = True
ActiveWorkbook.RoutingSlip.Mailto: xlOneAfterAnother

Workbooks("Test.XLS").HasRoutingSlip = True
With Workbooks("Test.XLS").RoutingSlip
.Delivery = xlOneAfterAnother
.Mailto: [email protected]
.Subject = "Here is Test.XLS"
.Message = "Here is the workbook. What do you think?"
End With
Workbooks("Test.XLS").Route

End Sub

I said there are TWO ways to do it. One using Mailto and another using Routing. You are trying to combine both things into one. If you look at the code sample it shows using .Recipients for the To box. You changed the code to something else.

Use one method or the other.