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

Anthos

Board Regular
Joined
Jun 10, 2002
Messages
64
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. :rolleyes:

Any help appreciated guys! :biggrin:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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....

Code:
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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.

Code:
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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Re: Save workbook, copy it to a folder and email a notificat

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

Anthos

Board Regular
Joined
Jun 10, 2002
Messages
64

ADVERTISEMENT

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

Thanks mate. I'll give it a try!
 

Anthos

Board Regular
Joined
Jun 10, 2002
Messages
64
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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314

ADVERTISEMENT

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

Anthos said:
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

Board Regular
Joined
Jun 10, 2002
Messages
64
Re: Save workbook, copy it to a folder and email a notificat

Duuhhhh :oops:

Thanks mate and sorry for being careless!
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Re: Save workbook, copy it to a folder and email a notificat

Simple way to avoid this problem?

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top