Attached file to Email with same name of texbox (1 Viewer)

[JPR]

Hello,

I have a form which exports data to a Word file and saves it with the value indicated in the textbox (FILENO).

I then have a code that opens Outlook but I am having problem in attaching the correct file.

The code (wrong) that I have come up with, will only attached a file with a specific name (ex. test.doc).

 .Attachments.Add "p:\Test\test.doc", olByValue, 1

Since the folder TEST will have multiple files, is there a way I can attach the file that has the same value in a textbox (FILENO) on the form?
Thank you

 

[Gasman]

You would concatenate it with the FileNo ?

Dim strFileName as String
strFileName = "P:\Test\" & Me.FileNo
....
....
.Attachments.Add strFileName,olByValue, 1

 

[JPR]

Yes, I will use the FileNo to concatenate.

I have tried your code but getting an error (cannot find the file) in the .Attachments.Add strFileName,olByValue, 1 line.

For your convenience, I am posting the entire code. Thank you

Dim objWord As Object
Dim strPath As String
Dim strBody As String

' This code uses a Table to indicate where the template is located.'

strPath = DLookup("[TemplateLocation]", "TablePatj", "[TemplateID]='" & Me.[txtpathMemo] & "'")
Dim strFileName As String
strFileName = "P:\Test\" & Me.FileNo
Set objWord = CreateObject("Word.Application")

objWord.Visible = False
objWord.Documents.Add strPath

objWord.ActiveDocument.Bookmarks("FileNo").Select
objWord.Selection.Text = Me.FileNo

objWord.ActiveDocument.Bookmarks("LNAME").Select
objWord.Selection.Text = Me.LNAME

objWord.Visible = False

' This code uses a Table to indicate where the template will be saved'   

strSavePath = DLookup("[TemplateLocation]", "TablePath", "[TemplateID]='WordSavePathFileNo'") 'xxx
objWord.ActiveDocument.SaveAs strSavePath & Me![FileNo] & ".doc"  ' xxxx
objWord.Quit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'CREATES MS OUTLOOK SESSION AND ATTACHES FILE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
With oMail

strBody = "Hello this is a test." & vbCrLf & vbCrLf
.Subject = "Remittance Memo"
.Body = strBody
    
.Attachments.Add strFileName, olByValue, 1
.Display True

 

[Gasman]

Ok, so you have added a .doc extension?

You need to supply ALL of the filename, path, prefix & extension ?

So put ALL of that into a string variable and just use that each time, both for the Save and the Attachment.

You specify the P:\Test for the attachment, yet are saving it somewhere where DLookup("[TemplateLocation]", "TablePath", "[TemplateID]='WordSavePathFileNo'") returns a value?

Are you understanding what you are writing?

 

[JPR]

Hello. I am sorry for the confusion.
I will try to be more clear.

I export data from a form to a Word Template named Memo.dot.
The template is stored in P:\Test\Memo.dot

Its' path and name is indicated in a table named TablePath which has two fields:

1) Template ID, indicating the name of the file (Memo)
2) Templatelocation, indicating the path where the template is stored (P:\Test\Memo.dot)

Maybe I could post a copy of the db with just the necessary information. Thank you

 

[Gasman]

Regardless of where you save it, you have to specify the same location and name?
So make StrSavePath the FULL path, folder,prefix and extension, (the whole works) then just use that for your save, then for your Attachments.Add

 

[JPR]

Hi,
Both the template and new file will be in the same folder but here is the problem. I do not know how to write the StrSavePath. Do you mind helping me with it? Thanks

 

[Gasman]

You already have

strSavePath = DLookup("[TemplateLocation]", "TablePath", "[TemplateID]='WordSavePathFileNo'") 'xxx

so I would just do

strSavePath = DLookup("[TemplateLocation]", "TablePath", "[TemplateID]='WordSavePathFileNo'") 'xxx
strSavePath = strSavePath & Me![FileNo] & ".doc"
objWord.ActiveDocument.SaveAs strSavePath

then

.Attachments.Add strSavePath

I am not sure what those options are for for the .Add, but use them if needed.

Also make sure, by inspecting the variables, as to what they contain.?
Your initial strSavePath might not have a trailing \, so you would need to add that as well?

I'll leave you to figure out how to do that.

 

[JPR]

Thanks. I will give it a try and will let you know.

 

[JPR]

Finally got it working. It was just a matter of putting the code in the right place
Thank you

 

[Gasman]

Finally got it working. It was just a matter of putting the code in the right place
Thank you

Well that applies all the time with coding?

Not like the Morcambe and Wise show with Andre Previn, when Eric Morcambe is playing the piano and Andre says that is not right, and Eric replies "I am playing the right notes, just not necessarily in the right order"