1

I am trying to semi-automate an email merge in Word. Excel is the source. Under filtering, I want the "Notice Date" column in the spreadsheet to equal whatever the current date is. Can I do that so we don't have to manually type the current date in the "compare to" field every day on the Filter and Sort dialog.

2
  • The formula to show the current date is =TODAY()
    – David
    Jun 18, 2013 at 22:02
  • It gives an error when I put that in the Compare to field. Maybe it only takes constants? Jun 18, 2013 at 22:06

2 Answers 2

0

You can't do it in the filtering dialog box, but there are two options: a. Use a nested field like this at the beginning of your mail merge main document: [[edited to show <> rather than =]] { SKIPIF { MERGEFIELD thedatefieldname \@YYYYMMDD } <> { DATE \@YYYYMMDD } } b. Use VBA to make the connection and provide a bit of SQL that does the comparison,

Two notes on (a) - I am not sure it will work with an emial merge as Word could generate blank emails. Microsoft has been saying for years that filtering should be used instead of SKIPIF but it always sounds to me as if the people who say that do not realise that the two methods do significantly different things.

For (b), you need something like the following for a default type (OLE DB) connection:

Sub connectToMMDS() 
ActiveDocument.MailMerge.OpenDataSource Name:="the full path name of your Excel workbook", _ SQLStatement:="SELECT * FROM [Sheetname$] WHERE [datecolumn] = date()" 
End Sub

Sheetname is the name of the sheet (i.e. if it's Sheet1, use [Sheet1$]). If it's a named range like myrange, use [myrange] and don't add the $.

11
  • A) Didn't seem to work. I got no records for email or creating docs. B) Didn't work either. For B) how does it know that that is the connection to use? When I open the doc it still shows SQL for the original connection to Excel with no WHERE clause. Jun 19, 2013 at 19:25
  • For a, you probably need <> rather than = - I will try to edit my post For b, you will probably need to remove the existing connection before Word will remember the new one. e.g., open your existing Mail Merge Main Document, get through any data source dialog, change the Main document type to be Normal Document (not a mail merge document), save/close, re-open, run the VBA, save, close and re-open.
    – user181946
    Jun 19, 2013 at 20:36
  • For a) I put at at the top of my document. It appears like regular text..not sure if that is right. Even if I change it to:{ SKIPIF { 1 <> 1 } } , I get no emails (or docs if I try that). Jun 19, 2013 at 21:57
  • i figured out I have to do it through insert field. I put in: SKIPIF { MERGEFIELD Notice_Date \@YYYYMMDD } <> { DATE \@YYYYMMDD }. It gives an error during the merge about too many picture switches. Jun 19, 2013 at 22:07
  • /Every/ pair of {} has to be the "special field code braces" that you can insert using ctrl-F9.
    – user181946
    Jun 20, 2013 at 0:05
1

If you can change the Excel spreadsheet, put this on one of the columns.

=IF(Y2=TODAY(),1,0)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .