excel 如何防止发送无附件的电子邮件

bmp9r5qi  于 2023-02-05  发布在  其他
关注(0)|答案(1)|浏览(250)

我把错误恢复旁边继续,如果它找不到电子邮件,但现在,如果它找不到附件,它仍然会发送到我需要它不发送没有附件,如果没有电子邮件仍然继续

Sub CreateStatement()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")

Dim EItem As Object

'Dim EApp As Outlook.Application
'Set EApp = New Outlook.Application

'Dim EItem As Outlook.MailItem
'Set EItem = EApp.CreateItem(olMailItem)
Dim path As String
path = "K:\E-Fax Invoicing\PDF Output\"

Dim RList As Range
Set RList = Range("A2", Range("a2").End(xlDown))

Dim R As Range

For Each R In RList

    Set EItem = EApp.CreateItem(0)

    With EItem
        On Error Resume Next
        .SentOnBehalfOfName = ""
        .To = R.Offset(0, 2)
        .Subject = "December Statement: "
        .Attachments.Add (path & R.Offset(0, 3))
        .Body = "Dear " & R & vbNewLine & vbNewLine _
        & "Please find your " & R.Offset(0, 4) & " attached." 
        On Error GoTo 0
        If .Attachments.Count > 0 Then
            .send
        Else
            next
        End If
    End With


Next R

Set EApp = Nothing
Set EItem = Nothing

End Sub
s5a0g9ez

s5a0g9ez1#

在尝试将文件添加为附件 * 之前 *,您可以检查文件是否存在:

Sub CreateStatement()
    Const PATH = "K:\E-Fax Invoicing\PDF Output\" 'use const for fixed values
    
    Dim EApp As Object, EItem As Object, RList As Range, R As Range
    Set EApp = CreateObject("Outlook.Application")
    
    Set RList = Range("A2", Range("a2").End(xlDown))
    
    For Each R In RList.Cells
    
        f = Dir(PATH & R.Offset(0, 3).Value) 'any matching file?
        
        Set EItem = EApp.CreateItem(0)
        With EItem
            .SentOnBehalfOfName = ""
            .To = R.Offset(0, 2)
            .Subject = "December Statement:"
            .Body = "Dear " & R.Value & vbNewLine & vbNewLine _
              & "Please find your " & R.Offset(0, 4).Value & " attached."
            
            If Len(f) > 0 Then
                .Attachments.Add PATH & f
                .send
            Else
                .display
            End If
        End With
    Next R
End Sub

相关问题