Tricky Linkage!

[Yes Boss]

Hi all,

I have a tricky problem which is proving difficult to solve. My first set of code creates a new word file using .dot. I then need to email this file to a counterpart using Lotus Notes. I have figured out how to create a file in Word but I cant seem to send this specific file by email.

I have written some seperate code for the email code for Lotus Notes, this code sends the file with the path P:\Documents\Bookmark1.dot. I cant however manage to send the i create with the first set of code.

The following code creates a word document by extracting data from access. I then somehow need to link this new file to the second set of code which then sends a file via lotus notes to a specific counterpart. Any suggestions would be most appreciated. The first and second set of code follow below.

Code 1....................................................................

Sub Jones()
Call createfile(GetData(InputBox("Enter Counterpart Name")))

End Sub

Public Function GetData(Cntrprt As String) As String

Dim myrecordset As New ADODB.Recordset
Dim mycnn As New ADODB.Connection
Set mycnn = CurrentProject.Connection
Dim mystring As String
Dim Myfinalstring As String


myrecordset.Open "qry_push_locate", mycnn

Currentcounterpart = myrecordset!Counterpart

'Do Until myrecordset.EOF

'CurrentCounterpart = myrecordset!Counterpart
While (Not myrecordset.EOF)


If myrecordset("Show Bid?") = "OK" And myrecordset("Counterpart") = Cntrprt Then

mystring = mystring + myrecordset!Counterpart + (Chr(9) + Chr(9) + Chr(9) + Chr(9) + CStr(myrecordset!Sedol)) + ((Chr(9) + Chr(9) + CStr(myrecordset!Bid) + Chr(9))) + Chr(9) + CStr(myrecordset("Bid Size")) + Chr(10)
Myfinalstring = Myfinalstring + mystring

End If

myrecordset.MoveNext

'If myrecordset.EOF Then

'Exit Do
' End
'End If

Wend

Debug.Print "Hello "
'Loop
Debug.Print Myfinalstring
GetData = Myfinalstring
End Function

Function createfile(reportcontents As String)

Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

Dim MergeDoc As String

MergeDoc = MergeDoc + "P:\Documents\Bookmark1.dot"
Wrd.Documents.Add MergeDoc
Wrd.Visible = True

'For i = 1 To 10
' MsgBox Wrd.ActiveDocument.Bookmarks.Item(i)
'Next

With Wrd.ActiveDocument.Bookmarks

.Item("Bookmark").Range.Text = reportcontents

End With


End Function

CODE 2 (sends file by email)................................................I NEED THIS TO LINK TO THE ABOVE CODE AND SEND THE NEW FILE CREATED IN WORD


Sub Test()

Call SendNotesMail("sub", "att", "Rec", "BodT", True)

End Sub

Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)

Dim Maildb As Object
Dim Username As String
Dim MailDbName As String
Dim MailDoc As Object
Dim AttachMe As Object
Dim Session As Object
Dim EmbedObj As Object
Dim Attach1 As String

Set Session = CreateObject("Notes.NotesSession")
Attach1 = "P:\Documents\Bookmark1.dot"



Username = Session.Username
MailDbName = Left$(Username, 1) & Right$(Username, (Len(Username) - InStr(1, Username, ""))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.ISOPEN = True Then
Else
Maildb.OPENMAIL
End If
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = InputBox("Hello Please enter email address")
MailDoc.Subject = "Locates"
MailDoc.Body = "Here are the locates, Kind Regards Daniel Childs"
MailDoc.SAVEMESSAGEONSEND = SaveIt
Set AttachMe = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachMe.EMBEDOBJECT(1454, "", Myfinalstring, "P:\Documents\Bookmark1.dot")
MailDoc.CREATERICHTEXTITEM ("Attachment")
MailDoc.PostDate = Now()
MailDoc.Send 0, InputBox("Hello Please enter email address")
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachMe = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

End Function

[Norie]

Why can't you pass the path and file name of the document to the code that creates the email?