VB form attaching more than one file

stevehorton09 · Aug 23rd 2004

Hi everyone,

I have a form that attaches files to emails out of a specific folder.

My problem is i can only select one file from the folder and i need to attach more than one.

Does a0nyone know how to allow multiple attachments, i have tried "ctrl" and clicking on more than one but no joy and "shift" and the arrow keys.

Any ideas on this?

Here is my code

Code

Private Sub CommandButton3_Click()




' setting and attaching the work book
Dim MyPath As String
Dim SaveDriveDir As String
Dim saveasname As String


SaveDriveDir = CurDir
MyPath = "c:\eform\local"


ChDrive MyPath
ChDir MyPath


Attachment1 = GetAttach
attach.Text = Attachment1




ChDrive SaveDriveDir
ChDir SaveDriveDir




End Sub

Display More

bnix · Aug 23rd 2004

Can you show us the code for the GetAttach procedure? It looks like that is where you need to adjust the code.

Bruce

stevehorton09 · Aug 23rd 2004

sorry i was supposed to attach that too!

Private Function GetAttach() As String
Dim strFileFullPath As String

strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls")
If strFileFullPath = "False" Then Exit Function
GetAttach = strFileFullPath

End Function

cheers

bnix · Aug 23rd 2004

Pass the MultiSelect parameter as True and you will be able to select multiple files, the default is False

Code

Application.GetOpenFilename("Xl Files (*.xls), *.xls"),,,True)

stevehorton09 · Aug 23rd 2004

cool

having a bad day as should have found

just for anyone reading this it is four commas as below (but you would see that anyway!)

Application.GetOpenFilename("Xl Files (*.xls), *.xls", , , , True)

Thanks very much for your help that is great

stevehorton09 · Aug 23rd 2004

just tested it and it says it is a mismatch

any idea!!

bnix · Aug 23rd 2004

it is probably happening because if you are selecting more than none file, you now have an Array not just a string.

Try adjusting as follows

Code

Private Function GetAttach() As Variant
Dim strFileFullPath As Variant


strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls"),,,,True)
If Not IsArray(strFileFullPath) Then
    MsgBox "No File Selected"
    Exit Sub
Else
GetAttach = strFileFullPath
End If
End Function

Display More

You would then have to loop through the resulting array. I have not tested any of this, just going off the top of my head, so there may be some errors. I will try to test in a few.

Bruce

stevehorton09 · Aug 23rd 2004

i have had a look and the code will not match the mypath stuff, i have corrected the errors i found but still lost.

i hope this helps

The two parts of the code are below

Private Function GetAttach() As Variant
Dim strFileFullPath As Variant

strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls", , , , True)
If Not IsArray(strFileFullPath) Then
MsgBox "No File Selected"
Unload Me
Else
GetAttach = strFileFullPath
End If
End Function

Private Sub CommandButton3_Click()

' setting and attaching the work book
Dim MyPath As String
Dim SaveDriveDir As String
Dim saveasname As String

SaveDriveDir = CurDir
MyPath = "c:\eform\local"

ChDrive MyPath
ChDir MyPath

Attachment1 = GetAttach
attach.Text = Attachment1

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

bnix · Aug 23rd 2004

Try this

Code

Private Function GetAttach() As String
Dim strFileFullPath As Variant
Dim i As Integer
strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls", , , , True)
If Not IsArray(strFileFullPath) Then
MsgBox "No File Selected"
Unload Me
Else
For i = LBound(strFileFullPath) To UBound(strFileFullPath)
GetAttach = GetAttach & strFileFullPath & ";"
Next i
End If
End Function

Display More

stevehorton09 · Aug 23rd 2004

still no joy,

does not like

GetAttach = GetAttach & strFileFullPath & ";"

any idea on adifferent approach to the mypath, do you think that is the problem

bnix · Aug 23rd 2004

Sorry Steve, Left out one important part the index of the array

Code

Private Function GetAttach() As String 
    Dim strFileFullPath As Variant 
    Dim i As Integer 
    strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls", , , , True) 
    If Not IsArray(strFileFullPath) Then 
        MsgBox "No File Selected" 
        Unload Me 
    Else 
        For i = LBound(strFileFullPath) To UBound(strFileFullPath) 


            GetAttach = GetAttach & strFileFullPath(i) & ";" '<added index (i)
        Next i 
    End If 
End Function

Display More

stevehorton09 · Aug 23rd 2004

HEY HEY IT WORKS!

Could you quickly explain how! im a bit lost on how it works

bnix · Aug 23rd 2004

I have added some comments to the code

Code

Private Function GetAttach() As String 
'function returns a string


    Dim strFileFullPath As Variant 'variant will hold array of multiple selected files 
    Dim i As Integer 
    strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls", , , , True) 
    'if the array is nothing the user has not selected any files
    If Not IsArray(strFileFullPath) Then 
        MsgBox "No File Selected" 
        Unload Me 
    Else 
        'now loop through all of the selected files
        'LBound is the lower bound of the array usually set to zero
        'UBound is the upper bound of the array i.e., the number of files selected by user
        For i = LBound(strFileFullPath) To UBound(strFileFullPath) 
             'now concatenate each member of the array to the string
            GetAttach = GetAttach & strFileFullPath(i) & ";" '<added index (i)
        Next i 
    End If 
End Function

Display More

HTH

Bruce

stevehorton09 · Aug 23rd 2004

it was looking so good untill i sent it through lotus notes!

I need to some how get " between the file name i.e

"c.xls" "d.xls" (exactley like this)

It does not like ;

I have checked lotus notes and when i manually do this they look like my example above.

is this possible to have " either end of the file name(s)?

basically if i had file c.xls and d.xls, i would need them to concatenate as follows:-

"c.xls" "d.xls"

stevehorton09 · Aug 23rd 2004

i have worked out how to do the " but it is not attaching at all, it must be the mypath stuff!!!

your help would be much appreciated

bnix · Aug 23rd 2004

What is it that is not attaching? Are the files not attaching to a message or is the data not being displayed on the form?

Can you attach the workbook, I do not have Lotus, but maybe I can step through some things.

Bruce

stevehorton09 · Aug 24th 2004

i have attached the file, this is really annoying me now!!! ha ha

hope this helps

bnix · Aug 24th 2004

I do not have Notes on my machine, so I cannot step completely through it, but from I have seen everything looks fine. You may try stripping the actual path from the file name and see if that helps. Also, can you display the message before sending to see what it is actually attaching?

Bruce

stevehorton09 · Aug 24th 2004

I have actually worked it out now.

I just set more attachment boxes and changed the code slightly.

Thanks very much for you help as i would not have got there without your help

cheers again

bnix · Aug 24th 2004

Glad you got it worked out!

Bruce

VB form attaching more than one file

Participate now!

Share

Users Viewing This Thread