Long .Message Required in Excel Email Macro

[s-o-s]

Every week I send out an several Emails to a long distribution list. I have decided to automate this and have got most of it working. I need to include some extra text see below but have hit a limit that stops the macro. It is happy with the 1st 3-4 lines but after that...Error

Anyone got any ideas for getting around this problem???

Thanks in advance.

The code I am using is as follows:

Sub TestMail()
Workbooks("Book1.xls").HasRoutingSlip = True
M1 = "Please find attached the Revenue Analysis Report for the week ending " & Date & Chr(10)
M2 = "If you have any problems please contact me." & Chr(10) & Chr(10)
M3 = "Best regards" & Chr(10) & "S-O-S" & Chr(10)
M4 = "Cost Accountant" & Chr(10) & "Company Name" & Chr(10)
M5 = "T: 99999 999999" & Chr(10) & "F: 99999 999999" & Chr(10)
M6 = "M: 99999 999999" & Chr(10) & Chr(10)
M7 = "This is an automated distribution please ignore the routing request that appears below"

With Workbooks("Book1.xls").RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = Array("TestDL")
.Subject = "Revenue Analysis Report - " & Date
.Message = M1 & M2 & M3 & M4 & M5 & M6 & M7
End With
Workbooks("Book1.xls").Route

End Sub

 

[Russell]

Have you tried removing the colons against the phone numbers it seemed to work when I tried that.

Russell

 

[s-o-s]

Nice try...I think it is a system Limit...When I ran it I can add M1...M6 without an error, when I concat the M7 I get the error. The version I posted may have been just a bit too short if it worked for you.

Anyone know what the Maximum length in Characters is for adding the .Message is???

 

[Russell]

Looks like the limit is 255 (if you include Chr(10)'s)

When I first tried it I had removed M7

Russell

 

[s-o-s]

I guess I will have to be a bit less verbose!!!

Microsoft (it may be already in 2002) an addition that would be nice in the future is

With Workbooks("Book1.xls").RoutingSlip
...
...
.Message = "up to 254 Chars"