PDA

View Full Version : Auto run Word mail merge to Email



rward
09-07-2020, 06:26 AM
I need to generate an email to send to guest of a resort the day before they are scheduled to arrive. The Email contains a QR code to identify them at the gate when they arrive. I have completed several task to accomplish this.
1. I have an Excel routine that extracts data from the scheduling database and grooms it for a mail merge.
2. I have a mail merge in word that uses the mailing list from excel to create an email and generate the QR code.
3. I have a macro in word that sends the email to the list. I have it set to run when the document is close.

If I open the word doc manually, it ask (Yes / No - “Data from your database will be placed in this document”. I answer (Yes) and when I close the document the macro runs and it sends the emails.

When I use a Visual Basic Script to open and close the document, the macro encounters an error.
(Run-Time error: “5852”: Requested object is not available)
When you debug it points out: .Destination = wdSendToEmail

It seems that it does not know who to send the letter to.
Perhaps the question “Data from your database will be placed in this document” needs to be answered in the VBS script?

Any thoughts?

Chas Kenyon
09-07-2020, 11:12 AM
Save your primary merge document as a non-merge ordinary Word document.
Have your vba code that opens the document next change it to an email merge and attach your data source.

rward
09-08-2020, 03:14 AM
Save your primary merge document as a non-merge ordinary Word document.
Have your vba code that opens the document next change it to an email merge and attach your data source.

Thank you! That makes sense.
But I'm afraid that as a novice programmer I don't know how to write the code in the VB Script to change the doc to an email merge and attach the data.

Chas Kenyon
09-08-2020, 02:46 PM
Here is some code I wrote about 15 years ago that may help.


With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strFileName, _
SQLStatement:="SELECT * FROM `Clients$`" ', _
'
' Show merge data
.ViewMailMergeFieldCodes = False
End With



That is part of a macro in a global template that is called whenever I create a new document based on one of my merge templates. If you want to use documents rather than templates, it would be part of a macro that runs whenever the document is opened. You would want to exit the document without saving once you've performed the merge.

The global macro is called by (hopefully unique) name:

Application.Run "AttachClients"

The name could be made more specific by specifying the project and module.

Application.Run "pKenyonMenus.mMerge.AttachClients"

For running from Excel it may have to be Word.Run instead. I am unsure of that.

gmayor
09-08-2020, 09:11 PM
Without seeing your code it is difficult to determine the nature of the problem, however see https://www.gmayor.com/email_merge_addin.html (https://www.gmayor.com/email_merge_addin.html) which should achieve what you want and if the appropriate field is in your merge document it will also produce the bar code {MERGEBARCODE "Fieldname" QR \t }

rward
09-09-2020, 02:01 PM
Without seeing your code it is difficult to determine the nature of the problem, however see (https://www.gmayor.com/email_merge_addin.html)https://www.gmayor.com/email_merge_addin.html which should achieve what you want and if the appropriate field is in your merge document it will also produce the bar code {MERGEBARCODE "Fieldname" QR \t }

Thank you Graham. We are soooo close!
My intention is to run the merge and email at a specific time each day without user interaction. I built a VBS to open the word doc and run your script "Merge_Document". But I still need to manually hit the button on your app to "complete the merge" Is there a way to script the action of that button?

gmayor
09-09-2020, 10:16 PM
I have replied to you privately with a possible solution using the add-in.