Automated mail creation & sending

  • Thread starter Thread starter RichardSchollar
  • Start date Start date
R

RichardSchollar

Hello everyone

I create a report from a legacy system in csv format every day, and
once it has been produced I need to attach it to an email and send
this email out to one recipient (the same one every day). The subject
line is also the same every day, and the report is saved down to my C
drive.

How can I automate the process? Not running the report, but picking it
up from its specified location - preferably confirming first that the
file date is today's date (the date is NOT incorporated within the
file name) - and attaching it to an email to the recipient and with
the standard header?

I am conversant with Excel VBA but have never used Outlook VBA before.

Any help appreciated.

Best regards

Richard Schollar
 
I'm sure the following will make more competent coders cringe, but it
does what I need:

Sub CreateAndSend()
Dim fso, oFDT, myOlApp,myitem,myAttachments
Set fso = CreateObject("Scripting.FileSystemObject")
'Set fso = New FileSystemObject
Set oFDT = fso.GetFile("C:\MyFile.csv")
If Format(oFDT.DateLastModified, "ddmmyyyy") <> Format(Date,
"ddmmyyyy") Then
MsgBox "MyFile.csv does not carry today's Date Stamp": Exit Sub
End If

Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myitem.Attachments
myAttachments.Add "C:\AXAFIL.csv", _
olByValue, 1
myitem.To = "(e-mail address removed)"
myitem.Subject = "My Subject Line"
myitem.Display
myitem.Send
End Sub
 
Back
Top