ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   refering to details in another sub (https://www.excelbanter.com/excel-programming/362247-refering-details-another-sub.html)

Noemi
refering to details in another sub
 
Hi
I have an option button which provides an Input Box once selected.

What I would like to know if there is some way I can refer to the
information provided to the input box in another Sub.

ie
First person selects opt button and they enter the information into an input
box called stMessage, When all other details on the form are entered the
person selects a command button called Email to send attach the form to an
email and send.

But I also need to add the information stored in stMessage to go into the
main body of the email.

What I dont know how to do is to pick-up the stMessage from the opt button
and use it in the command button sub.

Following is the Code I have

Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

Private Sub optAllComm_Click()

Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA & vbNewLine &
vbNewLine
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Any help would be great.
Thanks
Noemi

Tom Ogilvy
refering to details in another sub
 
-------------------
In a General Module
-------------------

Public stMessage As String
Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

-------
in the sheet module
-------

Private Sub optAllComm_Click()
' remove next line
' Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA _
& vbNewLine & vbNewLine & stMessage
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

--
Regards,
Tom Ogilvy



"Noemi" wrote in message
...
Hi
I have an option button which provides an Input Box once selected.

What I would like to know if there is some way I can refer to the
information provided to the input box in another Sub.

ie
First person selects opt button and they enter the information into an
input
box called stMessage, When all other details on the form are entered the
person selects a command button called Email to send attach the form to an
email and send.

But I also need to add the information stored in stMessage to go into the
main body of the email.

What I dont know how to do is to pick-up the stMessage from the opt button
and use it in the command button sub.

Following is the Code I have

Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

Private Sub optAllComm_Click()

Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA & vbNewLine &
vbNewLine
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Any help would be great.
Thanks
Noemi

Noemi
refering to details in another sub
 
Hi Tom
I tried it but stil did not work.

Any other ideas as how I can do this

"Tom Ogilvy" wrote:

-------------------
In a General Module
-------------------

Public stMessage As String
Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

-------
in the sheet module
-------

Private Sub optAllComm_Click()
' remove next line
' Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA _
& vbNewLine & vbNewLine & stMessage
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

--
Regards,
Tom Ogilvy



"Noemi" wrote in message
...
Hi
I have an option button which provides an Input Box once selected.

What I would like to know if there is some way I can refer to the
information provided to the input box in another Sub.

ie
First person selects opt button and they enter the information into an
input
box called stMessage, When all other details on the form are entered the
person selects a command button called Email to send attach the form to an
email and send.

But I also need to add the information stored in stMessage to go into the
main body of the email.

What I dont know how to do is to pick-up the stMessage from the opt button
and use it in the command button sub.

Following is the Code I have

Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

Private Sub optAllComm_Click()

Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA & vbNewLine &
vbNewLine
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Any help would be great.
Thanks
Noemi

Tom Ogilvy
refering to details in another sub
 
Obviously I didn't offer a method that I felt would not work. Based on what
you showed, and following my instructions, I would expect it to work.

I will admit I don't understand what this function should do (beyond
nothing)

Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function


--
Regards,
Tom Ogilvy

"Noemi" wrote in message
...
Hi Tom
I tried it but stil did not work.

Any other ideas as how I can do this

"Tom Ogilvy" wrote:

-------------------
In a General Module
-------------------

Public stMessage As String
Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

-------
in the sheet module
-------

Private Sub optAllComm_Click()
' remove next line
' Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA _
& vbNewLine & vbNewLine & stMessage
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

--
Regards,
Tom Ogilvy



"Noemi" wrote in message
...
Hi
I have an option button which provides an Input Box once selected.

What I would like to know if there is some way I can refer to the
information provided to the input box in another Sub.

ie
First person selects opt button and they enter the information into an
input
box called stMessage, When all other details on the form are entered
the
person selects a command button called Email to send attach the form
to an
email and send.

But I also need to add the information stored in stMessage to go into
the
main body of the email.

What I dont know how to do is to pick-up the stMessage from the opt
button
and use it in the command button sub.

Following is the Code I have

Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

Private Sub optAllComm_Click()

Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA &
vbNewLine &
vbNewLine
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Any help would be great.
Thanks
Noemi

Noemi
refering to details in another sub
 
I did not realise I was meant to remove the other code and only use
Public stMessage As String

but after your comment below I removed the other code and now it does work.
Thanks


"Tom Ogilvy" wrote:

Obviously I didn't offer a method that I felt would not work. Based on what
you showed, and following my instructions, I would expect it to work.

I will admit I don't understand what this function should do (beyond
nothing)

Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function


--
Regards,
Tom Ogilvy

"Noemi" wrote in message
...
Hi Tom
I tried it but stil did not work.

Any other ideas as how I can do this

"Tom Ogilvy" wrote:

-------------------
In a General Module
-------------------

Public stMessage As String
Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

-------
in the sheet module
-------

Private Sub optAllComm_Click()
' remove next line
' Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA _
& vbNewLine & vbNewLine & stMessage
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

--
Regards,
Tom Ogilvy



"Noemi" wrote in message
...
Hi
I have an option button which provides an Input Box once selected.

What I would like to know if there is some way I can refer to the
information provided to the input box in another Sub.

ie
First person selects opt button and they enter the information into an
input
box called stMessage, When all other details on the form are entered
the
person selects a command button called Email to send attach the form
to an
email and send.

But I also need to add the information stored in stMessage to go into
the
main body of the email.

What I dont know how to do is to pick-up the stMessage from the opt
button
and use it in the command button sub.

Following is the Code I have

Private Declare Function Message _
Lib "optAllComm" Alias "EmailMessage" _
(ByVal lpEmail As String, nSize As Long) As Long


Function EmailMessageA()

Dim lpEmail As String
Dim lngret As Long

EmailMessageA = lpEmail

End Function

Private Sub optAllComm_Click()

Dim stMessage As String

stMessage = Application.InputBox("Comments")

End Sub


Private Sub cmdActioned_Click()


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = stName
.CC = ""
.BCC = ""
.Subject = "QRP Actioned, No is " & stqrp
.Body = stName & "," & vbNewLine & EmailMessageA &
vbNewLine &
vbNewLine
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Any help would be great.
Thanks
Noemi


All times are GMT +1. The time now is 03:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com