ExecuteMSO on copy

[excel-user]

Hi all,
help appreciated:
need to send excel worksheet under new name, as pdf, to lotus notes (mail database on server only, so cannot use solution posted on web.)

My attempt:

Sub sendSheetAsPDF()
Dim filename As String
Dim topic As String
Dim emailAddress As String

filename = Correlation.Range("stockEntry").Value & " " & Correlation.Range("selector") & "-based correlation"

ActiveWorkbook.Worksheets("Correlation").Copy
ActiveWorkbook.SaveAs filename, FileFormat:=52

ActiveWorkbook.Activate

ActiveWorkbook.CommandBars.ExecuteMso ("FileEmailAsPdfEmailAttachment")

ActiveWorkbook.Close False
Kill filename
End Sub



I get Run-time error '91' on the line where I want to execute. Tried with .Activate, same. How can I create the object - I thought is was created during the copy?

Thanks,
Don

 

[excel-user]

Found it myself by chance:

CommandBars.ExecuteMso ("FileEmailAsPdfEmailAttachment")

does the trick. Still would be interested why the run-time error occurs on the active workbook.