VBA语法，以排除附加到批量电子邮件发送.XLS文件类型

Question

有一个关于附加的快速问题。 部分 -我有一些 VBA，它会询问用户是否要附加任何其他文档(Atach = MsgBox("您是否希望附加任何其他文档"，vbYesNo(到循环电子邮件任务 - 但是，我需要找到一种更智能的方式来提醒用户.xls文件类型无法附加并且不允许他们仅附加该文件类型。

已经尝试了语法，但我认为我错过了一些东西。我是 VBA 的新手，所以感谢您的所有反馈。

Application.ScreenUpdating = False
Dim OutApp As Object
Dim OutMail As Object
Dim OutWordEditor As Object
Dim Tocell As Range
Dim SigString As String
Dim t               '' Overall timer after selecting files
Dim i As Integer    '' ASLS looping
Dim j As String    '' extra attachment
Dim x As Single     '' Delay timer
Dim valid As Integer
Dim titlestring As String
Dim FileW As Boolean
Dim Attachrng As String
Dim attach As String
Dim Worddoc As Object
Dim WordFile As String
Dim ToRangeCounter As Variant
Dim attachrange() As String
i = 0
j = 0
valid = 0
FileW = False
WordFile = ""
Atach = MsgBox("You have selected the Notifications Auto Email Macro",    vbYesNo)
If Atach = vbNo Then
            MsgBox ("Macro will now close")
            Exit Sub
            End If
WordFile = ""
FileW = False
Do Until FileW = True
WordFile = Application.GetOpenFilename(Title:="Select Notificatin to send as the email body", MultiSelect:=False)
Call Preview(WordFile)
Usercheck = MsgBox("Confirm this is correct ", vbYesNo)
If Usercheck = vbYes Then
FileW = True
End If
Loop
Else
MsgBox ("Please re select the file you wish to attach")
End If
Loop

***
Atach = MsgBox("Do you wish to attach any additional Documents", vbYesNo)
If Atach = vbYes Then
    For k = 0 To 1
    j = InputBox("How many files do you wish to attach?" & vbNewLine & "Only enter a number", "Enter a number here", 1)
    If Not IsNumeric(j) Then
        MsgBox ("You have failed to enter a number, please try again")
        k = 0
        valid = valid + 1
        If valid = 3 Then
            MsgBox ("You have failed to input a number 3 times" & vbNewLine & "Macro will now close")
            Exit Sub
            End If
        Else
            k = k + 1
            ReDim Preserve attachrange(j)
        End If
    Next

---

For Z = 1 To j
   titlestring = "You are attaching file number " & Z & " of " & j
   attachrange(Z) = Application.GetOpenFilename(Title:=titlestring, MultiSelect:=False)
   Next
  End If

On Error GoTo Cleanup

Answer 1

如果要使用Word的FileDialog，则可以做两件事。第一，您可以过滤掉xlx文件。他们不会出现在选择中。第二，您可以允许多项选择，从而消除用户想要添加多少的问题。您可以根据需要调整以下代码。

Function FilePicker(ByVal Tit As String, _
                    ByVal Btn As String, _
                    ByVal FltDesc As String, _
                    ByVal Flt As String, _
                    Fn As String, _
                    Optional Multi As Boolean = False) As Boolean
    ' 22 Mar 2017
    ' ==================================================
    '   Parameters:
    '       Tit               = Title
    '       Btn               = Button caption
    '       FltDesc           = Plain language filter
    '       Flt               = Filter string
    '       Fn                = Initial file name
    '                           Returns selected full file name
    ' ==================================================
    Dim FoD As FileDialog                           ' FilePicker Dialog
    ' ==================================================
    Set FoD = Application.FileDialog(msoFileDialogFilePicker)
    With FoD
        .Title = Tit
        .ButtonName = Btn
        .Filters.Clear
        .Filters.Add FltDesc, Flt, 1
        .AllowMultiSelect = Multi
        .InitialFileName = Fn
        If .Show Then
            Fn = .SelectedItems(1)
            FilePicker = True
        End If
    End With
    Set FoD = Nothing
End Function

然后，您可以处理用户附加的文件(可能使用其他方法或通过修改 FileDialog(并检查附加文件的 FileFormat 属性。删除任何您不批准的内容。查找 xl文件格式常量以标识特定的文件格式。