Email Excel files automatically

[pcb105]

Hi All,

I have a report that I want created each night and emailed to a list of people. Is there a way to do this within Excel using VBScript. I already have an Excel VBScript to create the files.

Thanks in advnace.

 

[SkipVought]

Hi,

Turn on your macro recorder and record File > Send to...

Post back with your recorded code if you need help customizing.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[pcb105]

After searching for a while on the internet I found the following code that meets my needs. It emails via Outlook (so you have to have outlook installed on your machine; but you then do have a history of what was sent which for me is a good thing).

It works pretty well.


Sub SendMailOutlook(aTo, Subject, TextBody, aFrom, aFileName)

'Create an Outlook object
Dim Outlook 'As New Outlook.Application
Set Outlook = CreateObject("Outlook.Application")

'Create e new message
Dim Message 'As Outlook.MailItem
Set Message = Outlook.CreateItem(olMailItem)
With Message
'You can display the message To debug And see state
'.Display

.Subject = Subject
.Body = TextBody

'Set destination email address
.Recipients.Add (aTo)

'Set sender address If specified.
Const olOriginator = 0
If Len(aFrom) > 0 Then .Recipients.Add(aFrom).Type = olOriginator

.Attachments.Add (aFileName)


'Send the message
.Send
End With
End Sub