Does anyone know why this won't work?

[Jason]

Everything works fine if I use text for gwMessage.BodyText="adkfasldjfasdfasdfs" but if I use an inputbox for user prompt and go (TxtRange= an input box) gwMessage.BodyText= TxtRange I get an error

Does anyone know how to have the user click on the text that they want sent. My input box works on other appliations for text, I don't see why it won't work with this

Private Sub CommandButton7_Click(
Dim gwMessage As GroupwareTypeLibrary.Message
Dim gwaccount As GroupwareTypeLibrary.Account
Dim gwapp As GroupwareTypeLibrary.Applicatio
Dim gwattach As GroupwareTypeLibrary.Attachmen
Dim rs As Varian
Dim WhoTo As Strin
Dim Person As Strin
Dim TxtRange As Rang
Dim Cancel As Boolea



If Application.UserName = "Jason" The

WhoTo = InputBox("Name?", "Send EMail"
If Cancel The
Exit Su
End I

If WhoTo = "Jason" The
Person = "(e-mail address removed)
End I

Set gwapp = CreateObject("NovellGroupWareSession"
Set gwaccount = gwapp.Login(MyUserName, , MyPassword
Set gwMessage = gwaccount.MailBox.Messages.ad

Set TxtRange = Application.InputBox(prompt:="Click on the text you want sent", Title:="Text Selection", Type:=8
TxtRange.Selec
gwMessage.BodyText = TxtRang

gwMessage.Subject = "Spreadsheet Stuff
gwMessage.Recipients.add Perso
gwMessage.Sen
End I
End I
End Sub

 

[Jake Marx]

Hi Jason,

It depends on what the user selects. An InputBox of type=8 will return a
Range object. The default property of a Range is the Value property. So
your line of code

gwMessage.BodyText = TxtRange

will attempt to get the Value property of the Range TxtRange and assign that
value to the BodyText property of gwMessage. If the user selects more than
one cell, then the Value property will fail. If you want to allow for a
multi-cell selection, you may have to get a little more explicit in your
code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jim Rech]

It looks like it should work (without running it). Does using:

gwMessage.BodyText = TxtRange.Cells(1).Value

help?

--
Jim Rech
Excel MVP
| Everything works fine if I use text for
gwMessage.BodyText="adkfasldjfasdfasdfs" but if I use an inputbox for
user prompt and go (TxtRange= an input box) gwMessage.BodyText= TxtRange
I get an error.
|
| Does anyone know how to have the user click on the text that they want
sent. My input box works on other appliations for text, I don't see why it
won't work with this?
|
|
| Private Sub CommandButton7_Click()
| Dim gwMessage As GroupwareTypeLibrary.Message2
| Dim gwaccount As GroupwareTypeLibrary.Account2
| Dim gwapp As GroupwareTypeLibrary.Application
| Dim gwattach As GroupwareTypeLibrary.Attachment
| Dim rs As Variant
| Dim WhoTo As String
| Dim Person As String
| Dim TxtRange As Range
| Dim Cancel As Boolean
|
|
|
|
|
|
| If Application.UserName = "Jason" Then
|
|
| WhoTo = InputBox("Name?", "Send EMail")
| If Cancel Then
| Exit Sub
| End If
|
| If WhoTo = "Jason" Then
| Person = "(e-mail address removed)"
| End If
|
| Set gwapp = CreateObject("NovellGroupWareSession")
| Set gwaccount = gwapp.Login(MyUserName, , MyPassword)
| Set gwMessage = gwaccount.MailBox.Messages.add
|
| Set TxtRange = Application.InputBox(prompt:="Click on the text you want
sent", Title:="Text Selection", Type:=8)
| TxtRange.Select
| gwMessage.BodyText = TxtRange
|
| gwMessage.Subject = "Spreadsheet Stuff"
| gwMessage.Recipients.add Person
| gwMessage.Send
| End If
| End If
| End Sub

 

[Jim Rech]

This time it returns the top left cell.value of the range I select

Right, so echoing what Jake said, you can only use .Value with one cell. If
you want to have the text of multiple cells get into the message you'll have
to build up a string of each cell's text first, and then assign it to
BodyText. A simple example:

Sub a()
Dim AllText As String
Dim Cell As Range
For Each Cell In Selection
AllText = AllText & " " & Cell.Value
Next
MsgBox AllText
End Sub

I've added a space between each cell's text. You may or may not need that.

--
Jim Rech
Excel MVP
|
| That is more progress than I had. This time it returns the top left
cell.value of the range I select.
| ----- Jim Rech wrote: -----
|
| It looks like it should work (without running it). Does using:
|
| gwMessage.BodyText = TxtRange.Cells(1).Value
|
| help?
|
| --
| Jim Rech
| Excel MVP
| | | Everything works fine if I use text for
| gwMessage.BodyText="adkfasldjfasdfasdfs" but if I use an
inputbox for
| user prompt and go (TxtRange= an input box) gwMessage.BodyText=
TxtRange
| I get an error.
| |
| | Does anyone know how to have the user click on the text that they
want
| sent. My input box works on other appliations for text, I don't see
why it
| won't work with this?
| |
| |
| | Private Sub CommandButton7_Click()
| | Dim gwMessage As GroupwareTypeLibrary.Message2
| | Dim gwaccount As GroupwareTypeLibrary.Account2
| | Dim gwapp As GroupwareTypeLibrary.Application
| | Dim gwattach As GroupwareTypeLibrary.Attachment
| | Dim rs As Variant
| | Dim WhoTo As String
| | Dim Person As String
| | Dim TxtRange As Range
| | Dim Cancel As Boolean
| |
| |
| |
| |
| |
| |
| | If Application.UserName = "Jason" Then
| |
| |
| | WhoTo = InputBox("Name?", "Send EMail")
| | If Cancel Then
| | Exit Sub
| | End If
| |
| | If WhoTo = "Jason" Then
| | Person = "(e-mail address removed)"
| | End If
| |
| | Set gwapp = CreateObject("NovellGroupWareSession")
| | Set gwaccount = gwapp.Login(MyUserName, , MyPassword)
| | Set gwMessage = gwaccount.MailBox.Messages.add
| |
| | Set TxtRange = Application.InputBox(prompt:="Click on the text you
want
| sent", Title:="Text Selection", Type:=8)
| | TxtRange.Select
| | gwMessage.BodyText = TxtRange
| |
| | gwMessage.Subject = "Spreadsheet Stuff"
| | gwMessage.Recipients.add Person
| | gwMessage.Send
| | End If
| | End If
| | End Sub
|
|
|

 

[Jason]

I was hoping to avoid any type of loop, but it didn't seem to slow anything down. It works great. Thanks a lot to all of you. Sending Emails this way saves me a lot of time
I just had one thought though. Is there a way to insert a return instead of a space in the code you gave me
AllText = AllText & " " & Cell.Valu
----- Jim Rech wrote: ----

Right, so echoing what Jake said, you can only use .Value with one cell. If
you want to have the text of multiple cells get into the message you'll have
to build up a string of each cell's text first, and then assign it to
BodyText. A simple example

Sub a(
Dim AllText As Strin
Dim Cell As Rang
For Each Cell In Selectio
AllText = AllText & " " & Cell.Valu
Nex
MsgBox AllTex
End Su

I've added a space between each cell's text. You may or may not need that

--
Jim Rec
Excel MV

| That is more progress than I had. This time it returns the top left
cell.value of the range I select
| ----- Jim Rech wrote: ----

| It looks like it should work (without running it). Does using

| gwMessage.BodyText = TxtRange.Cells(1).Valu

| help

| --
| Jim Rec
| Excel MV
| | | Everything works fine if I use text fo
| gwMessage.BodyText="adkfasldjfasdfasdfs" but if I use an
inputbox fo
| user prompt and go (TxtRange= an input box) gwMessage.BodyText=
TxtRang
| I get an error
|
| | Does anyone know how to have the user click on the text that they
wan
| sent. My input box works on other appliations for text, I don't see
why i
| won't work with this
|
|
| | Private Sub CommandButton7_Click(
| | Dim gwMessage As GroupwareTypeLibrary.Message
| | Dim gwaccount As GroupwareTypeLibrary.Account
| | Dim gwapp As GroupwareTypeLibrary.Applicatio
| | Dim gwattach As GroupwareTypeLibrary.Attachmen
| | Dim rs As Varian
| | Dim WhoTo As Strin
| | Dim Person As Strin
| | Dim TxtRange As Rang
| | Dim Cancel As Boolea
|
|
|
|
|
|
| | If Application.UserName = "Jason" The
|
|
| | WhoTo = InputBox("Name?", "Send EMail"
| | If Cancel The
| | Exit Su
| | End I
|
| | If WhoTo = "Jason" The
| | Person = "(e-mail address removed)
| | End I
|
| | Set gwapp = CreateObject("NovellGroupWareSession"
| | Set gwaccount = gwapp.Login(MyUserName, , MyPassword
| | Set gwMessage = gwaccount.MailBox.Messages.ad
|
| | Set TxtRange = Application.InputBox(prompt:="Click on the text you
wan
| sent", Title:="Text Selection", Type:=8
| | TxtRange.Selec
| | gwMessage.BodyText = TxtRang
|
| | gwMessage.Subject = "Spreadsheet Stuff
| | gwMessage.Recipients.add Perso
| | gwMessage.Sen
| | End I
| | End I
| | End Su


|

 

[Jim Rech]

I'd modify the example for a carriage return to look like this:

Sub a()
Dim AllText As String
Dim Cell As Range
For Each Cell In Selection
AllText = AllText & Cell.Value & Chr(10)
Next
''Remove last CR
AllText = Left(AllText, Len(AllText) - 1)
MsgBox AllText
End Sub


--
Jim Rech
Excel MVP
|
| I was hoping to avoid any type of loop, but it didn't seem to slow
anything down. It works great. Thanks a lot to all of you. Sending Emails
this way saves me a lot of time.
| I just had one thought though. Is there a way to insert a return instead
of a space in the code you gave me?
| AllText = AllText & " " & Cell.Value
| ----- Jim Rech wrote: -----
|
| >> This time it returns the top left cell.value of the range I select
|
| Right, so echoing what Jake said, you can only use .Value with one
cell. If
| you want to have the text of multiple cells get into the message
you'll have
| to build up a string of each cell's text first, and then assign it to
| BodyText. A simple example:
|
| Sub a()
| Dim AllText As String
| Dim Cell As Range
| For Each Cell In Selection
| AllText = AllText & " " & Cell.Value
| Next
| MsgBox AllText
| End Sub
|
| I've added a space between each cell's text. You may or may not need
that.
|
| --
| Jim Rech
| Excel MVP
| | |
| | That is more progress than I had. This time it returns the top
left
| cell.value of the range I select.
| | ----- Jim Rech wrote: -----
| |
| | It looks like it should work (without running it). Does using:
| |
| | gwMessage.BodyText = TxtRange.Cells(1).Value
| |
| | help?
| |
| | --
| | Jim Rech
| | Excel MVP
| | | | | Everything works fine if I use text for
| | gwMessage.BodyText="adkfasldjfasdfasdfs" but if I use an
| inputbox for
| | user prompt and go (TxtRange= an input box)
gwMessage.BodyText=
| TxtRange
| | I get an error.
| | |
| | | Does anyone know how to have the user click on the text that
they
| want
| | sent. My input box works on other appliations for text, I don't
see
| why it
| | won't work with this?
| | |
| | |
| | | Private Sub CommandButton7_Click()
| | | Dim gwMessage As GroupwareTypeLibrary.Message2
| | | Dim gwaccount As GroupwareTypeLibrary.Account2
| | | Dim gwapp As GroupwareTypeLibrary.Application
| | | Dim gwattach As GroupwareTypeLibrary.Attachment
| | | Dim rs As Variant
| | | Dim WhoTo As String
| | | Dim Person As String
| | | Dim TxtRange As Range
| | | Dim Cancel As Boolean
| | |
| | |
| | |
| | |
| | |
| | |
| | | If Application.UserName = "Jason" Then
| | |
| | |
| | | WhoTo = InputBox("Name?", "Send EMail")
| | | If Cancel Then
| | | Exit Sub
| | | End If
| | |
| | | If WhoTo = "Jason" Then
| | | Person = "(e-mail address removed)"
| | | End If
| | |
| | | Set gwapp = CreateObject("NovellGroupWareSession")
| | | Set gwaccount = gwapp.Login(MyUserName, , MyPassword)
| | | Set gwMessage = gwaccount.MailBox.Messages.add
| | |
| | | Set TxtRange = Application.InputBox(prompt:="Click on the text
you
| want
| | sent", Title:="Text Selection", Type:=8)
| | | TxtRange.Select
| | | gwMessage.BodyText = TxtRange
| | |
| | | gwMessage.Subject = "Spreadsheet Stuff"
| | | gwMessage.Recipients.add Person
| | | gwMessage.Send
| | | End If
| | | End If
| | | End Sub
| |
| |
| |
|
|
|