Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code problem (2000)

    <font color=blue>ListSend = ListSend + "'" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & "', "
    ListSend = ListSend + "'" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & "', "
    ListSend = ListSend + "'" & ThisWorkbook.Worksheets("Line 2").Range("B3").Value & "'"</font color=blue>


    <font color=448800>'sRecipentSendTo = Array("Steve Wilders/GB/CCE", "Johanna Williams/GB/CCE")</font color=448800>


    <font color=blue>sRecipentSendTo = "'Array('" & ListSend & ")"</font color=blue>

    What i'm trying to do, is add a number of cells together for the destination(s)
    What is shown here in Green, does work., I've played around with various combinations trying to get it to make the string
    the one in blue is the obvious one, but doesn't work correctly. (some weird concoction of quotes i'm sure.) <img src=/S/mad.gif border=0 alt=mad width=15 height=15>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code problem (2000)

    If B1 has
    <pre>Steve Wilders/GB/CCE</pre>

    and B2 has:
    <pre>Johanna Williams/GB/CCE</pre>


    Then:
    <pre>ListSend = ListSend + """" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & """"& ", "
    ListSend = ListSend + """" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & """"

    sRecipentSendTo = Array(ListSend)</pre>


    should work

    A good way to test is that you want in the immediate window:
    <pre>? listsend</pre>

    to display:
    <pre>"Steve Wilders/GB/CCE", "Johanna Williams/GB/CCE"</pre>

    which is the array you want to feed.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    The first part works, in that the string is being defined ok, if its entered as below in Red, then the code works, and the emails are sent, but if its done as <font color=blue>sRecipentSendTo = Array(ListSend)</font color=blue> then nothing happens, even though the string is correct.




    <font color=blue>Dim objNotes As Object '/Holds a Notes Session.
    Dim objdb As Object '/Holds the Defualt Mail Database in Notes.
    Dim objDoc As Object '/Creates a Mail message document.
    Dim objAttachment As Object '/Holds the Attachment
    Dim sMessageBody As String '/Holds the Message text.
    Dim sSubject As String '/Holds the Subject of the Message.
    Dim lErrorNum As Long '/Gets the Error Number during Error Handling.
    Dim sProblem As String
    Dim sRecipentSendTo As Variant
    Dim ListSend As String


    Beep

    ListSend = ListSend + """" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & """" & ", "
    ListSend = ListSend + """" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & """"



    sRecipentSendTo = Array(ListSend)

    Stop</font color=blue>


    <font color=red>'sRecipentSendTo = Array("Steve Wilders/GB/CCE", "Cliff Brown/GB/CCE") ' THIS WORKS</font color=red>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Try:

    <pre>Dim sRecipentSendTo(0 To 1) As Variant
    sRecipentSendTo(0) = """" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & """"
    sRecipentSendTo(1) = """" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & """"
    </pre>

    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code problem (2000)

    do you need?:
    sRecipentSendTo = Array("""" & ListSend & """")

    (I hate all these extra quotes)
    Steve

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Thanks for the suggestion Steve,....... unfortunately its does the same thing, no emails are sent.

    The string is perfectly formated now as intended, but i can't seem to get the Array command set correctly into sRecipentSendTo.

    currently its at :-

    <font color=blue>sRecipentSendTo = Array("""" & ListSend & """")</font color=blue>

    and ive tried various other combinations eg:-

    <font color=blue>sRecipentSendTo = Array & "(" & ListSend & ")"</font color=blue>



    anyone have any more suggestions to pass ListSend into the sRecipentSendTo as an array ?

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Steve

    Rather than trying to put ListSend into an array and then pass the array to sRecipentSendTo, why not just build up the array directly and pass that?

    Dim aRecipients(1) As String
    aRecipients(0) = ThisWorkbook.Worksheets("Line 2").Range("B1").Value
    aRecipients(1) = ThisWorkbook.Worksheets("Line 2").Range("B2").Value

    .....

    .Recipients = aRecipients



    HTH

    Peter

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Peter, i tried as you suggested.....
    but its it still doesn't seem to work...


    the bottom line confuses me a bit, and generates an error 'compile error, unqualified etc.."#

    I changed it slightly, (typo?)

    here is the code atm:


    <font color=blue>Dim sRecipentSendTo(5) As String
    Dim ListSend As Variant
    Dim B1 As String, B2 As String, B3 As String, B4 As String, B5 As String, B6 As String
    'Application.Cursor = xlWait
    Beep

    B1 = """" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & """"
    B2 = """" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & """"
    B3 = """" & ThisWorkbook.Worksheets("Line 2").Range("B3").Value & """"
    B4 = """" & ThisWorkbook.Worksheets("Line 2").Range("B4").Value & """"
    B5 = """" & ThisWorkbook.Worksheets("Line 2").Range("B5").Value & """"
    B6 = """" & ThisWorkbook.Worksheets("Line 2").Range("B6").Value & """"

    sRecipentSendTo(0) = B1
    If Len(B2) > 5 Then sRecipentSendTo(1) = ", " + B2
    If Len(B3) > 5 Then sRecipentSendTo(2) = ", " + B3
    If Len(B4) > 5 Then sRecipentSendTo(3) = ", " + B4
    If Len(B5) > 5 Then sRecipentSendTo(4) = ", " + B5
    If Len(B6) > 5 Then sRecipentSendTo(5) = ", " + B6

    .sRecipentSendTo = sRecipentSendTo</font color=blue>

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Sorry I assumed that you were doing this with routing and the bottom line was from the routing code.

    Here is the code I used to test my idea. (with Email address in the spreadsheet) which works ok

    <pre>Sub testSend()
    Dim aRecipients(1) As String
    aRecipients(0) = ThisWorkbook.Worksheets("sheet1").Range("B1").Valu e
    aRecipients(1) = ThisWorkbook.Worksheets("sheet1").Range("B2").Valu e

    With ThisWorkbook
    .HasRoutingSlip = True
    With .RoutingSlip
    .Delivery = xlAllAtOnce
    .Recipients = aRecipients
    .Subject = "Here is the workbook"
    .Message = "Here is the workbook. What do you think?"
    .ReturnWhenDone = True
    End With
    .Route
    End With

    End Sub
    </pre>



    HTH

    Peter

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code problem (2000)

    I still think it is the dbl-quote problem. Try this scheme, it uses chr(34) = dbl-quote so you don't confuse the EMBEDDED dbl-quotes with the delimiter dbl-quotes:

    If B1 has
    Steve Wilders/GB/CCE

    and B2 has:
    Johanna Williams/GB/CCE

    Then:
    ListSend = ListSend & chr(34) & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & chr(34) & ", "
    ListSend = ListSend & chr(34) & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & chr(34)

    sRecipentSendTo = Array(ListSend)

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Steve, :- Tried this approach and it still didn't work.. i know the string looks correct, but passing it to the array just doesn't seem to work....

    Peter, :- I don't really recognise your code, I'm using lotus notes to send the emails with Excel. The comlpete code is posted below. I know that if i pass the Array'ed string into SRecipentSendto, then the emails work.


    <font color=blue>Private Sub CommandButton2_Click()
    'Attribute VB_Name = "LotusNoteMailer"
    'Option Explicit

    Dim objNotes As Object '/Holds a Notes Session.
    Dim objdb As Object '/Holds the Defualt Mail Database in Notes.
    Dim objDoc As Object '/Creates a Mail message document.
    Dim objAttachment As Object '/Holds the Attachment
    Dim sMessageBody As String '/Holds the Message text.
    Dim sSubject As String '/Holds the Subject of the Message.
    Dim lErrorNum As Long '/Gets the Error Number during Error Handling.
    Dim sProblem As String
    Dim sRecipentSendTo(5) As String
    Dim ListSend As Variant
    Dim B1 As String, B2 As String, B3 As String, B4 As String, B5 As String, B6 As String
    'Application.Cursor = xlWait
    Beep

    B1 = """" & ThisWorkbook.Worksheets("Line 2").Range("B1").Value & """"
    B2 = """" & ThisWorkbook.Worksheets("Line 2").Range("B2").Value & """"
    B3 = """" & ThisWorkbook.Worksheets("Line 2").Range("B3").Value & """"
    B4 = """" & ThisWorkbook.Worksheets("Line 2").Range("B4").Value & """"
    B5 = """" & ThisWorkbook.Worksheets("Line 2").Range("B5").Value & """"
    B6 = """" & ThisWorkbook.Worksheets("Line 2").Range("B6").Value & """"

    sRecipentSendTo(0) = B1
    If Len(B2) > 5 Then sRecipentSendTo(1) = ", " + B2
    If Len(B3) > 5 Then sRecipentSendTo(2) = ", " + B3
    If Len(B4) > 5 Then sRecipentSendTo(3) = ", " + B4
    If Len(B5) > 5 Then sRecipentSendTo(4) = ", " + B5
    If Len(B6) > 5 Then sRecipentSendTo(5) = ", " + B6

    '.sRecipentSendTo = sRecipentSendTo





    'sRecipentSendTo = Array("""" & ListSend & """") ' NOT WORK

    'sRecipentSendTo = Array("Steve Wilders/GB/CCE", "Cliff Brown/GB/CCE") ' THIS WORKS

    sMessageBody = "This is the message body"
    sSubject = "This is a test"

    '/Create a Notes session. If Notes is not Open then the code will stop.

    Err.Clear
    lErrorNum = 0
    On Error Resume Next
    Set objNotes = CreateObject("Notes.Notessession")
    lErrorNum = Err
    '/429 = ActiveX component can't create object.

    If objNotes Is Nothing Or lErrorNum = 429 Then

    Beep
    MsgBox "Lotus Notes is not curretly Open!"
    Application.Cursor = xlDefault
    End If

    Set objdb = objNotes.GETDATABASE("", "")
    '/Set database to default mail database.
    Call objdb.openmail
    Set objDoc = objdb.CREATEDOCUMENT

    '/Compose the Message.

    With objDoc

    Call .ReplaceItemValue("SendTo", sRecipentSendTo)
    Call .ReplaceItemValue("CopyTo", sRecipentCopyTo)
    Call .ReplaceItemValue("BlindCopyTo", sRecipentBlindCopyTo)
    Call .ReplaceItemValue("Subject", sSubject)
    Call .ReplaceItemValue("Body", sMessageBody)

    Call .Send(False, sRecipentSendTo)
    Application.Cursor = xlDefault
    End With

    Set objNotes = Nothing
    Beep

    Application.StatusBar = False
    End Sub</font color=blue>

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Steve

    As the method I tried worked without adding all the punctuation in Outlook, it might be worth trying the same method for notes as well.

    B1 = ThisWorkbook.Worksheets("Line 2").Range("B1").Value
    B2 = ThisWorkbook.Worksheets("Line 2").Range("B2").Value
    B3 = ThisWorkbook.Worksheets("Line 2").Range("B3").Value
    B4 = ThisWorkbook.Worksheets("Line 2").Range("B4").Value
    B5 = ThisWorkbook.Worksheets("Line 2").Range("B5").Value
    B6 = ThisWorkbook.Worksheets("Line 2").Range("B6").Value

    sRecipentSendTo(0) = B1
    If Len(B2) > 5 Then sRecipentSendTo(1) = B2
    If Len(B3) > 5 Then sRecipentSendTo(2) = B3
    If Len(B4) > 5 Then sRecipentSendTo(3) = B4
    If Len(B5) > 5 Then sRecipentSendTo(4) = B5
    If Len(B6) > 5 Then sRecipentSendTo(5) = B6



    I am not sure what will happen if you get a name of less than 6 in the string though!

    HTH

    Peter

  13. #13
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    Peter , i don't really understand what your code replaces. does it replace all the of remaining code>?

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code problem (2000)

    It just replaces the corresponding lines of code. Try

    Private Sub CommandButton2_Click()
    'Attribute VB_Name = "LotusNoteMailer"
    'Option Explicit

    Dim objNotes As Object '/Holds a Notes Session.
    Dim objdb As Object '/Holds the Defualt Mail Database in Notes.
    Dim objDoc As Object '/Creates a Mail message document.
    Dim objAttachment As Object '/Holds the Attachment
    Dim sMessageBody As String '/Holds the Message text.
    Dim sSubject As String '/Holds the Subject of the Message.
    Dim lErrorNum As Long '/Gets the Error Number during Error Handling.
    Dim sProblem As String
    Dim sRecipentSendTo(5) As String
    Dim ListSend As Variant
    Dim B1 As String, B2 As String, B3 As String, B4 As String, B5 As String, B6 As String
    'Application.Cursor = xlWait
    Beep

    B1 = ThisWorkbook.Worksheets("Line 2").Range("B1").Value
    B2 = ThisWorkbook.Worksheets("Line 2").Range("B2").Value
    B3 = ThisWorkbook.Worksheets("Line 2").Range("B3").Value
    B4 = ThisWorkbook.Worksheets("Line 2").Range("B4").Value
    B5 = ThisWorkbook.Worksheets("Line 2").Range("B5").Value
    B6 = ThisWorkbook.Worksheets("Line 2").Range("B6").Value

    sRecipentSendTo(0) = B1
    If Len(B2) > 5 Then sRecipentSendTo(1) = B2
    If Len(B3) > 5 Then sRecipentSendTo(2) = B3
    If Len(B4) > 5 Then sRecipentSendTo(3) = B4
    If Len(B5) > 5 Then sRecipentSendTo(4) = B5
    If Len(B6) > 5 Then sRecipentSendTo(5) = B6





    '.sRecipentSendTo = sRecipentSendTo





    'sRecipentSendTo = Array("""" & ListSend & """") ' NOT WORK

    'sRecipentSendTo = Array("Steve Wilders/GB/CCE", "Cliff Brown/GB/CCE") ' THIS WORKS

    sMessageBody = "This is the message body"
    sSubject = "This is a test"

    '/Create a Notes session. If Notes is not Open then the code will stop.

    Err.Clear
    lErrorNum = 0
    On Error Resume Next
    Set objNotes = CreateObject("Notes.Notessession")
    lErrorNum = Err
    '/429 = ActiveX component can't create object.

    If objNotes Is Nothing Or lErrorNum = 429 Then

    Beep
    MsgBox "Lotus Notes is not curretly Open!"
    Application.Cursor = xlDefault
    End If

    Set objdb = objNotes.GETDATABASE("", "")
    '/Set database to default mail database.
    Call objdb.openmail
    Set objDoc = objdb.CREATEDOCUMENT

    '/Compose the Message.

    With objDoc

    Call .ReplaceItemValue("SendTo", sRecipentSendTo)
    Call .ReplaceItemValue("CopyTo", sRecipentCopyTo)
    Call .ReplaceItemValue("BlindCopyTo", sRecipentBlindCopyTo)
    Call .ReplaceItemValue("Subject", sSubject)
    Call .ReplaceItemValue("Body", sMessageBody)

    Call .Send(False, sRecipentSendTo)
    Application.Cursor = xlDefault
    End With

    Set objNotes = Nothing
    Beep

    Application.StatusBar = False
    End Sub

Posting Permissions

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