Save workbook, copy it to a folder and email a notification!

[Anthos]

I have to do this 10 times a day and it's getting anooying. I want to save the active workbook (not the same everytime), move a copy into a network folder and then send an email notification (MO) to a collegue that a new file has been added to the common folder.

Any help appreciated guys!

 

[jimboy]

Re: Save workbook, copy it to a folder and email a notificat

Hi, try this, you will need to change the email address and drive and folders.

You will also need to reference Outlook in VB, to do this goto Tool>References and select MS Outlook....

Sub Save_Send_Mail()
    'You need to Reference Outlook
    'Goto Tools>References and select Microsoft Outlook Libary 9.0 (or your version)
        Dim olApp As Outlook.Application
    Dim olMail As MailItem

    ActiveWorkbook.Save
    
    'ChDir "C:\temp"
    'Change drive & folder here
    ActiveWorkbook.SaveAs Filename:="C:\temp\" & ActiveWorkbook.Name
    Application.ScreenUpdating = False
    Set olApp = New Outlook.Application
    
                Set olMail = olApp.CreateItem(olMailItem)
                With olMail
'Change email address here
                    .To = "jim.boy@emailhere.com"
                    .Subject = "Reminder"
    'Change name ****************
                    .Body = "Dear Fred" & vbNewLine & vbNewLine & _
                            "This file has been added to C:\Temp drive:  " & ActiveWorkbook.Name
                    .Send  'Or use Display
                Set olMail = Nothing
       End With
    Set olApp = Nothing
    Application.ScreenUpdating = True

End Sub

 

[DonkeyOte]

Re: Save workbook, copy it to a folder and email a notificat

Jimboy - here's some stuff I picked up from other posts...

You can use late binding to avoid using the library altogether...see my question and the answers I got on this here:

http://www.mrexcel.com/board2/viewtopic.php?t=73384&highlight=late+binding

if you do want to use the outlook object model then use this code to auto reference the relevant Library (works for all versions) by using the GUID reference.

On Error Resume Next
Set ID = ThisWorkbook.VBProject.References
ID.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 7, 0
If Err.Number <> 32813 And Err.Number <> 0 Then
    MsgBox Err.Description
End If

Here's a thread going through all this and also some code from Nate to extract the GUID info for any library you want to add in this fashion.. the fact that's it works for different versions makes life a lot easier!

http://www.mrexcel.com/board2/viewtopic.php?t=8251

 

[jimboy]

Re: Save workbook, copy it to a folder and email a notificat

Great thanks, it will take a will for me to understand this...

 

[Anthos]

Re: Save workbook, copy it to a folder and email a notificat

Thanks mate. I'll give it a try!

 

[Anthos]

Re: Save workbook, copy it to a folder and email a notificat

jimboy,

I've tried using the code but I get this error

"User-defined type not defined"

on this line...

Dim olApp As Outlook.Application

I've declared the Microsoft Office 9.0 Object Library...

Any hints?

Thanks

 

[jimboy]

Re: Save workbook, copy it to a folder and email a notificat

jimboy,

I've tried using the code but I get this error

"User-defined type not defined"

on this line...

Dim olApp As Outlook.Application

I've declared the Microsoft Office 9.0 Object Library...

Any hints?

Thanks

Declare Microsoft Outlook 9.0

 

[Anthos]

Re: Save workbook, copy it to a folder and email a notificat

Duuhhhh

Thanks mate and sorry for being careless!

 

[DonkeyOte]

Re: Save workbook, copy it to a folder and email a notificat

Simple way to avoid this problem?

INSERT THE CODE I GAVE YOU into your mail macro at the beginning

On Error Resume Next 
Set ID = ThisWorkbook.VBProject.References 
ID.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 7, 0 
If Err.Number <> 32813 And Err.Number <> 0 Then 
    MsgBox Err.Description 
End If

 

[NateO]

Re: Save workbook, copy it to a folder and email a notificat

Another thing you can do is to test your references to see if one is established, if so, go about your business, if not, add from the GUID. E.g.,

http://www.mrexcel.com/board2/viewtopic.php?t=50211&start=1

I'd recommend the use of InstrB() over Instr() here...