Word naming and emailing attach from Access 10

[GaryPanic]

Hi guys long time

Bit of a weird one - and i cannot quite grasp it
outline

I need to create a word file (it needs to be word) based on template (not a very complicated template) max 1 to 1 and 1/2 pages long

the word doc needs to be named from the recordset but for now assume 12345.doc and the next one will be 12346.doc etc (I have a unique number system - available from tables /query

i can either make the word doc in code or use a template (template would be better)
recordset could have 20-30 in it each time i run it - but to be run every week

second half is how to email this out

i had in mind a email system

email to . test@testhotmail.xyz
file attach = this folder where the docs are store and attach 12345

in a loop

so either
create word file - email word file in a loop each time
or create all the word files and then send them individual

input would be welcome

oh.. and its sunny here in the UK - which makes a change

G

 

[GaryPanic]

starting point for Email element

attachement element would easy enough (I have pinched this from another posting ..)
i presume "Dim Sattachment as string etc - link it to my exported word doc location

say S drive Test
s\\test\

s\\Test\12345.doc ( or from my query )






Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qryQueryName", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(x)) = False Then
sToName = .Fields(x)
sSubject = "whatever: " & .Fields
sMessageBody = "Email Body Text " & vbCrLf & _* whatever i need here
"Field A: " & .Fields(a) & vbCrLf & _
"Field B: " & .Fields(b) & vbCrLf & _
"Field C: " & .Fields(c)

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

 

[GaryPanic]

I have had a look at my report and it's so simple that there are 22 lines of text
of which 11-12 are going to be the merge element

DoCmd.OutputTo acOutputReport, "Reportname", acFormatRTF, "S:\Test Data\xxxx\Policy\" & ReferenceNo & ".Doc", False

so this will create a RTF file singular

this gets me to looping
create loop - do merge
create email attach file from above location
S:\Test Data\xxxx\Policy\" & ReferenceNo & ".Doc",

send

using Outlook ...

 

[GaryPanic]

OK guys almost there (I have "borrowed" from all over the place )

my loop names the file
- good
I have an email sending out
its the attachment that got me foxed



Private Sub Command0_Click()


Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Dim Sattachment As String
Dim RefernceNumber As String
Dim attachments As String ?
Dim FilenameZ As String ?
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("bondqry", dbOpenSnapshot)





With rsEmail
.MoveFirst



Do Until rsEmail.EOF
DoCmd.OutputTo acOutputReport, "Bondreport", acFormatRTF, "c:\Test\" & .Fields(6) & ".Doc", False
FilenameZ = "c:\Test\" & .Fields(6) & ".Doc" ?
If IsNull(.Fields(11)) = False Then
sToName = .Fields(11)
sSubject = "whatever: " & "stuff"
sMessageBody = "Email Body Text "
'Sattachment =c:\Test\" & .Fields(6) & ".Doc"

Add.attachments = FilenameZ ?
'& vbCrLf & _* whatever i need here
'"Field A: " & .Fields(a) & vbCrLf & _
'"Field B: " & .Fields(b) & vbCrLf & _
'"Field C: " & .Fields(c)

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

 

[GaryPanic]

OK Guys i have cocked up (no surprise there)

i have a table with 10 records in it and i want the export to do 10 exports of 1 record each

currently i am getting 10 exports with 10 records

where have i lost it ?

(I know the email element will need changing as this doesn't work this way)



Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String to be changed
Dim sSubject As String to be changed
Dim sMessageBody As String to be changed
Dim Sattachment As String to be changed
Dim RefernceNumber As String
Dim attachments As String ?to be changed
Dim FilenameZ As String ?
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("bondqry", dbOpenSnapshot)





With rsEmail
.MoveFirst



Do Until rsEmail.EOF
DoCmd.OutputTo acOutputReport, "Bondreport", acFormatRTF, "c:\Test\" & .Fields(6) & ".Doc", False
FilenameZ = "c:\Test\" & .Fields(6) & ".Doc" ?
If IsNull(.Fields(11)) = False Then







sToName = .Fields(11)
sSubject = "whatever: " & "stuff"
sMessageBody = "Email Body Text "
'Sattachment =c:\Test\" & .Fields(6) & ".Doc"

Add.attachments = FilenameZ ?
'& vbCrLf & _* whatever i need here
'"Field A: " & .Fields(a) & vbCrLf & _
'"Field B: " & .Fields(b) & vbCrLf & _
'"Field C: " & .Fields(c)

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub