Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Long .Message Required in Excel Email Macro

  1. #1
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Russell

  3. #3
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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???
    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    When I first tried it I had removed M7

    Russell

  5. #5
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"
    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •