Sending email(s) from Excel

[KGee]

I want to setup an automated macro and need some assistance or tips. I want the workbook to open at a specific time, say noon on each workday, and send email(s) to a designated list of people.

Some background info:
There a three departments that each create a report on a daily basis, usually by 10am. All three files are saved to the same location. The macro needs to check this location and perform one of two actions:

1) If one or more files are present, it needs to copy and paste data from each file into a new file using a template I created and gets saved to a new "shared" location. Then it needs to send an email to a designated list of recipients with a link to the folder. I want to notify some using the "TO" field and some using the "CC" field.

2) If no files are present, an FYI email needs to be sent, again with recipients in the "TO" and "CC" fields.

My questions center around getting the file to open automatically. Can this be done solely within Excel, or would I have to use a scheduled task in Windows to get the file to open?

After that, I am all set with checking the folder location to see if files exist and creating the summary file if they do. I don't know how to go about the email part. I want to list the names/addresses in the file and have the appropriate action occur based on the whether the files exist or not. I found something on send mail, but not sure how to proceed.

Any ideas would be appreciated.

Kevin

 

[GettingThere]

Hi,

To set a macro to run a certain time I think you can use:

Application.OnTime ("15:16:00"), "Macro2"

- Put the time you want it to run in brackets and the name of the macro you want to run after that
- Probably best to put this macro in the Workbook_Open event so that it will run automatically when you open the spreadsheet.
- N.B. You need to have the spreadsheet open in order for it to run - as you may not have it open at the time maybe it would make more sense to put this in Outlook rather than Excel as most people tend to have that open from 9 - 5....

As far as the Email sending part:

Dim olApp As Outlook.Application
Dim Msg As MailItem
 
Set olApp = New Outlook.Application
Set Msg = olApp.CreateItem(olMailItem)
 
    Msg.To = "Me@Googlemail.com"
    Msg.Subject = "Hello World!"
    Msg.BCC = "asd@msn.com"
    Msg.Attachments.Add "G:\Trader spreadsheet.xls"
    Msg.Attachments.Add "G:\Minutes 24.02.09.doc"
    Msg.Body = "Please find attached the appropriate files.."
    Msg.Display 'Or msg.send if you don't want to check it first
 
Set Msg = Nothing

- N.B. you need to reference the Outlook library for this to work (Tools - References - Microsoft Outlook 10.0 Object Library)
- Also note that without the use of other programs (e.g. Express ClickYes) Outlook won't allow automated emails to be sent out - you'll have to click ok to allow it go out... Not that that's a massive problem =D

Hope this helps - If not let me know =D


Cheers,

A.

 

[KGee]

- N.B. you need to reference the Outlook library for this to work (Tools - References - Microsoft Outlook 10.0 Object Library)
- Also note that without the use of other programs (e.g. Express ClickYes) Outlook won't allow automated emails to be sent out - you'll have to click ok to allow it go out... Not that that's a massive problem =D

Thanks. I was able to get this to work and bypass the Outlook prompt using a CDO example that was posted somewhere else.

BTW - what do you mean by "N.B."