how do you automatically generate a letter on certain dates

[Solly]

bsd

I would like to automatically generate a birthday greeting letter in
word2000 to my friends a day before their birthdays wich I have a tendecy to
forget

 

[Jay Freedman]

bsd

I would like to automatically generate a birthday greeting letter in
word2000 to my friends a day before their birthdays wich I have a
tendecy to forget

Word can't automatically start itself on particular dates. It would be
possible to write an AutoExec macro to check the current date each time you
start Word, to see if it's in a list of dates on which to print letters --
but if you forgot to start Word on the proper day, then nothing would
happen. And if you keep Word running all the time, then the AutoExec macro
doesn't run after the initial startup. That doesn't seem to me to be a good
scheme.

Do you use Outlook? It has a calendar feature with reminders, all built in
and ready to use. You can put in all your friends' birthdays, and have a
reminder for each one pop up a few days ahead.

If you don't use Outlook, check Google for free or low-cost calendar
programs -- or just hang a paper calendar on the wall and enter all the
birthdays on it.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[StevenM]

To: Jay Freedman,

<< ... or just hang a paper calendar on the wall and enter all the birthdays
on it. >>

If I hang a paper calendar on the wall, is there any way I can get a Word
macro to enter all the dates for me?

Steven Craig Miller

 

[Jay Freedman]

To: Jay Freedman,

<< ... or just hang a paper calendar on the wall and enter all the
birthdays on it. >>

If I hang a paper calendar on the wall, is there any way I can get a
Word macro to enter all the dates for me?

Steven Craig Miller

I'm thinking of the scene in Start Trek IV when Scotty picks up the
computer's mouse and starts to give it voice commands...

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Solly]

i open word almost every day
i wolud like these letters
generate automatically
what's wrong with that?

 

[Gordon Bentley-Mix]

Solly,

To paraphrase George W, I think you misunderestimate the complexity of the
task. I suggest you start by trying to put together something yourself and if
you get stuck then post back here. These forums are here to help to those who
are willing to help themselves, but they're not a source for free services.
All of the people who offer their assistance do so voluntarily and receive no
compensation other than the gratitude of those they help - and sometimes a
bit of reciprocity when they run into problems themselves. To ask someone to
develop a complete solution for you - for free - is unfair, and the responses
you have received are, IMHO, fitting.

If you don't have the skills to at least make an attempt to solve the
problem yourself then either hire someone to do it for you or accept Jay's
recommendation to use a tool better suited to the job - such as Outlook, some
sort of free or shareware utility or a paper calendar.

If you do want to take a stab at it, here's what you will need:

* A list of your friends' birthdays
* A form letter of some sort - with or without additional automation to
customise the letter
* Sufficient understanding of VBA (and, by extension, Word) to write a
macro that will look at the date list and generate a document using the form
letter

Jay has suggested an AutoExec macro, which of course has the limitation that
it will run only when Word starts. I suppose this could also be done using
some sort of "manually invoked" macro as well - e.g. click a button and the
macro checks the date against the list and either generated the appropriate
letter or (as a nice to have feature) displays a message telling you that
there are no letters to print today.

However, I still think you'd be better off using some sort of tool that
offers a calendaring function natively...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Greg Maxey]

Well said. But as a whimsical exercise:

Sub autoexec()
Dim myArray(5, 1) As Variant
Dim oDoc As Word.Document
Dim i As Long
For i = 0 To 5
myArray(i, 0) = Choose(i, "Mary", "Bill", "Bob", "Susie", "Sally",
"Fred")
myArray(i, 1) = Choose(i, "07/14", "07/18", "09/01", "10/13",
"03/19", "06/12")
Next i
For i = 0 To 5
If myArray(i, 1) Like Format(Date, "MM/dd") Then
Set oDoc = Documents.Add
With oDoc
.Range.Text = "Happy birthday to you, happy Birthday to you." _
& " happy birthday dear " & myArray(i, 0) & ",
happy birthday to you!!"
End With
End If
Next i
End Sub

 

[Gregory K. Maxey]

To spare a bit of complexity, the dates used in the code are "the day
before" the recipients birthday ;-)

 

[Gordon Bentley-Mix]

How did I know you were going to get involved in this? You really _do_ need
to get out more! ;-D

(BTW, I was going to have a go at this myself...)
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Jean-Guy Marcil]

Solly,

To paraphrase George W, I think you misunderestimate the complexity of the

"misunderestimate" I love that word!

Hmm. I would sure love to receive one of those preprinted birthday letter...
I'd put it next to the one I get from my dentist... which also has such a
personal touch...

 

[Jean-Guy Marcil]

Well said. But as a whimsical exercise:

Sub autoexec()
Dim myArray(5, 1) As Variant
Dim oDoc As Word.Document
Dim i As Long
For i = 0 To 5
myArray(i, 0) = Choose(i, "Mary", "Bill", "Bob", "Susie", "Sally",
"Fred")
myArray(i, 1) = Choose(i, "07/14", "07/18", "09/01", "10/13",
"03/19", "06/12")
Next i
For i = 0 To 5
If myArray(i, 1) Like Format(Date, "MM/dd") Then
Set oDoc = Documents.Add
With oDoc
.Range.Text = "Happy birthday to you, happy Birthday to you." _
& " happy birthday dear " & myArray(i, 0) & ",
happy birthday to you!!"
End With
End If
Next i
End Sub

Very nice, but can you make it sing as well?

;-)

 

[Greg Maxey]

How did I know you were going to get involved in this? You really _do_ need
to get out more! ;-D

(BTW, I was going to have a go at this myself...)
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.






- Show quoted text -

Well. It was something to do after work on a rainy day ;-)

 

