Looping to increment variables

[JohnDurbin]

I have an Outlook macro for creating reply to messages. I will allow users to include up to 5 attachments. I want to allow the users to modify the part of the vba which has the names of the attachment files which are stored at C:\EmailMsg.
So the users would provide the file names like this:
strAttach1 = "ExpenseReports.pdf"
strAttach2 = "AccountPayableReport.pdf"
strAttach3 = ""
strAttach4 = ""
strAttach5 = ""
But I want to loop while i'm adding the attachments. Something like this:
For x = 1 To 5
objMail.Attachments.Add ("C:\EmailMsg\" & strAttach & x)
Next x
As I'm sure you know, this results are this this when I debug.print:
C:\EmailMsg\strAttach1
C:\EmailMsg\strAttach2
C:\EmailMsg\strAttach3
C:\EmailMsg\strAttach4
C:\EmailMsg\strAttach5
Instead of:

C:\EmailMsg\ExpenseReports.pdf
C:\EmailMsg\AccountPayableReport.pdf
C:\EmailMsg\
C:\EmailMsg\
C:\EmailMsg\
Any suggestions?...JD

[gmayor]

That's easy enough to handle. You need to check if the file exists before attempting to add it e.g.


For x = 1 To 5
If FileExists("C:\EmailMsg\" & strAttach & x) Then
objMail.Attachments.Add ("C:\EmailMsg\" & strAttach & x)
End If
Next x

This uses the following function in the same module to establish whether the file exists.


Private Function FileExists(filespec) As Boolean
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(filespec) Then
FileExists = True
Else
FileExists = False
End If
lbl_Exit:
Exit Function
End Function

[JohnDurbin]

That's easy enough to handle. You need to check if the file exists before attempting to add it e.g.


For x = 1 To 5
If FileExists("C:\EmailMsg\" & strAttach & x) Then
objMail.Attachments.Add ("C:\EmailMsg\" & strAttach & x)
End If
Next x

This uses the following function in the same module to establish whether the file exists.


Private Function FileExists(filespec) As Boolean
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(filespec) Then
FileExists = True
Else
FileExists = False
End If
lbl_Exit:
Exit Function
End Function

That doesn't do it.



THIS LINE: objMail.Attachments.Add ("C:\EmailMsg\" & strAttach & x)

Doesn't create what I need. When I debug.print it loops together strAttach & x which results in the path:

C:\EmailMsg\strAttach1
C:\EmailMsg\strAttach2


strAttach1 = "ExpenseReports.pdf"
strAttach2 = "AccountPayableReport.pdf"

Instead I need code that will result in the path:

C:\EmailMsg\ExpenseReports.pdf
C:\EmailMsg\AccountsPayableReport.pdf

Any suggestions?...JD

[JohnDurbin]

I need strAttach1 to be treated as a variable.

When I do this: strAttach = strAttach & x

strAttach1 becomes a string and produces a path like this: C:\EmailMsg\strAttach1

since strAttach1 = "ExpenseReports.pdf" I need strAttach & x to become the variable.

[JohnDurbin]

I need strAttach1 to be treated as a variable.

When I do this: strAttach = strAttach & x

strAttach1 becomes a string and produces a path like this: C:\EmailMsg\strAttach1

since strAttach1 = "ExpenseReports.pdf" I need strAttach & x to become the variable.


I ended up going with an array. Something like this:

Dim myAttach As Variant
For Each myAttach In Array(strAttach1, strAttach2, strAttach3, strAttach4, strAttach5)
objMail.Attachments.Add ("C:\EmailMsg\" & myAttach)
Next myAttach