Automatically Email Daily Reports Using Outlook.

[chris.levin]

Our trading system automatically generates 4 reports everyday. I want
to create a macro that will automatically send those reports to the
appropriate people. Here is what I have so far:

Sub Test()
Set objOL = CreateObject("Outlook.Application")
Set objMail = objOL.CreateItem(0)
With objMail
.Subject = "Destination Details Report"
.To = "John Q. Public"
.Attachments.Add "c:\temp\TEST.txt"
.Send
End With
End Sub

I am encountering 2 problems. First, I would like the current date to
automatically fill in somewhere in Subject line. For example,

..Subject = "Destination Details Report" $mm/dd/yy$

Anyone know the correct syntax for this or if it is even possible?
Secondly, our trading system generates the filename of the report in
this format:

DestExecDetailsReport20060117.csv

As you can see, the date is automatically added to the end of the
report name. Is it possible for me to do this macro if the name of the
file I want to attach changes names everyday?

Thank you in advance for your help!

 

[Michael Bauer]

Am 18 Jan 2006 10:47:59 -0800 schrieb (e-mail address removed):

1. The Date function returns the date. Additionally you could use the Format
function, e.g.: str=Format(Date, "mm/dd/yy")

2. The second question I don´t understand. Do you want to add the date to
the file name?

 

[chimp]

Thank you for your response, Michael. I re-read my initial post and
realized I failed to make myself clear concerning the second issue.

When our trading system creates the report on a daily basis, it
automatically adds the date to the end of the filename. For example,
yesterday's reports was named:

DestExecDetailsReport20060118.csv

Today's reports will be:

DestExecDetailsReport20060119.csv

My question is how do I reference a report that changes names everyday,
and attach it to an email, in my macro? In my test macro, I simply
used the static filename of TEST.txt to add that document as an
attachment to the generated email:

..Attachments.Add "c:\temp\TEST.txt"

However, the report I want to automatically attach using a macro
changes names everyday. Is there a way to attach that report that
changes every day to an email? And if so, I would I reference it in my
macro?

Thanks again for your help.

 

[chimp]

Additionally, what is the proper syntax to use to have the subject line
of the email read:

"1/19/06 Destination Details Report"

where 1/19/06 is derived from the current date. I tried using the date
function in several different ways:

.Subject = "Destination Details Report" str=Format(Date,
"mm/dd/yy")

Is it possible to add the date in the .Subject line?

 

[Michael Bauer]

Am 19 Jan 2006 06:37:03 -0800 schrieb chimp:

Additionally, what is the proper syntax to use to have the subject line
of the email read:

"1/19/06 Destination Details Report"

where 1/19/06 is derived from the current date. I tried using the date
function in several different ways:

.Subject = "Destination Details Report" str=Format(Date,
"mm/dd/yy")

Is it possible to add the date in the .Subject line?

Strings are being joined by the ampersand:

Subject = Format(..) & "more text"

 

[Michael Bauer]

Am 19 Jan 2006 06:15:06 -0800 schrieb chimp:

Thanks, now I understand you

You do know the fixed file name, you need to know the date, and now you also
do know how to join strings. That´s all.

FileName = "fixed part" & Format(TheDateInQuestion, "yyyymmdd") & ".csv"

 

[chimp]

Awesome. Thanks for helping me out. I rectified the .Subject line
problem. However, I must have the syntax wrong for calling the report
to be attached. Here is what I have for the attachment line:

..Attachments.Add FileName = "c:\temp\DestExecDetailsReport" &
Format(Date, "yyyymmdd") & ".csv"

I receive a "Run-time error '5': Invalid procedure call or argument"
error message when I run it. I also tried:

..Attachments.Add FileName = "c:\temp\DestExecDetailsReport" &
Format(TheDateInQuestion, "yyyymmdd") & ".csv"

I received the same error. I wasn't sure if I should actually use
"TheDateInQuestion" in the line. You can fit all I know about Visual
Basic into a thimble, but that is fairly evident by now.

 

[chimp]

I finally figured it out. Here is what I needed to do:

..Attachments.Add "c:\temp\DestExecDetailsReport" & Format(Date,
"yyyymmdd") & ".csv"

Now it works. Thanks so much Michael. I would like to ask you one
more question however. How do I disable the warning box that pops up
when I send the email via the macro.

"A program is trying to automatically send e-mail on your behalf. Do
you want to allow this? If this is unexpected, it may be a virus and
you should choose "No"." I browsed the newsgroup and followed the
links, but I don't completely understand the process.

 

[Michael Bauer]

Am 20 Jan 2006 07:07:19 -0800 schrieb chimp:

Please read this: http://www.outlookcode.com/d/sec.htm