MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Damon Ostrander


Posted by Gary on August 31, 2001 3:51 AM

Hi,

From reading previuos messages you seem to be the guy to consult.

I have this code for emailing the contents of a listbox but all get is a blank email. Where am i going wrong?

Private Sub CommandButton8_Click()

Dim OLook As Object
Dim Mitem As Object
Dim SendAnEmail As Boolean


Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.to = "MMouse@Disneyland..."
Mitem.Subject = "Stock level warning!"
Mitem.body = "Bla bla bla..." & ListBox1.Text

Mitem.send


Set OLook = Nothing
Set Mitem = Nothing
End Sub

I get a message if use a textbox...?

I could also do with placing comments into textboxes if you could cast some light onto this too..

i can see the comments in MsgBox thanks to a previous message placed on the board but can't manipulate it.

Thanks for any help,

Gary


Posted by Ivan F Moala on August 31, 2001 5:07 AM

Gary
ListBox.text will not give you the text if your
Listbox is set to frmmultiselectmulti.
To get the Text for multiselect you will have to
loop through the listboxes selected item and
test to see if it is true = selected eg.


For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
msg = msg & vbCrLf & ListBox1.List(i)
End If
Next i


Mitem.body = "Bla bla bla..." & msg 'ListBox1.Text


Ivan

Posted by Gary on August 31, 2001 6:29 AM

Emailing a listbox

Hi,

Thank you Ivan, i've checked that the listbox is on fmMultiSelectMulti and am now using this code but i'm still only recieving "Bla bla bla..." you get the picture. I don't know if it would make a huge difference but i'm using '97.


Private Sub CommandButton8_Click()

Dim OLook As Object
Dim Mitem As Object
Dim SendAnEmail As Boolean
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
For i = 0 To ListBox1.ListCount - 1

Mitem.To = "MMouse@Disneyland...."
Mitem.Subject = "Stuff"

If ListBox1.Selected(i) = True Then
msg = ListBox1.List(i)
End If
Next i

Mitem.body = "bla bla bla bla" & msg

Mitem.send


Set OLook = Nothing
Set Mitem = Nothing
End Sub

Thanks again,
Gary

Posted by Ivan F Moala on August 31, 2001 6:42 AM

Re: Emailing a listbox

Hi Gary
Did you want the listbox set as multiselect ?
Or did you want to send the msg as only 1 item
selected.....I just assumed you wanted multiselect

Both versons worked for me ?? except when I
changed it to multiselect hence the code I gave you.

couple of things
1) change code to
Private Sub CommandButton8_Click()
Dim msg as string
Dim OLook As Object
Dim Mitem As Object
Dim SendAnEmail As Boolean
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)

Mitem.To = "MMouse@Disneyland...."
Mitem.Subject = "Stuff"

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
msg = msg & ListBox1.List(i)
End If
Next i

Mitem.body = "bla bla bla bla" & msg

Mitem.send


Set OLook = Nothing
Set Mitem = Nothing
End Sub


2) Have you selected anything from the ListBox ?


Ivan Hi, Thank you Ivan, i've checked that the listbox is on fmMultiSelectMulti and am now using this code but i'm still only recieving "Bla bla bla..." you get the picture. I don't know if it would make a huge difference but i'm using '97.

Posted by Gary on August 31, 2001 7:10 AM

Re: Emailing a listbox

Hi Ivan,

Your code works - your a saviour.


Thanks again.

Posted by Gary on August 31, 2001 7:16 AM

Last one...

Sorry,

One last question.

Is it possible to have the contents of the email appear as a list rather than a block. If i have multiple selections i end up with a block of text rather than a legible list.

Thanks,

Posted by Damon Ostrander on August 31, 2001 8:37 AM

Thanks Ivan -- Good work.

You had it all taken care of before I even got out of bed this morning!

Best regards.

Damon Hi Gary Mitem.To = "MMouse@Disneyland...." Mitem.Subject = "Stuff" For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then msg = msg & ListBox1.List(i) End If Next i Mitem.body = "bla bla bla bla" & msg Mitem.send

Posted by Damon Ostrander on August 31, 2001 8:47 AM

Re: Last one... okay Ivan, I'll get this one.

Hi Gary,

Just change

msg = msg & ListBox1.List(i)

to

msg = msg & vbCr & ListBox1.List(i)

(vbCr is a pre-defined constant in VBA that is a
carriage return character. There is also a vbLf
defined for a line feed, vbCrLf for both, etc.
Sometimes you have to experiment to see which one
is needed, as different applications use different
special characters to terminate a line.)

Damon

Posted by Ivan F Moala on August 31, 2001 12:07 PM

Thanks Damon

Yes...and now I'm in bed and your answering.
least i could do

Thanks You had it all taken care of before I even got out of bed this morning! Best regards. Damon : Hi Gary Mitem.To = "MMouse@Disneyland...." Mitem.Subject = "Stuff" For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then msg = msg & ListBox1.List(i) End If Next i : Mitem.body = "bla bla bla bla" & msg : Mitem.send :