VBA to VBA Script Function

D

Danny Young

Hello:

An answer to one of the following would be greatly appreciated.

(1) I am trying to send data to a specific location in a VBA script from a
cell on its respective spreadsheet.
Is this possible?
If so, how?

(2) If not, is it possible to send data from a cell on a spreadsheet to a
DOS Batch File?
If so, how?

(3) Is it possible to create a Macro that records the actions in the VBA
Script Editor?
If so, how?

I am trying to pass data from a cell to a VBA Script, which is a mailing
program for Novell Groupwise; I need to allow input of different e-mail
addresses, messages, and subjects.

As I am fairly new to VBA I would appreciate layman's terms, if that is
possible.

Again, any help would be greatly appreciated.

Thank you,

Danny
 
D

Don Guillett

I think you are looking at using variables in your macro such as

myvalue=range("a2").value
msgbox myvalue

Perhaps if you posted your code and requirements.
 
D

Danny Young

Hi, Don;

thank you for the speedy response. Here's the code I am currently working
with:

Sub SNMV()
Dim gwApp As Object
Dim gwRootAccount As Object
Dim oMessage As Object
Dim oMessages As Object
Dim strMessage As String

'Create the GroupWise application object.
Set gwApp = CreateObject("NovellGroupWareSession")
'Login to GroupWise. Since I should be already logged in, no password is
needed.
Set gwRootAccount = gwApp.Login("myusername")

'Open my mailbox.
Set oMessages = gwRootAccount.MailBox.Messages

'Crate a new blank message.
Set oMessage = oMessages.Add(" ")
strMessage = "message here"


'Set the message body and subject.
oMessage.BodyText = strMessage
oMessage.Subject = "The Modification Log has been Updated"

'Set the recipients.

'One internal address.
oMessage.Recipients.Add""
oMessage.Recipients.Resolve

'Send the message.
oMessage.Refresh
oMessage.Send
End Sub

I would like to pass data (variable?) from a userform, preferably a list
box, to the code lines below:

strMessage = "message variable?"
oMessage.Subject = "subject variable?"
oMessage.Recipients.Add"recipient variable?"

What do you think?

Again, much appreciated for the speedy reply. I look forward to hearing from
you.

| Danny
 
D

Danny Young

Hello, Don;

thanks for the speedy reply.

Here's the code I am currently working with:

Sub SNMV()
Dim gwApp As Object
Dim gwRootAccount As Object
Dim oMessage As Object
Dim oMessages As Object
Dim strMessage As String

'Create the GroupWise application object.
Set gwApp = CreateObject("NovellGroupWareSession")
'Login to GroupWise. Since I should be already logged in, no password is
needed.
Set gwRootAccount = gwApp.Login("myusername")

'Open my mailbox.
Set oMessages = gwRootAccount.MailBox.Messages

'Crate a new blank message.
Set oMessage = oMessages.Add(" ")
strMessage = "To view the changes to the log, click here:
X:\Marketing\Daniel R. Young\Modification Log"



'Set the message body and subject.
oMessage.BodyText = strMessage
oMessage.Subject = "The Modification Log has been Updated"

'Set the recipients.

'One internal address.
oMessage.Recipients.Add
oMessage.Recipients.Resolve

'Send the message.
oMessage.Refresh
oMessage.Send
End Sub

I would like to pass data (marked as "variable?") from a user form,
preferably a list box, to the following lines of code:

oMessage.Recipients.Add"variable? from form"
oMessage.Subject = "variable? from form"
strMessage = "variable? from form"

Again, thank you for the speedy reply; it's much appreciated; I look forward
to hearing from you.

| Danny |
 
N

NickHK

Do you have to do in a VBScript ?
Why not run it in Excel VBA, then it is more straight forward.

NickHK
 
D

Danny Young

Hello, NickHK;

Thank you for your suggestion; however, I believe I may have misrepresented
my question.

When I say "VBA Script," I am refering to the VBA window that is displayed
after selecting the "Visual Basic Editor" selection in the "Tools" menu.

Could you elaborate on what you mean by "Why not run it in Excel VBA,..." ?
As far as I know, I am in Excel VBA when I select the "Visual Basic Editor."
Am I correct?

Is there a function that I'm not using that would help me?

Your help thus far is greatly appreciated.

| Danny |
 
N

NickHK

Danny,
OK, I paid more attention to the "Script" part than the "VBA" part.

oMessage.Recipients.Add Userform1.ListBoxRecipients.Value
oMessage.Subject = Userform1.TextBoxSubject.Text
strMessage = Userform1.TextBoxMessage.Text

NickHK
 
D

Danny Young

Hi, NickHK;

I'm close, very close. Here's my dillema. I'm placing
UserForm1.TextBoxSubject.Text after the equals sign in the GroupWise VBA
Script (oMessage.Subject = "UserForm1.TextBoxSubject.Text")
I'm using the a text box from the drawing toolbar; for some reason the text
box available in VBA is not available on the sheet itself.

The error I seem to be encountering frequently is "Compile error: Method or
Data member not found."

I have a feeling I am missing a great amount of steps. Am I as close as I
think?

I believe my error lies in not linking the VBA script(s) with the text box
on the sheet, which I don't know how to do.

Your further assistance has been and will continue to be greatly appreciated.

| Danny |
 
N

NickHK

Danny,
I'm confused. Is your text box on the userform or on the worksheet ?

And you do not want the "" in
oMessage.Subject = "UserForm1.TextBoxSubject.Text"

NickHK
 
D

Danny Young

Hi, NickHK;

My Text Box is on the sheet.
The code for the text box is

Private Sub

TextBox1_Change()

End Sub

and I placed it in the same place as the original mail-script.
So, here's where I stand, specific to my code:


Private Sub

TextBox1_Change()

End Sub

---------------------------------------------------------------------------------
Sub SNMV()
Dim gwApp As Object
Dim gwRootAccount As Object
Dim oMessage As Object
Dim oMessages As Object
Dim strMessage As String

'Create the GroupWise application object.
Set gwApp = CreateObject("NovellGroupWareSession")
'Login to GroupWise. Since I should be already logged in, no password is
needed.
Set gwRootAccount = gwApp.Login("myusername")

'Open my mailbox.
Set oMessages = gwRootAccount.MailBox.Messages

'Crate a new blank message.
Set oMessage = oMessages.Add(" ")
strMessage = "To view the changes to the log, click here:
X:\Marketing\Daniel R. Young\Modification Log"


'Set the message body and subject.
oMessage.BodyText = strMessage
oMessage.Subject = UserForm1.TextBox1_Change().Text

'Set the recipients.

'One internal address.
oMessage.Recipients.Add "(e-mail address removed)"
oMessage.Recipients.Resolve

'Send the message.
oMessage.Refresh
oMessage.Send

End Sub

Hopefully this information will help

Thanks again

| Danny |
 
D

Danny Young

Hello, NickHK;

Well, I studied your last response, your question to me, long and hard and
it hit me: How could I possibly pass data from the sheet to the script if the
form isn't on the sheet? I applied what little I know to calling a userform
from the VBA Editor and applied your methods and it worked! I successfully
passed data from the userofrm
to the script.

NickHK: Thank you very much for all your help. Your persistance and patience
is greatly appreciated.

Sincerely,

Danny
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top