I need some help with Date Coding ?

[JohnnyBravo]

HI guys - got a 2 part question.

Part A:
I've got a form letter (word doc) which I am using to merge an Access db with.

October 15, 2005



Mr. Joe Smith
ABC Hospital, Inc.
123 Easy Lane
Anytown, MI 48231

On page 2 & 3, I had inserted two bookmarks. At bookmark #1, is the current date at top of letter plus 14 days; at bookmark #2, is the current date plus 35 days.

Now i've got a small tiny problem because the letters are not always going out that same day. Sometimes I have to wait upon information from other people before mailing them out. The VBA code I had is no longer meeting my needs because I was using an an AUTO insert field at the top of the letter and based on that date, the VBA was calculating dates for bookmark #1 and #2 accordingly.

Now being the smart people you guys (& gals) are, I know you're sitting there wondering well why don't you just merge the documents again on the day the letters finally get mailed? The answer is that after I merge all the records, I customize each letter deleting certain paragraphs and adding certain sentences. I don't want to have to do that for each batch of 20-30 letters. It would be better for me to save the merged doc. say for ex: "Merged Documents Batch Run_A.doc" and then edit the dates accordingly.

How do you tell VBA to:

At bookmark #1: Insert whatever date that I type at the top of the letter.
At bookmark #2: plus 14 days
At bookmark #3: plus 35 days

Part B:
The merged doc. might have anywhere from 5 to 20 addressee's. So for example, the first merged record might be Mr. Joe Smith at ABC Hospital. I want VBA to create a subfolder under "C:\Letters Sent\" as "ABC Hospital" and then save his letter there. How can you get VBA to save each portion of the merged document and save it separately rather than the entire document?

[TonyJollans]

Hi Johnny,

How, exactly, are you creating your dates? If the "current date" is a date field, and the other two are in (calculated) fields based on it, Word should be able to sort it out. If you do the calculations up front, of course, you'll have to do them again.

I'm not a mail merge expert so I don't know about the second question. I suspect you'll need special code to split out the sections from the merged document. I'll have a play later unless someone comes back with an easy way of doing it first.

[MOS MASTER]

Hi Johny, :hi:

I'm not sure this is the total sollution for you cause I don't know if your dates are bookmarked (Range) or the bookmark is in front of it.

If they are range bookmarked then something like this could work:
Sub myDates()
Dim dDate As Date
dDate = CDate(InputBox("The date", "Provide Date", Format$(Now, "DD MM YYYY")))

With ActiveDocument
.Bookmarks("bmDate").Range.Text = Format$(dDate, "DD MM YYYY")
.Bookmarks("bmDate2").Range.Text = Format$(DateAdd("d", 14, dDate), "DD MM YYYY")
.Bookmarks("bmDate3").Range.Text = Format$(DateAdd("d", 35, dDate), "DD MM YYYY")
End With

End Sub


Of course a field suggestion like Tony says would be possible of course depending on your document.

Btw...keep in mind that my date format (Holland) is "DD MM YYYY" you should change that to your own dateformat.

HTH. :whistle:

[gmaxey]

This page might help with the split and save part of your question:
http://www.gmayor.com/individual_merge_letters.htm

[JohnnyBravo]

This page might help with the split and save part of your question:

Thanks for that link. I tried to run it but I get an error at the highlighted line - any clue as to why?? All I did was simply copy and paste exactly as he has it on his website.

Sub SplitMerge()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
' with modifications by Graham Mayor 16-06-03 & 08-10-04

Dim Title As String
Dim Default As String
Dim MyText As String
Dim MyName As Variant
Dim MyPath As String

Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1

Default = "Merged"
MyText = "Enter a filename. Long filenames may be used."
Title = "File Name"
MyName = InputBox(MyText, Title, Default)
If MyName = "" Then
End
End If

Default = "D:\My Documents\Test\"
Title = "Path"
MyText = "Enter path"
MyPath = InputBox(MyText, Title, Default)
If MyPath = "" Then
End
End If

While Counter < Letters
Application.ScreenUpdating = False
Docname = MyPath & LTrim$(Str$(Counter)) & " " & MyName

ActiveDocument.Sections.First.Range.Cut
Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, _FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub

[Anne Troy]

Here's another that might help:
http://vbaexpress.com/kb/getarticle.php?kb_id=139

[gmaxey]

All I can suggest is that there is no "_" character following the comma in that line of code that Graham has posted. Have you tried removing that?

[JohnnyBravo]

All I can suggest is that there is no "_" character following the comma in that line of code that Graham has posted. Have you tried removing that?

Yes. And still no go. :dunno

Steamboat, i'll take a look at that link shortly after I try something. Thanks.

Edit: Steamboat - that did the trick. Thank you - but how do I change the directory to which the individual files are saved to??

[Anne Troy]

Steamboat???

Are you related to Dave Hawley?? He calls me Tuggy.

[JohnnyBravo]

Steamboat???

Are you related to Dave Hawley?? He calls me Tuggy.

Oh bazookas....I'm sorry about that Dreamboat. My brain is fried today from wrestlin with this code all day. My mind was somewhere else when I typed that.

Got any ideas on how I can change the directory?

[Anne Troy]

LOL, no problem. I should say something now, like "No wonder you need help with date coding. :D

Have you searched the KB? There's all kinds of code in there. And don't discount, for instance, Excel code 'cause the syntax and lots of code will be the same, just the objects change. Here's the KB: www.vbaexpress.com/kb (http://www.vbaexpress.com/kb)

Like the forum, you only need to log in the first time to be able to search it as long as you're a forum member. I'll go now to look, too.

[Ken Puls]

Hi Johnny,

Anne asked me to take a quick look, and I don't have much time here. There error you are getting... do you actually have a directory at "D:\My Documents\Test\"?

If not, change this line:

Default = "D:\My Documents\Test\"

This actually passes a path to an input box as a default, but update everything between the quotes to your path.

Sorry if I missed the point. I'll check back in a bit. :)

[Ken Puls]

Oh! And Greg is abosultely right. You need to lose the _ in that line:


ActiveDocument.SaveAs FileName:=Docname, FileFormat:=wdFormatDocument

[JohnnyBravo]

Oh! And Greg is abosultely right. You need to lose the _ in that line:

That's weird - earlier tonight I did remove the underscore in that line and it wouldnt' work - but now all of a sudden it does! Go figure. Cool - now I got 2 different ways to split those merged documents. I appreciate everyone's help here - you guys are real helpful. I'm hittin the sack - this boy is dead tired. Thanks all.

[Bilby]

DOn't know if your date problem has been solved but, If the prob is re-calculating the date for when the letters are sent. Assuming you print them on the day then how about a customized Print command that updates the bookmarks before sending to the printer?

[JohnnyBravo]

DOn't know if your date problem has been solved but, If the prob is re-calculating the date for when the letters are sent. Assuming you print them on the day then how about a customized Print command that updates the bookmarks before sending to the printer?

Hey that's totally cool - yes I would like something like that - but I've got bigger fish to fry - this RFI project is so manual intensive... there's so many other steps involved here that If i were to explain all the steps involved, the scroll wheel on your mouse might wear out! :lol: