using assigned variable in different modules

[buhay]

How do I use an assigned variable such as MsgMail that has been written in a module procedure in other module procedures.

For example:

The assigned variable MsgMail in procedure MailBody in module PublicSub:


Public MsgMail As String
____________________________________________________________________

Public Sub MailBody()



MsgMail = "Sehr geehrte Damen und Herren" & vbCrLf & _
"Dear ladies and gentlemen." & vbCrLf & " " & vbCrLf & _
"Enclosed you will find your monthly account statement. Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & " & vbCrLf & _


End Sub




I've been trying to use the variable MsgMail in the procedure SendMail() located in the module SendMail without any success:


Public MsgMail As String
____________________________________________________________________

SendMail()

Sub SendDocumentAsAttachment()

Dim bStarted As Boolean

Dim oOutlookApp As Outlook.Application

'You'll need to add the Outlook Object Library to VBA Tools References

Dim oItem As Outlook.MailItem

On Error Resume Next

If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved

ActiveDocument.Save 'so save it

End If

'see if Outlook is running and if so turn your attention there

Set oOutlookApp = GetObject(, "Outlook.Application")

If Err <> 0 Then 'Outlook isn't running

'So fire it up

Set oOutlookApp = CreateObject("Outlook.Application")

bStarted = True

End If

'Open a new e-mail message

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem 'and add the detail to it

.To = "Put here your E-Mail" 'send to this address


.Subject = "Depotauszug GGS0000061 per " & Format(Date, "dd/MM/yyyy") 'This is the message subject

.Body = MsgMail


.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue

.Display


End With

If bStarted Then 'If the macro started Outlook, stop it again.

oOutlookApp.Quit

End If

'Clean up

Set oItem = Nothing

Set oOutlookApp = Nothing

End Sub


Any idea how to make the following line".Body = MsgMail" so that the e-mail displays the text message assigned to the variable MailBody located in the SendMail() procedure?


Thanks in advance

[fumei]

That looks like:

Public MsgMail As String

is declared in both module PublicSub and module SendMail.

Is this correct?

[fumei]

This is cross-posted.

1. fire MailBody first, so MsgMail gets its value.

2. why does it gets its value as a Sub execution? Why not just make it a
CONSTANT string?

3. why are they in different modules anyway?

[buhay]

That looks like:

Public MsgMail As String

is declared in both module PublicSub and module SendMail.

Is this correct?

That's right. I've done it in hope to use the variable MsgMail in different module but without any success.

[buhay]

This is cross-posted.

1. fire MailBody first, so MsgMail gets its value.

I've tried to fire it first by calling the procedure Mailbody that is located in a different module but without any success

2. why does it gets its value as a Sub execution? Why not just make it a
CONSTANT string?

I made mistake while explaining it yesterday. I've done the correction above. It's supposed to get its value from the variable MsgMail

3. why are they in different modules anyway?

I want to edit the e-mail body from time to time without having to amend all the other modules that uses the same e-mail body text.

Thanks anyway for trying to help me out

[fumei]

You are not using Option Explicit? I suspect you are not because the code below:[vba]
Public Sub MailBody()



MsgMail = "Sehr geehrte Damen und Herren" & vbCrLf & _
"Dear ladies and gentlemen." & vbCrLf & " " & vbCrLf & _
"Enclosed you will find your monthly account statement. Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & " & vbCrLf & _

End Sub
[/vba]should fail as it is incorrect syntax - that last & _ needs to be followed by something.

Declare it once in a standard module. If it is declared as Public then it can be used anywhere in the project. Of course you must, again, run your procedure to give it a value first, before you use it.

Again, though, as it is simply a string, WHY execute a procedure to give the same value everytime. Declare it as a CONSTANT.[vba]
Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf
[/vba]without that extra & _

Now it can be used anywhere.
[vba].Body = MsgMail
[/vba]will simply get the value of MsgMail, defined as a constant.

[buhay]

You are not using Option Explicit? I suspect you are not because the code below:[vba]
Public Sub MailBody()



MsgMail = "Sehr geehrte Damen und Herren" & vbCrLf & _
"Dear ladies and gentlemen." & vbCrLf & " " & vbCrLf & _
"Enclosed you will find your monthly account statement. Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & " & vbCrLf & _

End Sub
[/vba]should fail as it is incorrect syntax - that last & _ needs to be followed by something.

Declare it once in a standard module. If it is declared as Public then it can be used anywhere in the project. Of course you must, again, run your procedure to give it a value first, before you use it.

Again, though, as it is simply a string, WHY execute a procedure to give the same value everytime. Declare it as a CONSTANT.[vba]
Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf
[/vba]without that extra & _

Now it can be used anywhere.
[vba].Body = MsgMail
[/vba]will simply get the value of MsgMail, defined as a constant.

this still doesn't work

Public Sub Mailbody()
Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf

.Body = MsgMail

it gives me a compile error "Invalid attribution in Sub or Function" after trying to run the MailBody()

[fumei]

You are declaring Const within the Sub!

This is "invalid".

In a standard module:[vba]

Option Explicit

Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf
[/vba]
As stated. MsgMail can be used anywhere.

BTW: please use the code tags when posting code.

You never declare Public variables or constants inside a procedure.

[buhay]

You are declaring Const within the Sub!

This is "invalid".

In a standard module:[vba]

Option Explicit

Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf
[/vba] As stated. MsgMail can be used anywhere.

BTW: please use the code tags when posting code.

You never declare Public variables or constants inside a procedure.

it works Thank you so much

[buhay]

You are declaring Const within the Sub!

This is "invalid".

In a standard module:[vba]

Option Explicit

Public Const MsgMail As String = "Sehr geehrte Damen und Herren" & _
vbCrLf & "Dear ladies and gentlemen." & vbCrLf & " " & _
vbCrLf & "Enclosed you will find your monthly account statement. " & _
"Please check the content" & vbCrLf & _
"of the statement(s) and let us know if you have questions to our data." & _
vbCrLf & " " & vbCrLf & vbCrLf & " " & vbCrLf & _
"Kind regards" & vbCrLf & vbCrLf
[/vba] As stated. MsgMail can be used anywhere.

BTW: please use the code tags when posting code.

You never declare Public variables or constants inside a procedure.

it works Thank you so much

[fumei]

You should read up on Scope. This tells where/when a variable is valid and can be used, and where/when it can hold a value, and when it is destroyed. This is a critical piece of knowledge for both preventing errors, as well as efficient use of code.

Only declaring variable for the Scope you need them, for example.