Passing variables not working

[talytech]

I've checked the forum for this particular issue and I found an example of what I want to do, but I can't make sense out of it and I can't get my code to work. Can somebody please help me?

I have a userform with a button on it that loops through rows on a spread sheet. then it takes the information from the cell and set it to a variable. then I call a function called sendNotice that sends an email with the variable "myName" in the body. I don't know how to pass the variable to my function. Here's my code that doesn't work and for the sake of shortening the code I didn't include all the declared variables.

Private Sub cmdUpdateDB_Click()
dim myName as string
 
lastrow = Cells(Rows.Count, "b").End(xlUp).Row

myName = ""
 
For I = 20 To lastrow
    'Get Fields
    ctrfname = Cells(I, "b").Value
    ctrlname = Cells(I, "f").Value
    ctrmid = Cells(I, "e").Value
    ctrSSN = Cells(I, "i").Value
    ctrDOB = Cells(I, "j").Value
    ctrVendor = Cells(I, "k").Value
    If myName = "" Then
        myName = "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
    Else
        myName = myName & Chr(13) & "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
    End If
Next
 
Call SendNotice
 
End Sub

--------------------------------------------------------------------

Function SendNotice(myName as string)
Set OLF = GetObject("", _
    "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
    .Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject
    
    Set ToContact = .Recipients.Add("somename@fanniemae.com") ' add a recipient
    Set ToContact = .Recipients.Add("somename@fanniemae.com")
    
    Set ToContact = .Recipients.Add("" & mgruserid & "")
    ToContact.Type = olCC ' set latest recipient as CC
    
    Set ToContact = .Recipients.Add("" & vpUserID & "")
    ToContact.Type = olCC ' set latest recipient as CC
    
    .Body = myName
    .Display 'Send ' sends the e-mail message (puts it in the Outbox)
End With
ENd Function

[CreganTur]

You need to provide a parameter value when you call the SendNotice function. Like this:
[VBA]Call SendNotice myName[/VBA]

This calls the function and provides the value of the variable myName to satisfy the function's parameter.

[Bob Phillips]

You need to provide a parameter value when you call the SendNotice function. Like this:
[VBA]Call SendNotice myName[/VBA]

This calls the function and provides the value of the variable myName to satisfy the function's parameter.

You mean like this

[VBA]Call SendNotice(myName)[/VBA]

[Bob Phillips]

BTW, I personally think it is a bad idea to have the name of an argument in the called procedure the same as the name of the variable passed in the calling procedure. It just might lead people to think it is the same variable, and it is not.

Someone will probably pount to a dozen instances where I have done it now

[Bob Phillips]

Also, just noticed that myName is used to load the email Body. Not exactly a descriptive argument name!

[talytech]

I tried that and I get the following compile error:

"ByRef argument type mismatch"

I don't know what's going on.

[Bob Phillips]

Where did you get that, which line, the call? It doesn't happen for me.

[talytech]

OK .. my code is long so I tried to shorten it and give you the simple form but maybe I've missed something. Hopefully this helps.

1. On my userform I have variables set to the cells on my spreadsheet and the variable "myName". For example ctrFname = whatever the value is in the associated cell. And myName = ""

2 Then if the variable "myName" = "" then set myName = ctrFname. Else set myName = myName & chr(13) & ctrFname

3. then the code goes to the next row and does the same thing.

4. then after I get the value of myName, I want to pass that value to my function called SendNotice and have it show up as the body for my email. For example: I have 3 rows so "myName" should look something like this:
Marcy
Jack
Tasha

[VBA]
Dim myName, results As String

lastrow = Cells(Rows.Count, "b").End(xlUp).Row

'CHECK FOR Exception record
myName = ""

For I = 20 To lastrow
'Get Fields

ctrfname = Cells(I, "b").Value
ctrlname = Cells(I, "f").Value
ctrmid = Cells(I, "e").Value
ctrSSN = Cells(I, "i").Value
ctrDOB = Cells(I, "j").Value
ctrVendor = Cells(I, "k").Value

If myName = "" Then
myName = "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
Else
myName = myName & Chr(13) & "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
End If

Next

Call SendNotice(myName)
[/VBA]


[VBA]Function SendNotice(myName as string)

Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient


Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderIn box)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
.Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject

Set ToContact = .Recipients.Add("somename@fanniemae.com") ' add a recipient
Set ToContact = .Recipients.Add("somename@fanniemae.com")

Set ToContact = .Recipients.Add("" & mgruserid & "")
ToContact.Type = olCC ' set latest recipient as CC

Set ToContact = .Recipients.Add("" & vpUserID & "")
ToContact.Type = olCC ' set latest recipient as CC

.Body = myName
.Display 'Send ' sends the e-mail message (puts it in the Outbox)
End With
[/VBA]

[Kenneth Hobs]

It is a bit hard to troubleshoot since I don't have your data and not all varialbes declared.

Can you post a sample xls or sample data for the loop range?

What should the string that you are passing look like based on the data?

Have you tried sending a hard coded string to see if it does what you want.

Have you considered using CDO?

[talytech]

OK .. I fixed it. I had to tell my function how I wanted to use the variable .. like this:

[VBA]Function SendNotice(ByVal msgNames As String)[/VBA]

That worked.

Now I have another issue ... what if I want to pass values between two subs? For instance, on my UserForm .. I call up another userform with a listbox. I set the same variables on that form but I want the values to be passed to my first userform so that it then can be passed to SendNotice function. Is that doing way too much? I am almost finished this project .. please tell me that is possible.

[talytech]

Actually, I didn't explain my next issue good enough.

1. From one user form (UserForm A), I have a button that loops through the rows just like in my previous thread and then calls or Shows another UserForm (UserForm B)

2. On userForm B on click of a button, I have a variable called "updateName" which is set to whatever the value is in the cell of the corresponding row.
[vba]ctrlname = Cells(myRow, "f").Value
updateName = ctrfname[/vba]

3. The code closes userForm B and continues with the next row in the loop.

4. I want the value of updateName from userForm B to be passed to UserForm A for later use ... to be used after my Loop finishes. So like in my previous thread the names can show up in a list and be passed to the SendNotice function.

[Bob Phillips]

Declare a public variable, and store the value there. It is the simplest way.

[talytech]

I tried to declare the public variable but it gives me an error: "Invalid attribute in Sub or Function".

where should I be putting the declaration? This is what I tried to do:
[VBA]Private Sub ExistBI_Click()
Public updateName As String
[/VBA]

[Bob Phillips]

Public variables are declared in a standard code module, before any procedure.

[mdmackillop]

From Post #8
Dim myName, results As String

This is only dimming "Results" as String, not both variables. myName is Variant, hence the mismatch.

Dim myName as String, results As String

[talytech]

OK .. thanks guys .. I figured it out and it works. Thanks for all of your help. You all helped me to complete my project. However, there is one last thing I would like to do. Between my loops the code takes a while to finish the events. I don't want the user to think that it's not working because it looks like it isn't doing something. Is there a way I can change the mouse pointer from an arrow to an hourglass so that the user knows the program is still running? I'm searching the forum for that now. but any suggestions would be great. Thanks again for all of your help.

[Bob Phillips]

[vba]

Application.Cursor=xlWait

'then

Application.Cursor=xlDefault
[/vba]

[mdmackillop]

You can also put a message in the Statusbar

StatusBar Property Example
This example sets the status bar text to "Please be patient..." before it opens the workbook Large.xls, and then it restores the default text.
[VBA]
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient...
"Workbooks.Open filename:="LARGE.XLS"
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
[/VBA]