MailEnvelope problem

[liampog]

Hi

I have some code (below) that I use to send a Named Range via the MailEnvelope function of Excel.

It was working for a while but now, when I run the macro to send the e-mail, everything works up to .Send. There are no crashes or error messages but it doesn't send. Before, you would see the e-mail appear in the Outbox and then it would be Sent. Now, it just bypasses that line in the code, continues but doesn't send.

Can anyone figure out what's going wrong?

I'm using Windows 10 and Office 365 (Outlook and Excel).

I can't for the life of me understand why it's worked previously and isn't anymore.

Sub InterimReport_Send()
    If ActiveSheet.Name = "Report" Then        
        ThisWorkbook.Save        
        Dim PreviousSelection As Range    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .DisplayAlerts = False
        End With        
        Set PreviousSelection = ActiveCell        
        ActiveSheet.Unprotect Password:="Report"        
        Range("Report_InsideBorders").Borders(xlInsideHorizontal).Weight = xlMedium        
        ActiveSheet.Protect Password:="Report"
        On Error GoTo StopSend        
        Range("Report_MailRange").Select        
        ActiveWorkbook.EnvelopeVisible = True
        With ActiveSheet.MailEnvelope.Item                                
            .To = "email@address.com"            
            .Subject = "Subject"            
            .Send        
        End With        
        ActiveWorkbook.EnvelopeVisible = False    
StopSend:        
        ActiveSheet.Unprotect Password:="Report"
        Range("Report_InsideBorders").Borders(xlInsideHorizontal).Weight = xlThin
        ActiveSheet.Protect Password:="Report"    
        PreviousSelection.Select    
        With Application            
            .DisplayAlerts = True        
            .ScreenUpdating = True            
            .EnableEvents = True            
        End With    
    End If
End Sub

 

[Domenic]

First, comment out or delete this line...

On Error GoTo StopSend

Then try running your macro again. Do you get an error? If so, which one and on which line?

 

[liampog]

Hi

Sorry I should have mentioned I have tried this

It doesn’t stop on any line with any error messages. I’ve even done it step by step and it gets to that line .Send and just moves on to the next line.

 

[Domenic]

When .Send is executed, the email should only appear in Outbox for a few seconds, assuming all goes well, until it's actually sent. So the email should appear in your Sent folder. Is it not there in your Sent folder?

 

[liampog]

When .Send is executed, the email should only appear in Outbox for a few seconds, assuming all goes well, until it's actually sent. So the email should appear in your Sent folder. Is it not there in your Sent folder?

Hi Domenic

Yes I understand that and I've seen it happen where it's there momentarily, however, it's not even going to the Outbox for that split second and is definitely not being Sent.



It's frustrating because it was working but I don't believe there has been any significant change that has caused it to stop.

Thanks
Liam

 

[Domenic]

You've unprotected your sheet to apply your borders, but then you protect it again before sending your email. Try protecting it again only after you've sent your email. Does this help?

 

[liampog]

Haha

So interestingly, I had a different problem before this one whereby the line protecting the sheet BEFORE attempting to send was causing Outlook and Excel to both crash to the point where you can only force close them via Task Manager.

Adding the FIRST line of reprotection SOLVED the crashing issue but then seemed to stop .Send from working.

Before ALL of this however, the whole sheet was working without any issues at all.

Completely at a loss....

 

[Domenic]

Sorry, it's not clear to me whether you tried it. Did you try it? And, if so, was it successful?

 

[liampog]

Sorry I think my point was missed, being that prior to THIS specific problem, the line of protecting the sheet after setting the borders wasn’t present. The sheet was unprotected before applying the borders and reprotected after the borders are reset later on.

This would crash Excel and Outlook.