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

AlliCarr

Member
Joined
Feb 19, 2024
Messages
39
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.

Code:
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!
 
If you're looking for a file, you can use the Dir() function.
Code:
If Dir(strFile) = "" Then
    'File does not exist 
Else
    'File exists 
End If
Hope that helps...
 
If you're looking for a file, you can use the Dir() function.
Code:
If Dir(strFile) = "" Then
    'File does not exist
Else
    'File exists
End If
Hope that helps...
Yep, that helps!
Many thanks :)
 

Users who are viewing this thread

Top Bottom