Email notification on file save

[bradpallister]

Hi, this is a cross post http://www.ozgrid.com/forum/showthread.php?t=150920 as I am getting desparate for some help...


I have a requisition template which currently has a button which saves a copy of the workbook to a specific location with the requisition number as the file name.

On file save, I would like to automatically send an notification email (using Outlook 2003 and up) to our buyer. Note I don't want to send the whole file, just to fill out the subject line with the requisition number, and the body to have a few more details such as delivery instructions etc.

Any idea how I should do this? I have tried searching through but have so far come up fruitless..

Many thanks................


Appreciate any help..


Many thanks

 

[Hui]

Have a read of: http://msdn.microsoft.com/en-us/library/aa203718%28v=office.11%29.aspx


Specifically for your example i'd look at setting up a separate sheet which will be emailed

The sheet will contain all the data you want to send

[pre]

Sub Mail_ActiveSheet()
Dim strDate As String
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "someone@microsoft.com", _
"Subject_line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

[/pre]

 

[bradpallister]

Thanks Hui

The only snag is that there would be in the region of 50-60 requisitions being raised every day, thus I only want a notification or alert sent, rather than the worksheet attached if that makes sense.

The workbook is saved to a central location..

 

[vijaySharma]

Brad,


On your master sheet define the named ranges for the items that you want to include in the body of the mail.


Once the Save button is hit and the workbook has been saved you can extract the file name to be put in the subject or make the subject dynamically using the Requisition number that you can reference using the Named Range


and then send the email.


Below is the code to send mail using VBA.

You will need to add a reference to Microsoft Outlook Object library

[pre]

Set olApp = New Outlook.Application
Dim olNs As Outlook.Namespace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = [sendMailTo] 'named range referring to the list of people to whom you want to send the mail

strOtherBodyText = strOtherBodyText & vbTab & [YourNamedRange]

olMail.Subject = "Report Form " & Format(Date, "dddd dd mmmm yyyy")
olMail.Body = "Dear All," _
& vbCrLf & vbCrLf & "Please find attached the Report for " & Format(xDate, "dddd dd mmmm yyyy") & "." _
& strOtherBodyText & vbCrLf & vbCrLf & "Regards," _
& vbCrLf & "ACME Ltd" _
& vbCrLf & "India" _
& vbCrLf & vbCrLf

'olMail.Attachments.Add xPath 'if you decide to attach the file as well
olMail.Display

[/pre]

HTH

~Vijay

sharma.vijay1-at-gmail.com

 

[Luke M]

Check on Ron's suggestion for small message:

http://www.rondebruin.nl/mail/folder3/smallmessage.htm


or sending to people in a range:

http://www.rondebruin.nl/mail/folder3/message.htm


For his comprehensive list of emailing w/ Excel, see here:

http://www.rondebruin.nl/sendmail.htm