Excel VBA code to email a hidden worksheet

[Tony_Y]

Hi,

I have the following code which works great when my worksheet called PURGE is unhidden. However I have now hidden PURGE and the code is showing Run-time error: 5 Invalid procedure call or argument (I have highlighted in red the debug).

Any help with this issue will be greatly appreciated

TIA

Tony

VBA Code

Sub Send_Email()

Dim wFile As String, dam As Object

wFile = ThisWorkbook.Path & "\Tightness - " & Worksheets("INPUT GUIDE").Range("B26").Value & ".pdf"
Worksheets("PURGE").UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Set dam = CreateObject("Outlook.Application").CreateItem(0)
dam.to = Worksheets("INPUT GUIDE").Range("K56").Value
dam.Subject = "GAS PURGE CERTIFICATE - " & Worksheets("INPUT GUIDE").Range("B26").Value
dam.body = "PLEASE SEE ATTACHED"
dam.Attachments.Add wFile
dam.Send
MsgBox "Email sent"

End Sub

 

[Logit]

Try implementing Application.ScreenUpdating = False prior to making the hidden sheet visible.

Perform the email process.
Hide the sheet.

Then implement Application.ScreenUpdating = True

 

[Tony_Y]

Hi Logit,

That works a treat. Thank you very much for your help. Have a good evening.

Kind regards

Tony

 

[Logit]

.
You are welcome.