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

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]


Jason wrote:
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 = "
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

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

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

help?

--
Jim Rech
Excel MVP
"Jason" wrote in message
...
| 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 = "
| 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

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
"Jason" wrote in message
...
|
| 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
| "Jason" wrote in message
| ...
| | 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 = "
| | 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
|
|
|

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

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

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
"Jason" wrote in message
..

| 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
| "Jason" wrote in messag
| ..
| | 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 =
| | 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


|

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
"Jason" wrote in message
...
|
| 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
| "Jason" wrote in message
| ...
| |
| | 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
| | "Jason" wrote in message
| | ...
| | | 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 = "
| | | 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
| |
| |
| |
|
|
|