CDO attachment

[brianjohnson]

Hi All,

For some time I've been using Paul Sadowski's great CDO script to send mail from within Access 2003 and it has worked well including the use of a named file as an attachment. Into the future, I need to attach a file by reference rather than the exact file name ... but I can't get it to find the file (the system cannot find the path specified ...)

The file is created as here from within a form, key bits of code here ...

Dim LValue As Variant
LValue = DatePart("d", Now())
LValue = LValue & DatePart("m", Now())
LValue = LValue & DatePart("yyyy", Now())

Dim strqryname3 As String
dim strxlfile3 As String
strqryname3 = "qryexport"

strxlfile3 = Application.CurrentProject.Path & "\qryexport"
strxlfile3 = strxlfile3 & LValue
strxlfile3 = strxlfile3 & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strqryname3, strxlfile3

This all works fine. In another form and as part of the cdo process, I now need to reference this file to create the attachment ... here's the code line ...

objMessage.AddAttachment Application.CurrentProject.Path & "\" & strxlsfile3

... but as i say, the file can't be found ...

I've wondered if it's the scope of the variable across forms but that doesn't seem to be it. Can anyone point to where I'm going wrong?

Thanks all, Brian

 

[Grumm]

If you create a variable in form A try to use it in form B it only works if it is a global variable (defined in a module) http://www.blueclaw-db.com/access_database_global_variable.htm
It is not the case right now.
So my question is how to you initialize the strxlsfile3 in form B ?

 

[MarkK]

See the problem now?

strxlfile3 = Application.CurrentProject.Path & "\qryexport"
...
objMessage.AddAttachment Application.CurrentProject.Path & "\" & strxlsfile3

If not, do this . . .

msgbox Application.CurrentProject.Path & "\" & strxlsfile3
objMessage.AddAttachment Application.CurrentProject.Path & "\" & strxlsfile3

. . . and take a close look at the path you are trying to use. Debugging 101?
Cheers,

 

[brianjohnson]

Thanks both, absolutely right. The declaration 'Global strxlfile3 As String' in a module solves the problem and the email mail + attachment gets sent. I wonder if I might extend my question a little? The original attachment was in adobe acrobat .pdf format. This latest attachment is in Excel ... the Excel file generates properly but when received as an attachment to the email, I cannot open it. I get the error message:-

"qryexport1442016.xls cannot be accessed. The file may be read-only or you may be trying to access a read-only location. Or the server the document is stored on may not be responding"

I get the error whether I try to open the file in Outlook or if I save the file onto the hard disk.

Apologies if this should have been the subject of a separate post and best regards, Brian

 

[Grumm]

Have you tried to right click the attachment and save it somewhere on your computer ?
Maybe the security in office blocks email files.

Also if the Global declaration worked for you i guess you did something like this :

sub formA ()
Dim strxlfile3 as string
end sub

then you did something like this

sub formB ()
objMessage.AddAttachment Application.CurrentProject.Path & "\" & strxlsfile3
End sub

Variables declared in a sub or function or so called private. They only exists for that function and cannot be accessed from other functions.

 

[brianjohnson]

All I needed to do was declare strxlfile3 in a module and then just use it in my forms as if declared there. As others have said, variables can be declared as 'global' in modules but not in forms. Still no idea why the resultant file wont open after emailing ... it opens quite happily before being emailed. Tried saving to the hard disk but still won't open. File attached, can anyone open this or explain why it won't open?

Thanks all, Brian

 

[brianjohnson]

I can add to this that the Excel file is properly created and opens correctly.

I can right mouse click this filke, send to, mail recipient and it sends it (Outlook 2003), it's received and opens correctly. It does seem that it's CDO that doesn't like the Excel file unless there's something else I could try ... just wondering now what to do next :banghead:

Regards, Brian