#1
|
|||
|
|||
Don't duplicate merged messages
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. Last edited by Fyldeboy; 02-28-2022 at 01:28 AM. Reason: clarified needs |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
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?
|
#4
|
|||
|
|||
Quote:
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 Last edited by Fyldeboy; 03-02-2022 at 10:16 AM. Reason: Added SendMessage() comment |
#5
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
Brilliant, incidentally, found your website to be so interesting
|
#7
|
|||
|
|||
Now all working brilliantly, however, I'd like to add a html hyperlink into the generated message - would this be easy to do?
|
#8
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to unmerge all merged cell and fill them with the merged values? | Bumba | Excel Programming | 1 | 11-10-2019 11:36 AM |
Can Excel find Duplicate entries when only part of the cell's data is a duplicate of another cell? | jsisley | Excel | 1 | 07-21-2017 09:20 AM |
Web string/link in Excel, Mail merged into Word and then pasted into Messages on Mac issue | Alex Knight | Excel | 0 | 12-08-2016 08:33 AM |
Table will not allow sorting because "cells are merged". I can't find the merged cells. | wendyloooo | Word Tables | 1 | 05-26-2015 01:19 PM |
Why does leaving messages on the server in Outlook 2007 cause duplicate incoming emails? | haggis999 | Outlook | 0 | 02-23-2015 10:29 AM |