attach excel sheet

[G2K]

Hi All,

i have a macro to attach a excel sheet in new mails in outlook express and i use it to send a feedback form to our clients.at the clinet end, they need to save it on their system, update it and send it back to me.

is there any way to attach the same file without saving it. or what other options i have.....

 

[Simon Lloyd]

What do you mean?, of course they can forward the mail back to you complete with attachment.

Really what you want to do is have the macro that you have to send mail with attachment in your attached workbook in the thisworkbook module in this event "Private Sub Workbook_BeforeClose(Cancel As Boolean)" so after they have filled the form in and close it sends the workbook to you.

 

[G2K]

thanks Simon,

but it's not working. below is the code...


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim OutlookApp As Object
Dim MItem As Object
Dim Wb As Workbook
Dim NewWb As Workbook
Dim Ws As Worksheet

'Create Excel sheet link
Set Wb = ActiveWorkbook

'Create link to Excel sheet


'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = "ABC.abc.com"
.cc = "PQR.xyz.com"
.Subject = "Test Mail"
.Body = "Message"
.OriginatorDeliveryReportRequested = True ' delivery confirmation
.ReadReceiptRequested = True ' read confirmation
.Attachments.Add "Y:\abc\xyz.xls"
.display
End With
End Sub


the file xyz.xls have been attached from y: of my system. but the same code can not be used for attaching the same file from clinet end. pls suggest the code to attach the same file after update from ohter end


Thanks

 

[Simon Lloyd]

change

.ReadReceiptRequested = True ' read confirmation
.Attachments.Add "Y:\abc\xyz.xls"

for

.ReadReceiptRequested = True ' read confirmation
.Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
.display
.send
End With
msgbox "Please click yes on the Outlook warning message"

 

[G2K]

awesome......

thanks a ton

 

[Simon Lloyd]

The Outlook warning message can be bypassed by using ADO but i don't agree with not letting the user see what you are doing with their system