[Solly]

bsd

perhaps us ungeeks do have some good ideas which you geeks can use to make
really useful templates for us ungeeks to use
(in Word which most people i think still use) perhaps your dentist or carpet
cleaner could use such a Word template too?
But remember the KISS word (Keep It Simple Stupid)

 

[Gregory K. Maxey]

Solly,

<But remember the KISS word (Keep It Simple Stupid)

????

Here is another old saw: Don't bite the hand that feeds you."

Seriously. The short coming with any attempt to achieve your goal with Word
is limited by the fact, as Jay pointed out, that if won't work if you don't
remember to start Word. My example was provided as an illustration to show
that it could be done using AutoExec, but as Gordon pointed out most of us
aren't committed to providing a fully functional application a) for free,
b) to someone that hasn't shown us what they have tried, or c) someone
calling us names.

The code I provided could be simplified greatly:

Sub autoexec()
Msgbox "Look at your calender and then start typing birthday letters to all
the people that you normally forget."
End Sub

It could also be made more functional by extracting the list of names and
dates from an external file and then populating fields in a dedicated
birthday letter template based on information from that file.

Regardless of which extreme you take both fail if the user doesn't start
Word.

I will be happy to assist you further if you wish. Just contact me via the
feedback link on my website.

 

[Jean-Guy Marcil]

bsd

perhaps us ungeeks do have some good ideas which you geeks can use to make
really useful templates for us ungeeks to use
(in Word which most people i think still use) perhaps your dentist or carpet
cleaner could use such a Word template too?
But remember the KISS word (Keep It Simple Stupid)

Hmm, I thought it was "Keep It Short and Simple"....

 

[Solly]

bsd

OK OK my appol.
but you also have to start your calendar program or turn the page to next
month on the wall.... so let's say that i start word and close it every time
i use it (which i do about 5-10 times a week) and use a button to start the
macro.

i also have a mail merge data table file which word helped me make in word
(so i don't need to open other programs) to which i can add a birthday column.

How do i get word to read this file and extract the right info?

What is your feedback link please?

 

[Gregory K. Maxey]

Solly,

Accepted

Ok. Here is a slightly refined version of the code I first provided:

Sub AutoExec()
Dim oDoc As Document
Dim oTbl As Word.Table
Dim myArray() As Variant
Dim lngCount As Long
Dim i As Long
If MsgBox("Do you want to generate letters due today?", vbQuestion +
vbYesNo, "Generate Letters") = vbYes Then
'Open the source file. This is a simply Word document with a single table
and 3 columns. Column 1 contains the due date in in "7/18" format. Column
2 is the persons first name
'column 3 is the persons last name.
Set oDoc = Documents.Open(FileName:="C:\Source.doc", Visible:=False)
'Modify file name and path as needed
Set oTbl = oDoc.Tables(1)
lngCount = oTbl.Rows.Count
ReDim myArray(lngCount - 1, 2)
'Load the information from the table into an array.
For i = 0 To lngCount - 1
myArray(i, 0) = Left(oTbl.Cell(i + 1, 1).Range.Text, Len(oTbl.Cell(i +
1, 1).Range.Text) - 2)
myArray(i, 1) = Left(oTbl.Cell(i + 1, 2).Range.Text, Len(oTbl.Cell(i +
1, 2).Range.Text) - 2)
myArray(i, 2) = Left(oTbl.Cell(i + 1, 3).Range.Text, Len(oTbl.Cell(i +
1, 3).Range.Text) - 2)
Next i
oDoc.Close wdDoNotSaveChanges
'Iterage throug each item in the array
For i = 0 To lngCount - 1
'If the date in the array matches the system date then generate a
letter.
If myArray(i, 0) Like Format(Date, "M/dd") Then
'Create a new document based on a birthday letter template
Set oDoc = Documents.Add("F:\My Documents\Word\Templates\My birthday
letter template.dot") 'Modify file name and path as needed.
With oDoc
'Put the part or all of the array information in the new document.
.Range.Text = "Happy birthday to you, happy birthday to you." _
& " Happy birthday dear " & myArray(i, 1) & ", happy birthday to
you!!"
End With
End If
Next i
End If
End Sub

There is a "Contact me" link at the following address:
http://gregmaxey.mvps.org/word_tips.htm

 

[Solly]

bsd

Thank you

now let me try it on my simple word DB table

i have 8 columns and the date is in the 8th col. with the date format like
JUL/28/1968 should i use myArray(i, 8) instead of myArray(i, 0).

The year is needed so i can let them know how old they are and generate a
message like "real undrstanding starts at forty - the Talmud." so what should
i use instead of "M/dd" as the format

also what is that little o in front of Tbl and Doc?

 

[Greg Maxey]

Solly,

<i have 8 columns and the date is in the 8th col. with the date format like
JUL/28/1968 should i use myArray(i, 8) instead of myArray(i, 0).

That would depend on whether you loaded all of the information from the
table into the array.

I didn't test this but to get a match using JUL/28/1968 with the system
date, then you would probably have to use something like:

If Fromat(myArray(i, 0), "M/dd") Like Format(Date, "M/dd") Then

A table is an object (I think) so my convention is oTbl.

 

[Gordon Bentley-Mix]

Greg,

In Re:

A table is an object (I think) so my convention is oTbl.

The VBA help makes reference to the 'Table Object' - as well as the 'Table
Property' (of the Style Object) and the 'Tables Collection Object' (which is
absolutely chocka with Table Objects <g>).

In your particular application you are indeed working with a specific Table
Object: oDoc.Tables(1). Therefore, under your naming convention, your use of
oTbl is correct - as is your use of oDoc.

Feeling more confident now? ;-P
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.