I have a list of recipients (*.xlsm) which is added to daily. The intention of this list is for them to be sent a single (e)mailmerge about 4 days later.



I will eventually automate this, how do I make sure that Word doesn't re-email people every time the automation is run?

I'm imagining an extra (autofilled) column in the data source (attached) indicating mailmerge has been done on that entry.

The macro on attached spreadsheet can be denied access - it simply opens as a data input form.

Attached Files

MoveOuts.xlsm (17.3 KB, 6 views)

The obvious solution is to use the last column of your sheet to indicate whether the message has been sent. This can be automated from the macro that sends the messages - see attached

Attached Files

MoveOuts.xlsm (25.4 KB, 5 views)

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Thanks so much, I had intended sending the (e)mailmerge document as a html document via word 2016 [review.docx] (though I do have outlook 2016 available) simply because I have used Word for mailmerge in the past. Would your macro be alterable to allow that? And could I add an extra final line into the macro to [send] the email(s), so no further intervention than clicking the button on the spreadsheet?

Some questions, so I can learn

I think the modified workbook attached opens with a Userform, I'd like to run your code from the "save and send" button on that but at this moment can't get the SendMessage() sub to run from it?

Looking at your code with the intention of using Outlook, I see that BodyFormat means I can send the emails as HTML. Does that mean I can use just simple html in the code?

I can see the merge fields like "sName" - interested that doesn't match any of the column headers on the spreadsheet, so if I wanted to use the 'STAFF' column, how would I format that?

Can you tell me, where in the code it indicates send > 5 days after the 'date' column

And finally . . .

I assume that after testing, I need to remove the apostrophe at the start of '.send

Attached Files

MoveOuts2.xlsm (24.7 KB, 7 views)

See attached

1 - Form amended to run SendMessage
2 - The process creates HTML messages, see the modified syntax used in the code. If you want to do it as you had it, you must collapse the range to its end before each new text or it will simply overwrite the last text.
3 - sName is just a variable name used to reproduce the FirstName in the message from the indicated cell.
4 - Added 5 days to delivery date
5 - Yes - remove the apostrophe to send the messages
I have added some annotation to the code.

Attached Files

MoveOuts2.xlsm (26.4 KB, 7 views)

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Brilliant, incidentally, found your website to be so interesting

Now all working brilliantly, however, I'd like to add a html hyperlink into the generated message - would this be easy to do?

It depends on your definition of easy.
Declare a few more variables including the link and split the texts before and after the link, collapsing the range each time so that the previous texts are not overwritten.
See attached.

Attached Files

MoveOuts2.xlsm (26.9 KB, 6 views)

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com