[RESOLVED] Mail merge problem looping through records set

[dcshunger]

I need to create individual merge documents from Access and then attach each document to an email sent from Access. All works fine providing I do them one at a time. However, when I loop through a record set to send each one seperately, the first record goes through fine but then I get an error message "The remote server machine does not exist or is unavailable".

This is what I have so far

For Each varItem In ctlList.ItemsSelected
intMemberID = ListMFD.Column(1, varItem)
strEmailAddress = ListMFD.Column(6, varItem)
strContactName = ListMFD.Column(2, varItem)
strFee = "£" & ListMFD.Column(5, varItem) & ".00"
strMemNo = ListMFD.Column(3, varItem)
DteDue = ListMFD.Column(4, varItem)

'create mail merge reminder docs

Set objDoc = objWord.Documents.Open(strTemplatePath & "\RenewalMailMerge.doc")

objDoc.MailMerge.OpenDataSource Name:="", _
Connection:="DSN=MMSSQL;DATABASE=MMSSQL;uid=SWCAAMMS;pwd=swcaamms;", _
SQLStatement:="SELECT ShortName, MembershipNo, FeeNextDue " & _
"FROM VW_MembersFeeRemindersDueMM WHERE MembershipNo = '" & strMemNo & "'", _
LinkToSource:=True, AddToRecentFiles:=False
objDoc.MailMerge.Destination = wdSendToNewDocument
objDoc.MailMerge.Execute
objDoc.Close
objWord.Documents(1).SaveAs (strSavedDocsPath & "\" & strMemNo & "_" & Format(Now(), "yyyy") & "_Renewal.doc")
objWord.Documents.Close
objWord.Quit (Word.wdSaveChanges = False)
Next varitem

Can anyone help please.

[westconn1]

objWord.Quit (Word.wdSaveChanges = False)

as you close word here it is no longer available to open the next document, only do this after finishing all documents

also you are closing the document, before the saveAs, which as you want to use it again, seems to be incorrect (inefficient), you only want to close the new document

[dcshunger]

Thanks Pete. Your a star. Any ideas on how you turn off outlook warnings when sending email as part of the same process. I am attaching each document to an email and sending them from the app but get the message "Aprogram is trying to send email on your behalf etc"?

[westconn1]

robdog has posted some code to close outlook security warnings, but you would need to search for it, it may be in the faq thread at top of this forum

otherwise use findwindow and sendmessage APIs

[dcshunger]

Thanks Pete, have searched everywhere but can't find it. Can you point me in the right direction to robdog's post please

[westconn1]

check out
http://www.vbforums.com/showthread.p...tlook+security
http://www.vbforums.com/showthread.php?t=402086

[dcshunger]

Thanks Pete, that will do the job nicely.