Outlook attachment code crashes Excel

[dmkennard2]

Hi,
I have the following code that works and sends the required E-mail with attachment, but when i try and close Excel, Excel crashes everytime.

We are using Excel 2003 SP1. If this version is causing the problem, is there anther way of doing the same thing as we cannot upgrade to SP2 at the moment.

Private Sub cmdSendMail_Click()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
Dim address, datefrom, dateto As String

address = Right$(Range("A1"), 8)
header = Range("A1").Text
datefrom = Range("B4").Text
dateto = Range("B5").Text

ActiveWorkbook.Save

If address = "1001" Then
With OutMail
.To = "Test Email 1"
.CC = ""
.BCC = ""
.Subject = header
.Body = "Hier ist die " & header & " für die Periode vom " & datefrom & " bis " & dateto & Chr$(13) & Chr$(13) & " Viele Grüsse"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

Else

If address = "1002" Then
With OutMail
.To = "Test Email 2"
.CC = ""
.BCC = ""
.Subject = header
.Body = "Hier ist die " & header & " für die Periode vom " & datefrom & " bis " & dateto & Chr$(13) & Chr$(13) & " Viele Grüsse"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

Else

MsgBox "No such address", vbCritical, "Error"

End If
End If

Set OutMail = Nothing
Set OutApp = Nothing

frmControl.Hide

End Sub

Thanks in advance

Dazz

 

[PHV]

Have you tried the ActiveWorkbook.SendMail method ?

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

 

[dmkennard2]

Thanks for this.
I did not want to use this as its very limited.
You cant put a message in the body of the E-mail which is a shame, but i have had to use it!!

Thanks

Dazz

 

[djj55]

Have you tried OutMial.Close before the Nothing? MS does not always clean-up when you think it does.
djj

 

[dmkennard2]

Yep, tried this and it still crashes.
Dazz

 

[dmkennard2]

Got it.
It was the little bit of code at the bottom "frmControl.Hide".
Took this out and it works fine.

Thanks
Dazz