xlDialogSendMail (2000/SR-1)

Topic

New Reply

I believe I am experiencing a simple syntax problem. I have been using the code below without error…

   MailList = Range("EmailedResponseList").Value
   SubjectLine = "Resolved PBN " & Range("PBN_Display") & ", please note"
   success = Application.Dialogs(xlDialogSendMail).Show([MailList], [SubjectLine]) 

…and in every case the variable MailList is a single email address, but I now need to also send to a second email address – resolvedpolarbear@mycompany.com. Everything I have tried to concatenate, add or some other way include resolvedpolarbear@mycompany.com 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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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(buildmaillist, SubjectLine)

This is not completely tested, you may need to make a few changes to get it running.

Reply | Quote

Or just:

Function buildmaillist()
   buildmaillist = Worksheets("Sheet1").Range("recipients")
End Function

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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
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

Reply | Quote

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.

Reply | Quote