Send e-mail when saved 1

[mot98]

Hi All,

I am pretty sure this is doable....I am looking to setup an automatic e-mail that will get sent out everytime an excel document is saved.

Can someone give me some examples on how to set this up?

Thanks,

mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[sdraper]

look for the SendMail Function in Excel VBA help to send it. Place your code in the before save event.
hope this helps,
sdraper

 

[mot98]

Can someone give me some example code? mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[sdraper]

this should get you started...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strRecip As String
Dim strSubject As String
strRecip = "Somebody@email.com"
strSubject = "Hey I just Saved the Workbook!"
ActiveWorkbook.SendMail Recipients:=strRecip, Subject:=strSubject
End Sub

Hope this helps,
sdraper

 

[mot98]

Okay sdraper,

I set this up in Excel as a VB Macro, but it does not work.
Here is the code I am using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strRecip As String
Dim strSubject As String
strRecip = "motoole@ventratech.com"
strSubject = "The Reject Log has been updated. Please Check!"
ActiveWorkbook.SendMail Recipients:=strRecip, Subject:=strSubject
End Sub


Do I have to make any MAPI references?
mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[sdraper]

what error are you getting?
sdraper

 

[mot98]

Getting no errors, just not receiving any e-mails.... mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[sdraper]

interesting. What is your email setup??? MAPI reference not necessary with SendMail.
sdraper

 

[mot98]

Exchange 5.5 server, running Win2K and Outlook 2000 on my workstation.

mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[mot98]

Any new tips on this one..I am still getting no e-mail sent when I save the document.

Thanks,

mot98


"Victory goes to the player who makes the next-to-last mistake."
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[Gavona]

Changed first line to
Sub hello()
and the macro works (if fired manually)
But it doesn't work for me either with the original.

So the problem appears to be in the first line. I have never used events so I won't try to diagnose.

One point, Sendmail sends the workbook as an attachment - did you want that?

 

[MarcLodge]

I have a submit button in a spreadsheet that has the following macro attached to it that works fine.

Sub GITPD01()

ActiveWorkbook.SendMail Recipients:="Marc.Lodge@website.co.uk", Subject:="GITPD01"

ActiveWorkbook.Save

Application.Quit

End Sub

 

[sdraper]

The code I gave was OK. I think the issue is did the before save event fire? You can test this by putting msgbox "Before Save Fired" in the Workbook_BeforeSave Event. Whenever Excel Saves the workbook, you should see a msgbox that says Before Save Fired. I tried this by saving the workbook from the toolbar save button, the file>save and save as menu and from code (ActiveWorkbook.save) and the event fired every time. My guess is you might believe that the workbook is saving but it's actually not. Just one thought. LEt us know how it's working out for you.
hope this helps,
sdraper

 

[mot98]

Hey sdraper,

I actually got it working with the following code:

Sub SendNotif()

Dim bStarted As Boolean
Dim oOutlookApp As Object
Dim oItem As Object

On Error Resume Next

'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application&quot
If Err <> 0 Then
'Outlook wasn't running, start it from code
Set oOutlookApp = CreateObject(&quot;Outlook.Application&quot
bStarted = True
End If

'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(0)

With oItem
'Set the recipient for the new email
.To = &quot;QAAlert@ventratech.com&quot;
'Set the subject
.Subject = &quot;QUALITY ALERT!!!!&quot;
'Set the message body
.Body = &quot;The Reject Log has been updated. Please Check!&quot;
.Send
End With

If bStarted Then
'If we started Outlook from code, then close it
oOutlookApp.Quit
End If

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub


Thanks for all your input and help...


mot98


&quot;Victory goes to the player who makes the next-to-last mistake.&quot;
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)

 

[sdraper]

Ahh yes, The Outlook object model. It's a bit more cumbersome code wise but it's tons more versatile than sendmail. I am glad you found a viable solution.
sdraper

 

[Gavona]

Just the code I needed. How would I attach a file ot two to the email.
More generally how from Excel VBA or whatever can I find out all about the outlook object?

 

[mot98]

Hey Gavona,

Here is the updated code with an example attachment.


Sub SendNotif()

Dim bStarted As Boolean
Dim oOutlookApp As New Outlook.Application
Dim myAttachments As Outlook.Attachments
Dim oItem As Outlook.MailItem

On Error Resume Next

'Get Outlook if it's running
If Err <> 0 Then
'Outlook wasn't running, start it from code
Set oOutlookApp = CreateObject(&quot;Outlook.Application&quot
bStarted = True
End If

'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(0)

With oItem
'Set the recipient for the new email
.To = &quot;motoole@ventratech.com&quot;
'Set the subject
.Subject = &quot;QUALITY ALERT!!!!&quot;
'Set the message body
.Body = &quot;The Reject Log has been updated. Please Check!&quot;
' Attatch Message
Set myAttachments = oItem.Attachments
myAttachments.Add &quot;C:\test.txt&quot;
.Send
End With

If bStarted Then
'If we started Outlook from code, then close it
oOutlookApp.Quit
End If

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Set myAttachments = Nothing

End Sub


Hope it helps...

mot98


&quot;Victory goes to the player who makes the next-to-last mistake.&quot;
- Chessmaster Savielly Grigorievitch Tartakower (1887-1956)