MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fire a mail message


Posted by Mark Templeton on January 03, 2002 7:45 AM

Is there any way of getting excel to fire off a mail message to a specific user once a workbook has been updated.

All I want to do is have a subject of "Maintenance Log Added" and the message "Log Number XXX Has Been Created"

Cheers

Also anyone had any thoughts on the one I posted earlier about date format in userform textboxes. I'm still baffled


Posted by marc on January 04, 2002 11:18 AM

Hey Mark, I'm a newbie, but I credit this site with providing some great tips and answers. I believe I pulled this code off of the site somewhere. Should work. Regards, Marc

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim OLook As Object 'Outlook.Application
Dim Mitem As Object 'Outlook.Mailitem
Dim SendAnEmail As Boolean
Dim fname As String
Dim otlAttach As Object
Dim sbody As String 'The actual string to send

Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)

'fname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Mitem.to = "frank.booth@velvet.com"
Mitem.Subject = "Maintenance Log Added"
Mitem.body = "Log Number XXX Has Been Created"
'Mitem.Attachments.Add fname
Mitem.send
Set otlAttach = Nothing


End Sub


Posted by Mark Templeton on January 07, 2002 1:11 AM

TOP OF THE POPS! CHEERS MATE IT WORKS A TREAT : Is there any way of getting excel to fire off a mail message to a specific user once a workbook has been updated. : All I want to do is have a subject of "Maintenance Log Added" and the message "Log Number XXX Has Been Created" : Cheers : Also anyone had any thoughts on the one I posted earlier about date format in userform textboxes. I'm still baffled