Solved Problems with code execution following email

[AlliCarr]

Hi

I'm having problems with a section of code which previously worked but I can't see why it isn't fully executing now. Here's the code:

Private Sub SaveFirstAuth_Click()
Dim OrgURN As String
Dim GrantURN As String
On Error GoTo ErrorHandler

OrgURN = "Org URN " & Me.OrganisationURN
GrantURN = "Grant URN " & Me.GrantURN
        
If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
    GoTo Error

'Email message text
    Dim msg As String
    msg = "Organisation Name: " & OrganisationName & ",<p>" _
    & GrantURN & ",<p>" & "Payment ready for final authorisation."
    
'Define 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 and send
    With M
        .SentOnBehalfOfName = "crtsupport@coalfields-regen.org.uk"
        .BodyFormat = olFormatHTML
        .HTMLBody = msg
        .To = "allison.carr@coalfields-regen.org.uk" 'Me.FinalAuthorisation.Column(1)
        .Subject = "Payment for Authorisation"
        .Display
        .Send
        .SaveAs "S:\IT\1 - Testing\Documents\" & OrgURN & "\" & GrantURN & "\" & "Payment Authorised.msg"
    End With

    Set M = Nothing
    Set O = Nothing

'Disable authorisation fields and update payment status
    FirstAuthorisation.Enabled = False
    FirstAuthorisationDate.Enabled = False
    FinalAuthorisation.Enabled = False
    
    Me.PaymentStatus = "Awaiting final authorisation"
    Me.SaveFirstAuth.Enabled = False

    MsgBox "Payment authorised"
    Me.Dirty = False
    DoCmd.Close

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg

Error:
MsgBox "Please add first authorisation, date and final authorisation"
End Sub

With the error handling disabled, the code works perfectly until the end of the With block and the email pops up and sends straight away. But, the rest of the code doesn't execute so I can just keep pressing the save button and it keeps sending the same email.

With the error handling it doesn't work at all!

Any help would be greatly appreciated.

Kind regards
A very confused Allison

 

[MajP]

With the error handling it doesn't work at all!

Are you saying there is not an error message? If there is what is it?
I would not do this

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
    GoTo Error

simply

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
          MsgBox "Please add first authorisation, date and final authorisation"
          exit sub

 

[AlliCarr]

Are you saying there is not an error message? If there is what is it?
I would not do this

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
    GoTo Error

simply

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
          MsgBox "Please add first authorisation, date and final authorisation"
          exit sub

Yep, there's no error message at all with the error handling. I have been trying to make an error message pop up so it would help me debug but there's nothing!

Thanks for the feedback too, that's so much cleaner.

Interestingly, I have some similar code for the next part of the process which was working perfectly and as it should until I just added the .send after .display and then that wouldn't work either but no error message popped up. I'm inclined to think that I shouldn't be using .display and .send together regardless of the fact that it works without error handling??

 

[AlliCarr]

Interestingly, I have some similar code for the next part of the process which was working perfectly and as it should until I just added the .send after .display and then that wouldn't work either but no error message popped up. I'm inclined to think that I shouldn't be using .display and .send together regardless of the fact that it works without error handling??

I have removed the .send from this code but it still doesn't work and still no error message

 

[Gasman]

Walk your code with F8 and breakpoints and inspect your variables and flow.

 

[MajP]

Is it possible that the code is executing properly, just hanging?

If I understand correctly the code executes except for the following. Putting breakpoints on each line would help show you where it is getting to.

Disable authorisation fields and update payment status
    FirstAuthorisation.Enabled = False
    FirstAuthorisationDate.Enabled = False
    FinalAuthorisation.Enabled = False
    
    Me.PaymentStatus = "Awaiting final authorisation"
    Me.SaveFirstAuth.Enabled = False

    MsgBox "Payment authorised"
    Me.Dirty = False
    DoCmd.Close

Move this to the end just to be safe (should not make a difference)

Set M = Nothing
Set O = Nothing

 

[Minty]

I don't think you can do

.Display
.Send

It's one or the other.

 

[adhoustonj]

Agreeing with Minty.. all my db's have

oMail.send
    '.Send or .display

 

[Pat Hartman]

Thanks for the feedback too, that's so much cleaner.

Why would you leave If's dangling without end if's? Does the code even compile this way?

Have you stepped through the code to see where it is running off the rails?

 

[AlliCarr]

Thanks all for your help and suggestions. So I have the SaveFirstAuth_Click() sub working now. It turns out that I used 'msg' twice for different variables. This was included in the error handling which is maybe why I think the error handling didn't trigger.

I also updated this:

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
          MsgBox "Please add first authorisation, date and final authorisation"
          exit sub

to this:

If IsNull(Me.FirstAuthorisation) Then
    If IsNull(Me.FirstAuthorisationDate) Then
        If IsNull(Me.FinalAuthorisation) Then
    MsgBox "Please add first authorisation, date and final authorisation"
        End If
    End If
End If

And all is fine.

However, I am now having a problem with the next part of the process which is the SaveFinalAuth_Click() sub. This works so far as it generates the email, adds the attachment and displays it but doesn't save it in the file that's available and gives me the following error:

I did experience this error previously with the SaveFirstAuth_Click() sub and this was because the directory where Access was trying to save the email copy didn't exist. However, it does exist in this instance so not sure why it's happening. Here is the code:

