Automatically send a email to a distribution list at a set time

[David]

I've been programming VBA in Excel for years, but now I'm stepping outside of
my comfort zone and trying to learn how to create VBA applications for other
MS tools such as Outlook in order to work more efficiently. One thing I find
myself doing lately is sending out a weekly reminder email to a distribution
list at a set time. It's a mundane task, but must be done. Every time I do
this, I have to go into Outlook, create a new message, select the
distribution list, copy the generic message to the mail userform and click
"Send". I'd like to think that there's a macro I can write for Outlook that
will allow me simply push a button and presto! It's done! Or better yet,
have this macro running in the background such that every week at 9:00 am on
Tuesday, this email automatically gets sent to the distribution list. Can
this be done?

Thanks!

 

[Guest]

Hi David. There are two ways to do this:

- Write your e-mail as you usually do, but instead of sending it, save it as
an .oft template file and run that file and send it when you want
- Automate the construction of the e-mail with a VBA procedure, create a
scheduled task with a reminder, and run that proc whenever your reminder is
caught within some code you put in the ReminderFire event. The caveat with
this approach is that Outlook must be open to run it at the reminder time,
or else it will run it late if Outlook is opened after the reminder time

Let me know if you have any more questions.

--
Eric Legault [MVP - Outlook]
MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS 2007
& WSS 3.0 Application Development)
Collaborative Innovations
-> Try Picture Attachments Wizard For Microsoft Outlook <-
Web: http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault

 

[George]

Hi,

How should I programe my outlook to send my Access report at a given time?
Thank you in advance for reply.

George

Hi David. There are two ways to do this:

- Write your e-mail as you usually do, but instead of sending it, save it as
an .oft template file and run that file and send it when you want
- Automate the construction of the e-mail with a VBA procedure, create a
scheduled task with a reminder, and run that proc whenever your reminder is
caught within some code you put in the ReminderFire event. The caveat with
this approach is that Outlook must be open to run it at the reminder time,
or else it will run it late if Outlook is opened after the reminder time

Let me know if you have any more questions.

--
Eric Legault [MVP - Outlook]
MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS 2007
& WSS 3.0 Application Development)
Collaborative Innovations
-> Try Picture Attachments Wizard For Microsoft Outlook <-
Web: http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault

I've been programming VBA in Excel for years, but now I'm stepping outside
of
my comfort zone and trying to learn how to create VBA applications for
other
MS tools such as Outlook in order to work more efficiently. One thing I
find
myself doing lately is sending out a weekly reminder email to a
distribution
list at a set time. It's a mundane task, but must be done. Every time I
do
this, I have to go into Outlook, create a new message, select the
distribution list, copy the generic message to the mail userform and click
"Send". I'd like to think that there's a macro I can write for Outlook
that
will allow me simply push a button and presto! It's done! Or better yet,
have this macro running in the background such that every week at 9:00 am
on
Tuesday, this email automatically gets sent to the distribution list. Can
this be done?

Thanks!

 

[Jørund]

Hi Eric and David,

How can you save a message as an .oft file. If I go to the save as menu in
outlook, it only shows one option.

Do you have some kind of proc or VBA to copy? And does that mean that I have
to have the VBA installed on my computer?

Thanks

Joerund

Hi David. There are two ways to do this:

- Write your e-mail as you usually do, but instead of sending it, save it as
an .oft template file and run that file and send it when you want
- Automate the construction of the e-mail with a VBA procedure, create a
scheduled task with a reminder, and run that proc whenever your reminder is
caught within some code you put in the ReminderFire event. The caveat with
this approach is that Outlook must be open to run it at the reminder time,
or else it will run it late if Outlook is opened after the reminder time

Let me know if you have any more questions.

--
Eric Legault [MVP - Outlook]
MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS 2007
& WSS 3.0 Application Development)
Collaborative Innovations
-> Try Picture Attachments Wizard For Microsoft Outlook <-
Web: http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault

I've been programming VBA in Excel for years, but now I'm stepping outside
of
my comfort zone and trying to learn how to create VBA applications for
other
MS tools such as Outlook in order to work more efficiently. One thing I
find
myself doing lately is sending out a weekly reminder email to a
distribution
list at a set time. It's a mundane task, but must be done. Every time I
do
this, I have to go into Outlook, create a new message, select the
distribution list, copy the generic message to the mail userform and click
"Send". I'd like to think that there's a macro I can write for Outlook
that
will allow me simply push a button and presto! It's done! Or better yet,
have this macro running in the background such that every week at 9:00 am
on
Tuesday, this email automatically gets sent to the distribution list. Can
this be done?

Thanks!

 

[Eric Legault [MVP - Outlook]]

The option to save an .oft file *should* be in the Save As Type dropdown in
the Save As dialog - I don't know why it wouldn't be listed there.

Regardless, you can do so in code:

Sub CreateTemplate()
Dim myOlApp As New Outlook.Application
Dim MyItem As Outlook.MailItem
Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItem(olMailItem)
MyItem.Subject = "Status Report"
MyItem.To = "Dan Wilson"
MyItem.Display
MyItem.SaveAs "C:\statusrep.oft", OlSaveAsType.olTemplate
End Sub

You should already have VBA installed. You can see the editor by typing
ALT+F11.

--
Eric Legault [MVP - Outlook]
MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS 2007
& WSS 3.0 Application Development)
President
Collaborative Innovations
-> Try Picture Attachments Wizard 2.0 For Microsoft Outlook <-
-> Take your SharePoint content offline <-
-> More info: http://www.collaborativeinnovations.ca <-
Blog: http://blogs.officezealot.com/legault

Hi Eric and David,

How can you save a message as an .oft file. If I go to the save as menu in
outlook, it only shows one option.

Do you have some kind of proc or VBA to copy? And does that mean that I
have
to have the VBA installed on my computer?

Thanks

Joerund

Hi David. There are two ways to do this:

- Write your e-mail as you usually do, but instead of sending it, save it
as
an .oft template file and run that file and send it when you want
- Automate the construction of the e-mail with a VBA procedure, create a
scheduled task with a reminder, and run that proc whenever your reminder
is
caught within some code you put in the ReminderFire event. The caveat
with
this approach is that Outlook must be open to run it at the reminder
time,
or else it will run it late if Outlook is opened after the reminder time

Let me know if you have any more questions.

--
Eric Legault [MVP - Outlook]
MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS
2007
& WSS 3.0 Application Development)
Collaborative Innovations
-> Try Picture Attachments Wizard For Microsoft Outlook <-
Web: http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault

I've been programming VBA in Excel for years, but now I'm stepping
outside
of
my comfort zone and trying to learn how to create VBA applications for
other
MS tools such as Outlook in order to work more efficiently. One thing
I
find
myself doing lately is sending out a weekly reminder email to a
distribution
list at a set time. It's a mundane task, but must be done. Every time
I
do
this, I have to go into Outlook, create a new message, select the
distribution list, copy the generic message to the mail userform and
click
"Send". I'd like to think that there's a macro I can write for Outlook
that
will allow me simply push a button and presto! It's done! Or better
yet,
have this macro running in the background such that every week at 9:00
am
on
Tuesday, this email automatically gets sent to the distribution list.
Can
this be done?

Thanks!