E-mailing workbook in Excel causes error 1

[KeyserSoze]

I have a subroutine that e-mails the active workbook by pulling up the SendMail dialog box. However, after the user hits send, the following error is produced:

Run-time error '91';
Object variable or With variable not set

Here is the routine:

----------------------------------------------

Sub SendToEmployee()

Dim Result As Dialog

'
' First check and see if the user has saved the workbook.
'
If ActiveWorkbook.Saved = False Then
MsgBox "Your workbook must be saved before you can e-mail it to the employee!", vbOKOnly + vbExclamation, "Send Review to Employee"
Exit Sub
End If

Result = Application.Dialogs(xlDialogSendMail).Show
If Result Is Nothing Then
Exit Sub
Else
ThisWorkbook.CustomDocumentProperties("HDFS State").Value = "Employee"
MsgBox "Performance review has been sent!", vbOKOnly, "Send Review to Employee"
ActiveWorkbook.Close
End If

End Sub

----------------------------------------------

If the user clicks the Send button on the mail dialog box, I want to set a custom property. If the user does not send the e-mail, I want to exit the subroutine. I am using Excel 2002 and Outlook 2002.

Thanks!

 

[PHV]

Why mixing ThisWorkbook and Activeworkbook ?
Are you sure that the custom property already exists in the collection ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KeyserSoze]

No particular reason for mixing ThisWorkbook and ActiveWorkbook and the custom property will exist. Neither one of these factors, I believe, has anything to do with the e-mail error.

 

[PHV]

And which line of code is highlighted when in Debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KeyserSoze]

This line:

Result = Application.Dialogs(xlDialogSendMail).Show

 

[rmikesmith]

Dim Result as Boolean, as a True/False value is what the Dialog returns.


Regards,
Mike

 

[KeyserSoze]

Mike:

It worked like a charm. Thanks for your help.

 

[firefytr]

To have more control over your email (if using Outlook), check out ..

http://www.vbaexpress.com/kb/getarticle.php?kb_id=97

-----------
Regards,
Zack Barresse