Passing variable between subs with user form

Question

I have a user form that mimics an email form (To, CC, attachment, body, subject etc) with the addition of a list box populated with a list of names. User selects a name and the "To:" list box gets populated with the appropriate email addresses. Next the FileDialogOpen pops up and user selects attachments (attachement box popluates with names of files selected) . Here's where it gets tricky for me.

After selecting attachments the user then can fill in the "Subject" and "Body" and click OK button that calls the SendEmail sub, however the actual attachments paths can't be passed because that sub (GetFiles) has already completed. How do I store the file paths to be recalled later for the SendEmail sub? Here's my snippets of what I have already.

Sub GetFiles() 'Multi File picker dialog box

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vSelectedItems As Variant

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
GetFile = Application.FileDialog(msoFileDialogOpen).Show

If GetFile <> 0 Then
    For i = 1 To Application.FileDialog(msoFileDialogOpen).SelectedItems.Count
        strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(i)
    Next i
End If
With fd
For Each vSelectedItems In .SelectedItems
    Items = vrtSelectedItems
Me.AttachBox.AddItem vSelectedItems

Next vSelectedItems
End With

and the SendEmail attached to the OK button click after user has verified information to be emailed is correct.

Private Sub OKButton_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim sEmail As Variant
Dim myArray As Variant

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

myArray = ListBox1.List(ListBox1.ListIndex, 0) 'retrieving name selected and adding          email addresses
Set found = Cells.Find(What:=myArray, After:=ActiveCell, LookIn:=xlFormulas, Lookat:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)

Set sEmail = Range(found.Offset(0, 1), found.End(xlToRight))
sEmail.Copy

sRecipient = ""
For Each Item In sEmail
       sRecipient = sRecipient & ";" & Item.Value
Next

On Error Resume Next
With OutMail
    .To = sRecipient
    .CC = CC.Value
    .BCC = ""
    '.FROM = ""
    .Subject = Subject.Value
    .Body = Body.Value
     For Each vSelectedItems In GetFile 'no passing of file paths :(
        .Attachments.Add Item
     Next vSelectedItems
    .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Range("A1").Activate
Call CancelButton_Click
End Sub

Answer could be staring me in the face but I'm no expert nor new. Thanks in advance.

Can you use global variables? http://stackoverflow.com/questions/2722146/ — dcromley, Jul 03 '14 at 15:44

Socii · Accepted Answer · 2014-07-03T19:23:02.143

1

Try declaring the vSelectedItems variable outside of the GetFiles sub. This will make the variable available at module level so the OKButton_Click() sub can access it.

Private vSelectedItems as Variant

Sub GetFiles()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
    GetFile = Application.FileDialog(msoFileDialogOpen).Show

    If GetFile <> 0 Then
        ReDim vSelectedItems(Application.FileDialog(msoFileDialogOpen).SelectedItems.Count)
        For Each Item In Application.FileDialog(msoFileDialogOpen).SelectedItems
            vSelectedItems(i) = Item
            Me.AttachBox.AddItem vSelectedItems(i)
            i = i + 1
        Next Item
    End If

End Sub

Private Sub OKButton_Click()

    <... code ...>

    .Body = Body.Value
    For Each Item In vSelectedItems
        .Attachments.Add Item
    Next Item
    .Send

    <... code ...>

End Sub

edited Jul 03 '14 at 19:23

answered Jul 03 '14 at 15:45

Socii

535
4
13

Thanks, I get the concept but I think my new problem lies here `For Each vSelectedItems In GetFile 'no passing of file paths :( .Attachments.Add Item Next vSelectedItems` in the For Each In. What should the "In" be referencing? – mrbungle Jul 03 '14 at 16:06
This would need to be changed to iterate through the vSelectedItems array: For Each Item in vSelectedItems – Socii Jul 03 '14 at 16:33
I think the real issue is that once the user selects the files the macro essentially stops so user can verify/edit info in the Body/Subject fields and the vSelectedItems variable empties. Macro starts back up with the OKButton_Click. Am I correct and assuming the variable empties? I've thought about temporally store the file paths on a different sheet and call those up in the OKButton_Click. Regardless, Thanks for your help and suggestions. Cheers! – mrbungle Jul 03 '14 at 16:56
I've spotted that the vSelectedItems variable wasn't being populated by the GetFiles sub. I've updated the code above. vSelectedItems will be an Array and the size will depend on the number of files selected. The For Each loop will populate the Array with the filepaths, and also add the filepath to the AttachBox. The vSelectedItems Array will then be accessable by the OK_Button_Click sub – Socii Jul 03 '14 at 17:44
Thanks so much! That just about has it going except one thing. It's only attaching one file. I can step through the OK_Button and I can see the For Each Item loop through twice (I selected two files) but only the last file from the GetFiles For Each is left populated in the Array. Strange it knows the array size but only sees the last file path. – mrbungle Jul 03 '14 at 19:12
Ah, the vSelectedItems(i) = Item code in the GetFiles() sub is the problem. It needs to be incremented each time a new Item is added, otherwise it will add each Item into the first Array position. Code has been updated to inlcude i = i + 1 – Socii Jul 03 '14 at 19:25
Thanks you so much! Not only did you provide the answer but I learned something new and added some tools to my arsenal. I really do appreciate the help. Have a great long weekend. – mrbungle Jul 03 '14 at 19:31
No problems. Glad to help – Socii Jul 03 '14 at 19:35

Passing variable between subs with user form

1 Answers1