Join and Excel97

[Belair58]

Hi,

I'm using the e-mail function from Lotus Notes (Thanks for the code) but I a couple of users that are still using 97 and the code blows up at the following command.

MailDoc.Body = _ 
    Replace("The following NEW SUPPLIER request had been actioned today:@@" _ 
        & Join(Application.Transpose(Range([a1], [a300].End(3))), "@") _ 
            & "@@...", "@", vbCrLf)

To be exact, right at the Join command. Is there code I could use to accomplish this same task without using the Join command?

As always your help is greatly appreciated.

Thanks

 

[sweater_vests_rock]

belair.

you could use a loop to accomplish this without the join function. you would have to do this before the line you are referencing though. something along these lines (not error-tested):

   Dim MyJoinString as String
   Dim MyArray() as String

'  Load range into array for quicker processing
   MyArray = Range([a1], [a300].End(3))
   MyJoinString = ""

   For i = LBound(MyArray) to UBound(MyArray)
'     Combine strings using concatenate and seperator "@"
      MyJoinString = MyJoinString & MyArray(i, 1) & "@"
   Next i

'  Strip extra "@" from the end
   MyJoinString = Left(MyJoinString, Len(MyJoinString) - 1)

MailDoc.Body = _ 
    Replace("The following NEW SUPPLIER request had been actioned today:@@" _ 
        & MyJoinString & "@@...", "@", vbCrLf)

to replace

MailDoc.Body = _ 
    Replace("The following NEW SUPPLIER request had been actioned today:@@" _ 
        & Join(Application.Transpose(Range([a1], [a300].End(3))), "@") _ 
            & "@@...", "@", vbCrLf)

hope this helps. ben.