Automating dates

OCM · #1 03-15-2013, 10:40 AM

Greetings,
Using Outlook 2010 I created two templates that goes out to certain recipients every day.
The first template is used for the e-mail that goes out Tuesdays – Fridays & will have the following for the Subject/body:

Subject: report for: <today’s date> in “mm/dd/yy” format

Body of e-mail:
…first I ran the report using: <yesterday’s date> “mm/dd/yy” format
…and I ran the report using: <today’s date> “mm/dd/yy” format

The second template is used for the e-mail that goes out on Mondays & will have the following for the Subject/body:

Subject: report for: <today’s date>

Body of e-mail
…I ran the report using: <Friday’s date> and <Saturday’s date>
….I also ran this report using <today’s date>

Is it possible to have these dates already updated each time I open my templates?

TIA,

OCM

niton · #2 03-19-2013, 08:58 AM

Try this

Code:

Sub CreateDated_TF() 
    
    Dim MyItem As Outlook.MailItem
    Set MyItem = Application.CreateItem(olMailItem) 

    MyItem.Subject = "report for: " & Format(Date, "mm/dd/yy")

    Text_TF = "…I ran the report using:  " & Format(Date-1, "mm/dd/yy") & _
        vbCR & "….I ran this report using " & Format(Date, "mm/dd/yy")

    MyItem.Body = Text_TF
    MyItem.Display 

End Sub

Sub CreateDated_M() 
    
    Dim MyItem As Outlook.MailItem
    Set MyItem = Application.CreateItem(olMailItem) 

    MyItem.Subject = "report for: " & Format(Date, "mm/dd/yy")

    Text_M = "…I ran the report using:  " & Format(Date-3, "mm/dd/yy") & _
        " and " & Format(Date-2, "mm/dd/yy") & _
        vbCR & "….I also ran this report using " & Format(Date, "mm/dd/yy")

    MyItem.Body = Text_M
    MyItem.Display 

End Sub

If the text is not quite right see here for HTML example. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If you are not familiar with VBA see here. http://www.slipstick.com/outlook-dev...ks-vba-editor/

*******************
Consider rating the thread by going to the "Rate Thread" dropdown.

OCM · #3 03-22-2013, 12:06 PM

Niton,

Thank you very much the code worked fine. As you stated the text needs to be formatted. I’ve created the template in a certain format (text font, color, spacing etc.)

I’ll take a look at the link you provided & see if I can get the body of the e-mail as close as my template.

Regards,

ocm

OCM · #4 03-22-2013, 01:28 PM

Niton,

I forgot to attach my sample template (formatted). I appreciate any help on this…

Regards,

ocm

niton · #5 04-03-2013, 08:29 PM

Rather than recreating the entire email with VBA, try Replace.

Unfortunately I had a security warning message on each use of Replace.

Code:

Sub CreateFromTemplate_TF()

    Dim myOlApp As Outlook.Application

    Dim MyItem As Outlook.MailItem

    Set myOlApp = CreateObject("Outlook.Application")

    Set MyItem = myOlApp.CreateItemFromTemplate("C:\reportTF.oft")

    MyItem.Subject = "report for: " & Format(Date, "mm/dd/yy")

    MyItem.HTMLBody = Replace(MyItem.HTMLBody, "yesterday", Format(Date - 1, "mm/dd/yy"))

    MyItem.HTMLBody = Replace(MyItem.HTMLBody, "today", Format(Date, "mm/dd/yy"))

    MyItem.Display

End Sub

 

Sub CreateFromTemplate_M()

    Dim myOlApp As Outlook.Application

    Dim MyItem As Outlook.MailItem

    Set myOlApp = CreateObject("Outlook.Application")

    Set MyItem = myOlApp.CreateItemFromTemplate("C:\reportM.oft")

    MyItem.Subject = "report for: " & Format(Date, "mm/dd/yy")

    MyItem.HTMLBody = Replace(MyItem.HTMLBody, "Friday", Format(Date - 3, "mm/dd/yy"))

    MyItem.HTMLBody = Replace(MyItem.HTMLBody, "Saturday", Format(Date - 2, "mm/dd/yy"))

    MyItem.HTMLBody = Replace(MyItem.HTMLBody, "today", Format(Date, "mm/dd/yy"))

    MyItem.Display

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
automating a repetitive process	vthomeschoolmom	Excel Programming	1	02-28-2012 07:41 PM
automating insertion of some text	obrienaj	Word	3	10-17-2011 11:50 AM
Automating daily process	dreww2	Outlook	0	06-28-2011 07:25 PM
Automating Mailmerge from Access	PhilAJ	Mail Merge	4	05-23-2011 04:42 AM
Automating Cel Population of .jpg	skoz55	Excel	0	01-21-2009 03:43 AM