Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
The formula to show the current date is =TODAY() –  David Jun 18 '13 at 22:02
    
It gives an error when I put that in the Compare to field. Maybe it only takes constants? –  user1921849 Jun 18 '13 at 22:06

1 Answer 1

up vote 0 down vote accepted

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 $.

share|improve this answer
    
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. –  user1921849 Jun 19 '13 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. –  bibadia Jun 19 '13 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). –  user1921849 Jun 19 '13 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. –  user1921849 Jun 19 '13 at 22:07
    
/Every/ pair of {} has to be the "special field code braces" that you can insert using ctrl-F9. –  bibadia Jun 20 '13 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.