VBA error proofing attachment missing

[neveu]

hello Forum,

i'm using a for loop to attach several files to an email from a specific folder. the name of the file is the same as the information from a cell (+.tif extension).

i managed to make this work but i would like that if it happends that the file is not found in the specific folder , to promt a message to the user that the specific file is not found and display the message [rather than sending it] so that the user can attach manually the file.

If cell.Offset(0, 5).Value = "BONUSES" Then
         Set OutlookMsg = Outlook.CreateItem(olMailItem)
            With OutlookMsg
    ' set basic params
            .Subject = "Zahlung des Bonus" & Format(Date, " dd/mm/yyyy")
            .HTMLBody = "Sehr geehrte Damen und Herren, " & "<br><br>"
             For h = cell.Offset(0, 2).Value To cell.Offset(0, 3).Value ' 
            .Attachments.Add "C:\Documents and Settings\730001108\Desktop\Stapples\Invoice Matching for AP\tiff files\" & Cells(h, 4).Value & ".TIF", olByValue, 1
            Next h
            .To = cell.Value
             .display
            End With
           End If

 

[Comfy]

Give this ago, you may need to fiddle with it to suit your entire code

Dim NotFound() As String, NotFoundstr As String

If cell.Offset(0, 5).Value = "BONUSES" Then
Set OutlookMsg = Outlook.CreateItem(olMailItem)
With OutlookMsg
' set basic params
.Subject = "Zahlung des Bonus" & Format(Date, " dd/mm/yyyy")
.HTMLBody = "Sehr geehrte Damen und Herren, " & ""
For h = cell.Offset(0, 2).Value To cell.Offset(0, 3).Value ' 
If Len(Dir("C:\Documents and Settings\730001108\Desktop\Stapples\Invoice Matching for AP\tiff files\" & Cells(h, 4).Value & ".TIF")) = 0 Then
ReDim Preserve NotFound(h-1)
NotFound(h - 1) = Cells(h, 4).Value & ".TIF"
Else
.Attachments.Add "C:\Documents and Settings\730001108\Desktop\Stapples\Invoice Matching for AP\tiff files\" & Cells(h, 4).Value & ".TIF", olByValue, 1
End if
Next h
For h = LBound(NotFound) To UBound(NotFound)
NotFoundstr = NotFoundstr & " " & NotFound(h)
Next h
.To = cell.Value
If Not NotFoundstr = "" Then
.display
MsgBox "The Following Files do not exist: " & NotFoundstr
Else
'Send without additional attachments
End If
.display
End With
End If

 

[neveu]

Hello Comfy,

thank you very much for the help.

the code works great.
it only has one small issue: in case the attachment is not found, it displays the message that that the file has not been found but it runs the rest of the code ; what i would need it to wait for the user to attach the file and let him send the email manually by pressing the send button in the mail

 

[Comfy]

Possibly something like this.

Dim iReply As Integer


    If Not NotFoundstr = "" Then
    .display
    MsgBox "The Following Files do not exist: " & NotFoundstr & vbNewLine & _
            "Please add them manually and click Send on the email." & vbNewLine & _
            "OK will continue the macro, Cancel should be obvious :)"
    iReply = MsgBox(Prompt:="The Following Files do not exist: " & NotFoundstr & vbNewLine & _
            "Please add them manually and click Send on the email." & vbNewLine & _
            "OK will continue the macro, Cancel should be obvious :)", _
            Buttons:=vbOKCancel)
        If iReply = vbOK Then
        'Continue with the Next For
        Else 'They cancelled (VbCancel)
            Exit Sub
        End If
    Else

This will pause the macro until the user has added the files, sends the email manually and then clicks OK in Excel?

 

[neveu]

the code works ok.

still, something is still not just right.

the email is displayed but the msgbox appears affter the email window is closed. i tried copying the display command after the msgbox but doing this the msgbox message is displayed correctly , but the email window is not being displayed anymore

any ideas how can we fix this?

 

[Comfy]

You could put the message box message in the email body and ask the user to delete it before pressing send?

There may be other better options, but I don't know of them sorry

 

[neveu]

ok. thanks for your help.
i really appreciate it a lot.

all the best,
neveu