Private Sub SaveFinalAuth_Click()
On Error GoTo ErrorHandler
Dim OrgURN As String
Dim GrtURN 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
GrtURN = "Grant URN " & Me.GrantURN
strFolder = strParent & OrgURN
strGrantFolder = strFolder & "\" & GrtURN
strFile = strGrantFolder & "\" & "Bank Statement.pdf"

    If IsNull(Me.FinalAuthorisationDate) Then
        MsgBox "Please add the date", vbOKOnly
    End If

'Email message text
    Dim msg As String

        msg = "Organisation Name: " & OrganisationName & "<p>" _
        & "Grant URN " & 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 and send
    If Dir(strFile) = "" Then
        GoTo MailError
    End If

    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
        .Display
        .SaveAs strParent & OrgURN & "\" & GrantURN & "\" & "Payment ready to pay.msg"
    End With
  
'Disable final authorisation date and set payment status
    FinalAuthorisationDate.Enabled = False
    Me.PaymentStatus = "Awaiting payment"
  
'disable final authorisation save
    Me.SaveFinalAuth.Enabled = False
    MsgBox "Payment authorised"
    Me.Dirty = False
    DoCmd.Close

    Set M = Nothing
    Set O = Nothing
  
Exit Sub

MailError:
MsgBox "Bank statement does not exist, please check the file", vbOKOnly + vbCritical
Me.Undo
DoCmd.Close

ErrorHandler:
    Dim ErrMsg As String
    ErrMsg = Err.Number & ":" & Err.Description
    MsgBox ErrMsg
  
End Sub

I have tried adding break points and running the code but all I get is the Macros box asking me to add in the Macro name. It compiles fine too.

If anyone can shed any light I'd be really grateful!

 

[Gasman]

Put path and filename into a string variable and debug.print that.
Then use that in the SaveAs.

I would still be walking my code if that was me.

 

[MajP]

    If IsNull(Me.FinalAuthorisationDate) Then
        MsgBox "Please add the date", vbOKOnly
    End If

I think you need an exit sub. You will get a message but the code will continue.

 

[AlliCarr]

Put path and filename into a string variable and debug.print that.
Then use that in the SaveAs.

I would still be walking my code if that was me.

Thanks for the suggestion, I have managed to get it working after adding the variable. There was an error in there too so updating it has helped.

I just wanted to ask about debugging too as I've never managed to get it to work. Does this only work with modules or should it work with the code behind a form too? I have tried stepping into the code using F8 and nothing happens. Using debug.print only gives me this:

 

[AlliCarr]

    If IsNull(Me.FinalAuthorisationDate) Then
        MsgBox "Please add the date", vbOKOnly
    End If

I think you need an exit sub. You will get a message but the code will continue.

I can see why it needs an exit sub as it still sends the authorisation email if the date is left blank. However, when I leave in the exit sub in both sections of code, it stops the rest of the code running even if the date is completed.

Everything works perfectly without the Exit Sub except when the error is triggered so it does need to be there. Would it be better adding the if statement and exit sub elsewhere in the code or referring it back to an error handler at the end?

 

[MajP]

back to an error handler at the end?

Never do that. You never want to use error handler to manage code execution. Avoid gotos all the time. That mail error should just be part of the code an not where you have it IMO.

The best construct IMO

if Isdate(me.finalAuthorisationDate) then
  msgbox here
else
   run code here
end if

However the exit sub construct is nice to avoid lots of nested code blocks.

the isdate is a better check for an unbound control is that is what it is. It might not be null but it could be a letter, number, etc. If that is abound field you should be fine.

 

[Gasman]

Thanks for the suggestion, I have managed to get it working after adding the variable. There was an error in there too so updating it has helped.

I just wanted to ask about debugging too as I've never managed to get it to work. Does this only work with modules or should it work with the code behind a form too? I have tried stepping into the code using F8 and nothing happens. Using debug.print only gives me this:

View attachment 114169

Put a breakpoint where you need it in your form, then go to Form view and use the form so that code where the breakpoint is will be activated.. The window will change to the VBA window and stop at the first breakpoint. Then alk your code from there or to the next breakpoint.

Do not forget to remove the breakpoints when you get it working as you need it to.

 

[adhoustonj]

Once you put your breakpoint by clicking to the left on your code, go back to your SaveFinalAuth_Click on your form, and your code will stop at this line. Then F8 to walk through. You can put another breakpoint further down in your code and instead of having to F8 all the way through it, press F5 to run through everything until the next breakpoint, or if no more breakpoints, F5 will run the rest of your code.

 

[AlliCarr]

Never do that. You never want to use error handler to manage code execution. Avoid gotos all the time. That mail error should just be part of the code an not where you have it IMO.

The best construct IMO

if Isdate(me.finalAuthorisationDate) then
  msgbox here
else
   run code here
end if

However the exit sub construct is nice to avoid lots of nested code blocks.

the isdate is a better check for an unbound control is that is what it is. It might not be null but it could be a letter, number, etc. If that is abound field you should be fine.

It works!!!! Thanks so much!

 

[AlliCarr]

Thanks @Gasman and @adhoustonj that's much clearer and will help loads whilst I'm still testing after my code review.

 

[Pat Hartman]

It turns out that I used 'msg' twice for different variables.

Do you have Option Explicit at the top of every code module? Do you have the property set for the entire database so that it is automatically added to new modules?