Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xlDialogSendMail (2000/SR-1)

    I believe I am experiencing a simple syntax problem. I have been using the code below without error...
    <pre> MailList = Range("EmailedResponseList").Value
    SubjectLine = "Resolved PBN " & Range("PBN_Display") & ", please note"
    success = Application.Dialogs(xlDialogSendMail).Show([MailList], [SubjectLine]) </pre>

    ...and in every case the variable <font face="Georgia">MailList</font face=georgia> is a single email address, but I now need to also send to a second email address - <font color=blue>resolvedpolarbear@mycompany.com</font color=blue>. Everything I have tried to concatenate, add or some other way include <font color=blue>resolvedpolarbear@mycompany.com</font color=blue> has failed.

    I have reviewed the other postings here regarding xlDialogSendMail, but these appear either more complicated than I can figure out and/or more elaborate than my need to simply have more than one recipient.

    Thanks!

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xlDialogSendMail (2000/SR-1)

    Andrew -

    I've tried what you suggested previously and cannot get it to work. The email is created and APPEARS to be ok, but after it is sent it returns an "Undeliverable" error. If I look at my Sent folder I see the message with "Email Properties" for the "To:" address of...

    Display Name = "elsewhere.com" (i.e., all text that appears AFTER the second "@" sign in the concatenation)
    Email address = "user1@somewhere.com;user2" (i.e., all the text that appears BEFORE the second "@" sign in the concatenation)

    Perhaps I am missing something.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xlDialogSendMail (2000/SR-1)

    John -

    Not familiar with Arrays in VB, but after a careless error I made initially this works great. THANKS!

    Sorry I was not more thorough in my Lounge search - I could have saved you (and me) some time. As always - you guys saved the day.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: xlDialogSendMail (2000/SR-1)

    If you had run your Search far back, you should have found Rory advising me that multiple recipients need to be provided as an Array, as in:

    MailList = Array("Name1","Name2")

    Recipients Required Variant. Specifies the name of the recipient as text, or as an array of text strings if there are multiple recipients

    so you'll have to build the array manually or by looping through the range and adding each cell value.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: xlDialogSendMail (2000/SR-1)

    See if this function helps; it should allow you to set a variable size range of recipients (range named "recipients") in a column.

    Function buildmaillist() As Variant
    Dim arrTemp() As String
    Dim i As Integer, j As Integer
    Dim rngRecips As Range
    Set rngRecips = Worksheets("Sheet1").Range("recipients")
    i = rngRecips.Count
    ReDim arrTemp(1 To i)
    For j = 1 To i
    arrTemp(j) = rngRecips.Cells(j, 1).Value
    Next
    buildmaillist = arrTemp
    End Function

    then you would call it from the SendMail dialog line:

    success = Application.Dialogs(xlDialogSendMail).Show(buildma illist, SubjectLine)

    This is not completely tested, you may need to make a few changes to get it running.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: xlDialogSendMail (2000/SR-1)

    Or just:
    <pre>Function buildmaillist()
    buildmaillist = Worksheets("Sheet1").Range("recipients")
    End Function
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: xlDialogSendMail (2000/SR-1)

    This is new information for me; a Range and an Array are interchangeable in Excel? Can you tell me more, such as when to use this, when it wouldn't work?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: xlDialogSendMail (2000/SR-1)

    John,
    You can assign a range directly to a Variant and you will get a 2-D array (the second dimension being columns, so 1 in this example). The only thing you have to remember is that if you want to refer to elements of the array, you must specify the second dimension (e.g. arrTemp(i, 1) ) even if it's a one-column range.
    Hope that helps - any questions, post back.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xlDialogSendMail (2000/SR-1)

    Editted - I previously posted this but was entirely wrong as I stopped testing at the point I had a mail message that looked OK rather than clicking the send button <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    <font color=6495ed>The concetanation is a ";" character

    Try something along the lines of

    Sub test()
    Dim user1, user2, subjectline
    Dim success As Boolean
    user1 = "user1@somewhere.com"
    user2 = "user2@elsewhere.com"
    subjectline = "please note"
    success = Application.Dialogs(xlDialogSendMail).Show(user1 & ";" & user2, subjectline)
    End Sub</font color=6495ed>

Posting Permissions

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