Solved Auto email error needed if attachment file doesn't exist or is named incorrectly

[AlliCarr]

Hi,

Hoping someone can help.

I have the following code in my database which, when a payment is authorised, will send an automatic email to our finance team to say the payment needs to be made and will also attach the bank statement from a file directory. However, if the file doesn't exist/is named incorrectly, I need an error message which alerts the authoriser. I can't seem to find any guidance online which helps.

Private Sub SaveFinalAuth_Click()
Dim ctl As Control
Dim OrgURN As String
Dim GrantURN As String
Dim strFolder As String
Dim strGrantFolder As String
Dim strFile As String
Const strParent = "S:\IT\1 - Testing\Documents\"
OrgURN = "Org URN " & Me.OrganisationURN
GrantURN = "Grant URN " & Me.GrantURN
strFolder = strParent & OrgURN
strGrantFolder = strFolder & "\" & GrantURN
strFile = strGrantFolder & "\" & "Bank Statement.pdf"

If IsNull(Me.FinalAuthorisationDate) Then GoTo Error

'Email message text
Dim msg As String
msg = "Organisation Name: " & OrganisationName & "<p>" _
& GrantURN & "<p>" _
& "Payment of " & Format(CCur(Me.PaymentAmount), "Currency") & " was authorised on " _
& FinalAuthorisationDate & " by " & FinalAuthorisation & " and is ready to pay." & "<p>" _
& "Sort Code: " & SortCode & "<p>" _
& "Account No: " & AccountNumber
    
'Declare variables
Dim O As Outlook.Application
Dim M As Outlook.MailItem

'Set variables
Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

'Set sender address, text format, recipient, subject, add attachment and send
With M
.SentOnBehalfOfName = "crtsupport@coalfields-regen.org.uk"
.BodyFormat = olFormatHTML
.HTMLBody = msg
.To = "allison.carr@coalfields-regen.org.uk" ' "accounts@coalfields-regen.org.uk"
.Subject = "Payment authorised and ready to pay"
.Attachments.Add strFile
.Send
End With
End If

Set M = Nothing
Set O = Nothing
    
'Disable final authorisation date and set payment status to awaiting payment
FinalAuthorisationDate.Enabled = False
Me.PaymentStatus = "Awaiting payment"
Me.SaveFinalAuth.Enabled = False
MsgBox "Payment authorised"
Me.Dirty = False
DoCmd.Close

Exit Sub

Error:
MsgBox "Please add the date"

End Sub

Thanks in advance!

 

[theDBguy]

If you're looking for a file, you can use the Dir() function.

If Dir(strFile) = "" Then
    'File does not exist 
Else
    'File exists 
End If

Hope that helps...

 

[AlliCarr]

If you're looking for a file, you can use the Dir() function.

If Dir(strFile) = "" Then
    'File does not exist
Else
    'File exists
End If

Hope that helps...

Yep, that helps!
Many thanks

 

[theDBguy]

Yep, that helps!
Many thanks

You're welcome. Good luck with your project.